Solved

DBMS_SQL package in a trigger

Posted on 2002-05-21
6
685 Views
Last Modified: 2008-02-01
Hey gurus...

Is there a workaround for the following: (?)

I have a trigger (on insert) that wants to create a function (CREATE OR REPLACE FUNCTION fn ...)

I can build the function text in a string, then i try to use DBMS_SQL to open a cursor, parse the text, and close the cursor. (This is the same procedure that will work for normal DDL statement processing.)

But I'm getting an Oracle error 4092 - Cannot commit in a trigger.

Of course you can't commit in a trigger, but the DBMS_SQL package is in fact attempting it...  Is there a method of creating functions from a trigger that is different from the method i tried?

any help is appreciated.

randy
0
Comment
Question by:randyd
  • 3
  • 3
6 Comments
 
LVL 4

Expert Comment

by:asimkovsky
ID: 7024541
Yes, you can have your trigger call another procedure that contains the code to create your function. However, the trick is that the other procedure must be declared as AUTONOMOUS.  Anytime you execute a DDL statement, it causes an implicit commit, which is why you are getting this error.  Using an autonomous transaction creates another transaction independent of the first, and you can commit within that second transaction without affecting the state of the first one.


Andrew
0
 
LVL 3

Author Comment

by:randyd
ID: 7024572
hey andrew thanks..  

just before i got this response i entered:
PRAGMA AUTONOMOUS_TRANSACTION
in the top of my procedure and got past the initial COMMIT problem.. now it tells me i have insufficient privileges...

I have granted execute on DBMS_SQL and DBMS_SQL_SYS to the user who created the procedure and the trigger - and it still says insufficient privileges...

I'll be tracking this down - unless omeone else has a pointer...

I'll award points soon.
0
 
LVL 4

Accepted Solution

by:
asimkovsky earned 200 total points
ID: 7024654
Where in the package are you getting the error? Could it be with the CREATE FUNCTION statement? When you are issuing DDL from PL/SQL, the user issuing it must have the DDL privilege granted directly to him, and not through a role.


Andrew
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 3

Author Comment

by:randyd
ID: 7024851
yes i needed to grant create procedure directly to the creator of the trigger/procedure...  now it works...

a struggle :) but it does what i wanted when i started...

thanks andrew for the pointers. enjoy the points.

randy
0
 
LVL 4

Expert Comment

by:asimkovsky
ID: 7025625
Why just a grade of B? Did the answer not suit your needs?

Andrew

0
 
LVL 3

Author Comment

by:randyd
ID: 7025636
hey no offense..

i have reserved A grades on my few posts for the times people gave code examples

thanks again
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

785 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