?
Solved

Database Server Error: ORA-04092: cannot  in a trigger ORA-06512: at "SYS.DBMS_ISCHED

Posted on 2009-07-07
8
Medium Priority
?
2,169 Views
Last Modified: 2013-12-07
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",line 3
  ORA-04088: error during execution of trigger 'ADMUSER.TIMECONTROL_TASK_INSERT'


trigger-PMDB-Task.txt
sp-schtask.txt
sp-Insert-Task.txt
0
Comment
Question by:hmstechsupport
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 40

Accepted Solution

by:
mrjoltcola earned 2000 total points
ID: 24799922
With DBMS_SCHEDULER (a 10g feature) an implicit COMMIT is issued by the API, so this is why Oracle is complaining about using it inside a trigger.

You could do a few things:

1) Use an AUTONOMOUS transaction
2) Use the older DBMS_JOB API which does not do a COMMIT

In this case I would probably go with (1), since the DBMS_JOB is deprecated.
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24799945
autonomous transactions are to be used sparingly. I think they might fit this case. Just make sure you know what the side effects are. The overall transaction cannot rollback the effects of the trigger.

http://asktom.oracle.com/tkyte/autonomous/index.html

http://www.oracle-base.com/articles/misc/AutonomousTransactions.php
0
 

Author Comment

by:hmstechsupport
ID: 24799979
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?
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:hmstechsupport
ID: 24800046
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),TASK.PROJ_ID,TASK.WBS_ID,upper(substr(TASK_CODE,1,100)) INTO v_prj_name,v_proj_id,v_wbs_id,v_ccode
    FROM ADMUSER.PROJECT,ADMUSER.TASK 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)||'.'||trim(v_wbs_name)||'.'||trim(v_ccode);

  -- 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-MON-YY HH.MI.SS AM')||' US/EASTERN' INTO v_schdate FROM dual;
      v_jobaction      := 'BEGIN tcdba5.SP_INSERT_TASK('||Trim(to_char(v_task_id))||');end;';
      v_jobname      := 'TIMECONTROL_TASK_INSERT_'||Trim(to_char(v_task_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;

0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24800088
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.

0
 

Author Closing Comment

by:hmstechsupport
ID: 31600914
Thanks.  I should have posted earlier and saved myself the frustration...but hey that's how we learn.  Appreciate the quick and complete answers.
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24800112
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.
0

Featured Post

Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to recover a database from a user managed backup
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question