Solved

DBMS_SQL package in a trigger

Posted on 2002-05-21
6
688 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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Export table into csv file in oracle 10 241
Deleting Rows from an Oracle Database - Performance 19 53
Oracle dataguard 5 45
plsql job on oracle 18 33
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  â€¦
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.

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