BEGIN or not for DBMS_SCHEDULER PLSQL_BLOCK program?

From: "Rich Jesse" <rjoralist@xxxxxxxxxxxxxxxxxxxxx>
To: oracle-l@xxxxxxxxxxxxx
Date: Fri, 31 Oct 2008 14:22:59 -0500 (CDT)

Hey all,

In 10.1.0.5.0, I added a DBMS_SCHEDULER program:

BEGIN
SYS.DBMS_SCHEDULER.CREATE_PROGRAM
(
program_name => 'RICH.MY_PRG'
,program_type => 'PLSQL_BLOCK'
,program_action => 'begin
rich.my_procedure_01;
rich.my_procedure_02;
end;
'
,number_of_arguments => 0
,enabled => FALSE
,comments => '2007/04/16 -- Created.'
);

SYS.DBMS_SCHEDULER.ENABLE
(name => 'RICH.MY_PRG');
END;
/

Note the creation date of 18 months ago -- it's been running faithfully
every Monday since.

I created a similar program yesterday:

BEGIN
SYS.DBMS_SCHEDULER.CREATE_PROGRAM
(
program_name => 'T_SCHEMA.MY_NEW_PRG'
,program_type => 'PLSQL_BLOCK'
,program_action => 'BEGIN
T_SCHEMA.MY_PROC_01;
T_SCHEMA.MY_PROC_02;
END;
'
,number_of_arguments => 0
,enabled => FALSE
,comments => '2008/10/30 -- Created.'
);

SYS.DBMS_SCHEDULER.ENABLE
(name => 'T_SCHEMA.MY_NEW_PRG');
END;
/

I then created several schedules for this program (limitation of the
Scheduler in Ora 10.1) and corresponding jobs for each. Every job fails with
an ORA-12012 and ORA-6550:

ORA-06550: line ORA-06550: line 1, column 361:
PLS-00103: Encountered the symbol "" when expecting one of the following:

So I turned on tracing in a login trigger. The resulting trace says:

DECLARE job BINARY_INTEGER := :job; next_date TIMESTAMP WITH TIME ZONE :=
:mydate; broken BOOLEAN := FALSE; job_name VARCHAR2(30) := :job_name;
job_owner VARCH
AR2(30) := :job_owner; job_start TIMESTAMP WITH TIME ZONE := :job_start;
window_start TIMESTAMP WITH TIME ZONE := :window_start; window_end
TIMESTAMP WITH TIME
ZONE := :window_end; BEGIN BEGIN
T_SCHEMA.MY_PROC_01;
T_SCHEMA.MY_PROC_02;
END; :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;

Note the double "BEGIN" in the trace?  I removed the "BEGIN" and "END;"
lines from the Scheduler program and it works perfectly.  However, the
original RICH.MY_PRG that contains the BEGIN and END inexplicably also
works.

I'm very hesitant to trace the working one since it's in Production and in a
schema that has many other critical schedules running, especially now at
Financial Month End Close.

I couldn't find any examples in the Oracle Docs for a PLSQL_BLOCK-type
program that contains multiple statements.  But it seems obvious that the
PLSQL_BLOCK argument should not contain the BEGIN and END statements.  So
why does my older job work???

Not a very scary mystery, but still...

TIA!
Rich



--
//www.freelists.org/webpage/oracle-l

BEGIN or not for DBMS_SCHEDULER PLSQL_BLOCK program?

Other related posts: