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
Solved

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

Posted on 2009-07-07
8
2,079 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
  • 4
  • 3
8 Comments
 
LVL 40

Accepted Solution

by:
mrjoltcola earned 500 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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

856 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