Errors using dbms_scheduler instead of dbms_job
Hello,
I'm looking for some help using the dbms_scheduler in Oracle 10g. Up until recently, we have been happily submitting jobs in Oracle using the dbms_job.submit() command without problems. Due to this now becoming deprecated in release 10g and beyond we are looking to make use of the new dbms_scheduler.create_job() functionality. However, when trying to convert from dbms_job to dbms_scheduler, I keep getting errors when trying to run the stored procedure. I'm sure I must be missing something obvious but I just cannot put my finger on it at present.
The set up using dbms_job is as follows:
We have a procedure (called PRODCEURE_A) that is called directly from our code and included in the procedure is a call to dbms_job that submit a job to run PROCEDURE_C which in turn calls a PROCEDURE_B. For example PRODCEURE_A is created as follows:
----------------------------------------------------------------------
create or replace procedure PRODCEURE_A AS
jobnum number;
row_count number;
current_value_date DATE;
snapshot_id Number;
BEGIN
row_count :=0;
BEGIN
/* Fetch current value date and snapshot number */
SELECT COL_A, (COL_A- COL_B)
INTO current_value_date, snapshot_id
FROM TABLE_A
WHERE COL_C= 0;
/* delete records from TABLE_A table that are older than 40 days */
DELETE TABLE_A WHERE COL_A < current_value_date - 40;
/* ensure that are no records in TABLE_A for the current settlement date */
select count(*) into row_count
from TABLE_A
where COL_A = current_value_date;
/* Insert all the Date snapshot jobs into TABLE_Awith initial status as NS */
if row_count < 1 then
Begin
insert into TABLE_A values (1, 'PROCEDURE_B', current_value_date, NULL, NULL, 'NS', NULL);
/* Submit the oracle job spst_execute_date_snapshot to run asynchronously */
dbms_job.submit(jobnum, 'PROCEDURE_C ('||''''||current_value_date||''''||', ' || snapshot_id || ');');
End;
END IF;
END;
END;
/
----------------------------------------------------------------------
This works without problems. A job is created and PROCEDURE_C is duly executed. I attempt to change dbms_job to dbms_scheduler as below:
----------------------------------------------------------------------
create or replace procedure PRODCEURE_A AS
jobnum number;
row_count number;
current_value_date DATE;
snapshot_id Number;
BEGIN
row_count :=0;
BEGIN
/* Fetch current value date and snapshot number */
SELECT COL_A, (COL_A- COL_B)
INTO current_value_date, snapshot_id
FROM TABLE_A
WHERE COL_C= 0;
/* delete records from TABLE_A table that are older than 40 days */
DELETE TABLE_A WHERE COL_A < current_value_date - 40;
/* ensure that are no records in TABLE_A for the current settlement date */
select count(*) into row_count
from TABLE_A
where COL_A = current_value_date;
/* Insert all the Date snapshot jobs into TABLE_A with initial status as NS */
if row_count < 1 then
Begin
insert into TABLE_A values (1, 'PRODCEURE_B', current_value_date, NULL, NULL, 'NS', NULL);
/* Submit the oracle job spst_execute_date_snapshot to run asynchronously */
dbms_scheduler.create_job(
job_name => '"JOB_NAME"',
job_type => 'STORED_PROCEDURE',
job_action => 'PRODCEURE_C('||''''||current_value_date||''''||', ' || snapshot_id || ');',
enabled => TRUE);
End;
END IF;
END;
END;
/
----------------------------------------------------------------------
However, when trying the above I get the following error:
ORA-27452: PROCEDURE_C('22-JUL-09', 2494); is an invalid name for a database object
When I attempt to change job_type from 'STORED_PROCEDURE' to PLSQL_BLOCK as follows:
----------------------------------------------------------------------
create or replace procedure PRODCEURE_A AS
jobnum number;
row_count number;
current_value_date DATE;
snapshot_id Number;
BEGIN
row_count :=0;
BEGIN
/* Fetch current value date and snapshot number */
SELECT COL_A, (COL_A- COL_B)
INTO current_value_date, snapshot_id
FROM TABLE_A
WHERE COL_C= 0;
/* delete records from TABLE_A table that are older than 40 days */
DELETE TABLE_A WHERE COL_A < current_value_date - 40;
/* ensure that are no records in TABLE_A for the current settlement date */
select count(*) into row_count
from TABLE_A
where COL_A = current_value_date;
/* Insert all the Date snapshot jobs into TABLE_A with initial status as NS */
if row_count < 1 then
Begin
insert into TABLE_A values (1, 'PRODCEURE_B', current_value_date, NULL, NULL, 'NS', NULL);
/* Submit the oracle job spst_execute_date_snapshot to run asynchronously */
dbms_scheduler.create_job(
job_name => '"JOB_NAME"',
job_type => PLSQL_BLOCK',
job_action => 'BEGIN PRODCEURE_C('||''''||current_value_date||''''||', ' || snapshot_id || '); END;',
enabled => TRUE);
End;
END IF;
END;
END;
/
----------------------------------------------------------------------
This gives the following error:
ORA-02089: COMMIT is not allowed in a subordinate session
I'm stumped by both of these errors. Firstly, why should I get an error reported that the PROCEDURE_C is an invalid name? It lives in the user's schema that is running PROCEDURE_A (and so submitting the scheduled job?)
Secondly, why the COMMIT error when running as a PLSQL_BLOCK? I can't see any commit is used within the procedure itself.
Any help would be welcomed. If more info required, please let me know.
Thank you.