hmstechsupport
asked on
Database Server Error: ORA-04092: cannot in a trigger ORA-06512: at "SYS.DBMS_ISCHED
Hello all.
I am trying to write a trigger to create a scheduled job. I have created a package for the trigger and that works fine. Every executes on it's own in PL SQL but once I put it all together it fails with the following cryptic message that I cannot figure out. I have given all the grants and if I comment out the "DBMS_SCHEDULER.CREATE_JOB " in sp_schtask.txt then everything completes flalessly. I can run the same DBMS_SCHEDULER.CREATE_JOB command in PL SQL without an issue as the user that the stored proc will execute under.
Can you have a look and tell me why oh why this fails!
I have attached the trigger I have created, and the stored procedure that is called to create the job, and finally the stored procedure that I want the job to run.
Message:
PRM DBAccess Error: Database Server Error: ORA-04092:
cannot in a trigger
ORA-06512: at "SYS.DBMS_ISCHED",
line 99
ORA-06512: at "SYS.DBMS_SCHEDULER", line
262
ORA-06512: at "TCDBA5.SP_SCHTASK", line 97
PRM DBAccess Error: Database Server Error: ORA-04092: cannot in a trigger
ORA-06512: at "SYS.DBMS_ISCHED", line 99
ORA-06512: at "SYS.DBMS_SCHEDULER", line 262
ORA-06512: at "TCDBA5.SP_SCHTASK", line 97
ORA-06512: at "ADMUSER.TIMECONTROL_TASK_ INSERT",li ne 3
ORA-04088: error during execution of trigger 'ADMUSER.TIMECONTROL_TASK_ INSERT'
trigger-PMDB-Task.txt
sp-schtask.txt
sp-Insert-Task.txt
I am trying to write a trigger to create a scheduled job. I have created a package for the trigger and that works fine. Every executes on it's own in PL SQL but once I put it all together it fails with the following cryptic message that I cannot figure out. I have given all the grants and if I comment out the "DBMS_SCHEDULER.CREATE_JOB
Can you have a look and tell me why oh why this fails!
I have attached the trigger I have created, and the stored procedure that is called to create the job, and finally the stored procedure that I want the job to run.
Message:
PRM DBAccess Error: Database Server Error: ORA-04092:
cannot in a trigger
ORA-06512: at "SYS.DBMS_ISCHED",
line 99
ORA-06512: at "SYS.DBMS_SCHEDULER", line
262
ORA-06512: at "TCDBA5.SP_SCHTASK", line 97
PRM DBAccess Error: Database Server Error: ORA-04092: cannot in a trigger
ORA-06512: at "SYS.DBMS_ISCHED", line 99
ORA-06512: at "SYS.DBMS_SCHEDULER", line 262
ORA-06512: at "TCDBA5.SP_SCHTASK", line 97
ORA-06512: at "ADMUSER.TIMECONTROL_TASK_
ORA-04088: error during execution of trigger 'ADMUSER.TIMECONTROL_TASK_
trigger-PMDB-Task.txt
sp-schtask.txt
sp-Insert-Task.txt
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks guys. The message in Oracle did indicate it was a commit or rollback, but I could not find it in my code so I was going nuts!
Where would I put this "pragma autonomous_transaction;"?
Does it go in the TRIGGER, or the Stored Procedure creating the job?
Can you show me an example, or update my file correctly?
Where would I put this "pragma autonomous_transaction;"?
Does it go in the TRIGGER, or the Stored Procedure creating the job?
Can you show me an example, or update my file correctly?
ASKER
Ok. I added it to the top of the procedure and it now works, but I'm thinking that I should break this into another chunk of code that only does the Create Job since the procedure does other things that I may not want committed.
Is this the best way to do it?
CREATE OR REPLACE PROCEDURE "SP_SCHTASK" (v_TASK_ID in NUMBER) AS
PRAGMA AUTONOMOUS_TRANSACTION;
-- Called by another trigger or calling procedure with key to the Primavera Task table
-- The purpose of the routine is to add a newly created task in Primavera into TimeControl
-- The tables CHARGE, CHRHIS, and Activity are updated
-- Logging of some sort should be added
BEGIN
DECLARE
-- Variables
v_prstat VARCHAR(5);
v_ccode VARCHAR2(100);
v_chr_code VARCHAR2(100);
v_wbs_id NUMERIC(11);
v_wbs_name VARCHAR(30);
v_prj_name VARCHAR(30);
v_chr_key NUMERIC(11);
v_chr_prj NUMERIC(11);
v_proj_id NUMERIC(11);
v_schdate VARCHAR(50);
v_jobaction VARCHAR(200);
v_oldname VARCHAR(30);
v_jobexists CHAR(1);
v_jobname VARCHAR(30);
BEGIN
v_prstat := 'OK';
-- Fetch the project data from Primavera. If not found we cannot continue
BEGIN
SELECT UPPER(PROJ_SHORT_NAME),TAS K.PROJ_ID, TASK.WBS_I D,upper(su bstr(TASK_ CODE,1,100 )) INTO v_prj_name,v_proj_id,v_wbs _id,v_ccod e
FROM ADMUSER.PROJECT,ADMUSER.TA SK WHERE TASK.TASK_ID=v_TASK_ID AND TASK.PROJ_ID=PROJECT.PROJ_ ID;
EXCEPTION WHEN No_Data_Found THEN
v_prstat := 'Error';
END;
-- Find the Project Name
IF v_prstat <> 'Error' THEN
BEGIN
SELECT prj_key INTO v_chr_prj FROM tcproj WHERE PRJ_NAME=v_prj_name;
EXCEPTION WHEN No_Data_Found THEN
BEGIN
tcdba5.SP_INSERT_PROJECT(v _proj_id,' A');
SELECT prj_key INTO v_chr_prj FROM tcproj WHERE PRJ_NAME=v_prj_name;
EXCEPTION WHEN No_Data_Found THEN
v_prstat := 'Error';
END;
END;
END IF;
-- Find the WBS Name
IF v_prstat <> 'Error' THEN
BEGIN
SELECT UPPER(WBS_SHORT_NAME) INTO v_wbs_name FROM ADMUSER.PROJWBS WHERE WBS_ID=v_wbs_id AND PROJ_ID=v_proj_id;
EXCEPTION WHEN No_Data_Found THEN
v_prstat := 'Error';
END;
END IF;
v_chr_code := trim(v_prj_name)||'.'||tri m(v_wbs_na me)||'.'|| trim(v_cco de);
-- Check to see if the CHARGE code already exists. If it does it is an error.
IF v_prstat <> 'Error' THEN
BEGIN
SELECT CHR_KEY INTO v_chr_key FROM TCDBA5.CHARGE WHERE CHR_CODE=v_chr_code;
v_prstat := 'Error';
EXCEPTION WHEN No_Data_Found THEN
v_chr_key := -1;
END;
END IF;
IF v_prstat <> 'Error' THEN
BEGIN
SELECT to_char(sysdate+.010,'DD-M ON-YY HH.MI.SS AM')||' US/EASTERN' INTO v_schdate FROM dual;
v_jobaction := 'BEGIN tcdba5.SP_INSERT_TASK('||T rim(to_cha r(v_task_i d))||');en d;';
v_jobname := 'TIMECONTROL_TASK_INSERT_' ||Trim(to_ char(v_tas k_id));
BEGIN
SELECT JOB_NAME INTO v_oldname FROM USER_SCHEDULER_JOBS WHERE JOB_NAME=v_jobname;
BEGIN
DBMS_SCHEDULER.DROP_JOB (v_jobname);
END;
EXCEPTION WHEN No_Data_Found THEN
v_oldname := '';
END;
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => v_jobname,
job_type => 'PLSQL_BLOCK',
job_action => v_jobaction,
start_date => v_schdate,
job_class => 'DEFAULT_JOB_CLASS',
comments => 'Adds a task to TimeControl');
END;
END;
END IF;
END;
END;
Is this the best way to do it?
CREATE OR REPLACE PROCEDURE "SP_SCHTASK" (v_TASK_ID in NUMBER) AS
PRAGMA AUTONOMOUS_TRANSACTION;
-- Called by another trigger or calling procedure with key to the Primavera Task table
-- The purpose of the routine is to add a newly created task in Primavera into TimeControl
-- The tables CHARGE, CHRHIS, and Activity are updated
-- Logging of some sort should be added
BEGIN
DECLARE
-- Variables
v_prstat VARCHAR(5);
v_ccode VARCHAR2(100);
v_chr_code VARCHAR2(100);
v_wbs_id NUMERIC(11);
v_wbs_name VARCHAR(30);
v_prj_name VARCHAR(30);
v_chr_key NUMERIC(11);
v_chr_prj NUMERIC(11);
v_proj_id NUMERIC(11);
v_schdate VARCHAR(50);
v_jobaction VARCHAR(200);
v_oldname VARCHAR(30);
v_jobexists CHAR(1);
v_jobname VARCHAR(30);
BEGIN
v_prstat := 'OK';
-- Fetch the project data from Primavera. If not found we cannot continue
BEGIN
SELECT UPPER(PROJ_SHORT_NAME),TAS
FROM ADMUSER.PROJECT,ADMUSER.TA
EXCEPTION WHEN No_Data_Found THEN
v_prstat := 'Error';
END;
-- Find the Project Name
IF v_prstat <> 'Error' THEN
BEGIN
SELECT prj_key INTO v_chr_prj FROM tcproj WHERE PRJ_NAME=v_prj_name;
EXCEPTION WHEN No_Data_Found THEN
BEGIN
tcdba5.SP_INSERT_PROJECT(v
SELECT prj_key INTO v_chr_prj FROM tcproj WHERE PRJ_NAME=v_prj_name;
EXCEPTION WHEN No_Data_Found THEN
v_prstat := 'Error';
END;
END;
END IF;
-- Find the WBS Name
IF v_prstat <> 'Error' THEN
BEGIN
SELECT UPPER(WBS_SHORT_NAME) INTO v_wbs_name FROM ADMUSER.PROJWBS WHERE WBS_ID=v_wbs_id AND PROJ_ID=v_proj_id;
EXCEPTION WHEN No_Data_Found THEN
v_prstat := 'Error';
END;
END IF;
v_chr_code := trim(v_prj_name)||'.'||tri
-- Check to see if the CHARGE code already exists. If it does it is an error.
IF v_prstat <> 'Error' THEN
BEGIN
SELECT CHR_KEY INTO v_chr_key FROM TCDBA5.CHARGE WHERE CHR_CODE=v_chr_code;
v_prstat := 'Error';
EXCEPTION WHEN No_Data_Found THEN
v_chr_key := -1;
END;
END IF;
IF v_prstat <> 'Error' THEN
BEGIN
SELECT to_char(sysdate+.010,'DD-M
v_jobaction := 'BEGIN tcdba5.SP_INSERT_TASK('||T
v_jobname := 'TIMECONTROL_TASK_INSERT_'
BEGIN
SELECT JOB_NAME INTO v_oldname FROM USER_SCHEDULER_JOBS WHERE JOB_NAME=v_jobname;
BEGIN
DBMS_SCHEDULER.DROP_JOB (v_jobname);
END;
EXCEPTION WHEN No_Data_Found THEN
v_oldname := '';
END;
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => v_jobname,
job_type => 'PLSQL_BLOCK',
job_action => v_jobaction,
start_date => v_schdate,
job_class => 'DEFAULT_JOB_CLASS',
comments => 'Adds a task to TimeControl');
END;
END;
END IF;
END;
END;
I agree, you may want to encapsulate just the one part inside a separate AUTONOMOUS procedure, so the rest of the main procedure is under the outer transaction control.
ASKER
Thanks. I should have posted earlier and saved myself the frustration...but hey that's how we learn. Appreciate the quick and complete answers.
It's not the most elegant solution, but its not your fault that Oracle decided to make that API implicit commit. It was probably due to all of the support calls about jobs that weren't scheduled. That is an old newbie mistake, scheduling jobs but not using a commit statement.
http://asktom.oracle.com/tkyte/autonomous/index.html
http://www.oracle-base.com/articles/misc/AutonomousTransactions.php