Link to home
Start Free TrialLog in
Avatar of hmstechsupport
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",line 3
  ORA-04088: error during execution of trigger 'ADMUSER.TIMECONTROL_TASK_INSERT'


trigger-PMDB-Task.txt
sp-schtask.txt
sp-Insert-Task.txt
ASKER CERTIFIED SOLUTION
Avatar of mrjoltcola
mrjoltcola
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
Avatar of hmstechsupport
hmstechsupport

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?
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;

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.

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.