Solved

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

Posted on 2009-07-07
8
1,998 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:hmstechsupport
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

This article describes some very basic things about SQL Server filegroups.
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
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.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

763 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now