BEGIN or not for DBMS_SCHEDULER PLSQL_BLOCK program?
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-lReceived on Fri Oct 31 2008 - 14:22:59 CDT