BEGIN or not for DBMS_SCHEDULER PLSQL_BLOCK program?

From: Rich Jesse <rjoralist_at_society.servebeer.com>
Date: Fri, 31 Oct 2008 14:22:59 -0500 (CDT)
Message-ID: <2b7151e3fecafc15e9b6df5ee33bbc48.squirrel@society.servebeer.com>

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

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

Received on Fri Oct 31 2008 - 14:22:59 CDT