Automation Error

We’re doing a daily automation of a sql script that generates an excel file that it then emails, its also set to email on error and we’re receiving the following log entry when the automation runs.

– 5/23/2012 4:18:09 PM: custom_completed_activities_cr.log: Build started
– 5/23/2012 4:18:21 PM: custom_completed_activities_cr.log: Build Completed
– 5/23/2012 4:18:21 PM: custom_completed_activities_cr.log: Connecting to SMSP (LICSYSANDOPS), LICSYSANDOPS
– 5/23/2012 4:18:21 PM: custom_completed_activities_cr.log: Connection successful.
– 5/23/2012 4:18:22 PM: custom_completed_activities_cr.log: Begin Export
– 5/23/2012 4:18:22 PM: custom_completed_activities_cr.log: SQL to execute: drop table customer_compl_acts;
–create table custom_compl_acts as
–(SELECT
–c.com_record_type,
–c.act_complrep,
–c.act_status,
–c.act_type,
–c.act_action_step,
–c.act_objective,
–c.act_result,
–c.act_outcome,
–count(c.act_complrep) as NR_OF_COMPLETED_ACT,
–to_char(trunc(c.act_compldate), ‘MM/DD/YYYY’) AS COMPLETION_DATE,
–trunc(c.act_compldate) - trunc(c.act_scheddate) AS DAYS_TO_COMPLETION

–FROM
–(
–select
–b.com_record_type,
–a.act_complrep,
–a.act_status,
–a.act_type,
–a.act_action_step,
–a.act_objective,
–a.act_id,
–a.act_scheddate,
–a.act_compldate,
–a.act_result,
–a.act_outcome
–from root.mxactivity a, root.company b
–where a.act_compid = b.coid
–and a.act_status in(‘Completed’)
–AND a.act_compldate between trunc(sysdate) -1 and trunc(sysdate) -1
----AND trunc(a.ACT_COMPLDATE) = ‘13-APR-2012’
–and b.com_record_type in(‘GEN’)
–and a.act_complrep IN(‘amarsh’,
–‘ansmith’,
–‘astaffor’,
–‘dchenowe’,
–‘jhoffman’,
–‘jjones’,
–‘lnewland’,
–‘msmith’,
–‘myounger’,
–‘tscarbro’,
–‘HOS-CR’,
–‘Hspcstrp’,
–‘ECH-CR’,
–‘ECH-MAGc’,
–‘ECH-MAGe’,
–‘ECH-MAGw’,
–‘csmith’,
–‘brwilson’
–)

–GROUP BY b.com_record_type, a.act_complrep, a.act_status, a.act_type, a.act_action_step,a.act_objective,a.act_id, a.act_scheddate,
–a.act_compldate, a.act_result, a.act_outcome


–) c


–GROUP BY
–c.com_record_type,
–c.act_complrep,
–c.act_status,
–c.act_type,
–c.act_action_step,
–c.act_objective,
–c.act_result,
–c.act_outcome,
–c.act_compldate,
–c.act_scheddate);


–SELECT DISTINCT act_complrep, completion_date, SUM(nr_of_completed_act) AS NR_OF_ACCTS

– FROM

– (SELECT DISTINCT act_complrep, completion_date, act_outcome, nr_of_completed_act,
– row_number() OVER ( PARTITION BY act_complrep ORDER BY rownum) rn

– FROM custom_compl_acts)

– CONNECT BY act_complrep =PRIOR act_complrep

– AND rn = PRIOR rn+1

– START WITH rn =1

– GROUP BY act_complrep, completion_date;


– 5/23/2012 4:18:22 PM: custom_completed_activities_cr.log: Beginning export.
– 5/23/2012 4:18:23 PM: custom_completed_activities_cr.log: Attempted to export 8 rows.
– 5/23/2012 4:18:23 PM: custom_completed_activities_cr.log: Successfully exported 8 rows.
– 5/23/2012 4:18:23 PM: custom_completed_activities_cr.log: Export Finished, click Custom_Completed_Activities_CR_2012-05-23 16-18-21.xlsx to view file.
– 5/23/2012 4:18:23 PM: custom_completed_activities_cr.log: ORA-00955: name is already used by an existing object
– 5/23/2012 4:18:23 PM: custom_completed_activities_cr.log: at Quest.Toad.Workflow.Activities.Database.SelectToExcelActivity.Execute(ActivityExecutionContext executionContext)
– 5/23/2012 4:18:23 PM: custom_completed_activities_cr.log: Start error email

The weird part of this, its set to create the excel file with a date & time stamp (it creates the file), but it still errors out with the " name is already used by an existing object " error.

Sorry, I’m not an Oracle expert but it seems that the error is triggered not by existing xlsx file. It seems like customer_compl_acts table is not dropped by the time it should be recreated. Is it possible to get your Automation script that fails? You can send it directly to me (Igor.Manokhin@quest.com).

Thanks,

Igor.