We help IT Professionals succeed at work.

Oracle directory object create within trigger

Pete2003
Pete2003 asked
on
Hi All,

I have a table of different app settings, I want to trigger a directory object update when the path variable changes in the database. (Using Oracle 10G)

settings table: (setting varchar2, value varchar2)
trigger:
create or replace trigger SETTINGS_UPDATE
AFTER INSERT OR UPDATE ON SETTINGS
FOR EACH ROW WHEN (new.setting = 'DATA_PATH')
BEGIN
   execute immediate 'CREATE OR REPLACE DIRECTORY DATA_PATH AS '||:new.value;
END;
/

now on an inser statement oracle complains about literal strings:
ERROR at line 1:
ORA-01780: string literal required
ORA-06512: at "SETTINGS_UPDATE", line 2
ORA-04088: error during execution of trigger 'SETTINGS_UPDATE'

I have also tried without the 'execute immediate' but then the error tells me that I can't start with 'CREATE...'

Any help welcome.

Thanks
Comment
Watch Question

The "name" should be in single quotes.  Try this for the create or replace line:

execute immediate 'CREATE OR REPLACE DIRECTORY DATA_PATH AS '''||:new.value||''';

Note: There are 3 single quotes on either side of :new.value

Commented:
You might need to execute this as an autonomous transaction.
I'll get you an example.

Commented:
However, once you've fixed that you'll find that you come across the next issue ...

Oracle doesn't permit transaction control statements in triggers.  The "CREATE OR REPLACE DIRECTORY ... " DDL that you are running inside your trigger will do an implicit COMMIT.

ORA-04092: cannot COMMIT in a trigger
ORA-06512: at "ANDY.TEST_TRIGGER", line 4
ORA-04088: error during execution of trigger 'ANDY.TEST_TRIGGER'

Commented:
I wouldn't use an autonomous transaction.  Tom Kyte has a really elegant solution to this problem:

Don't perform the DDL in the trigger.  Instead wrap it in a procedure and schedule a call to that code using DBMS_JOB.

See the following post for details:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:698152300346710865

Commented:
Just in case you decide to do Autonomous transaction:

create or replace trigger SETTINGS_UPDATE
AFTER INSERT OR UPDATE ON SETTINGS
FOR EACH ROW WHEN (new.setting = 'DATA_PATH')

DECLARE
 PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   execute immediate 'CREATE OR REPLACE DIRECTORY DATA_PATH AS '||:new.value;
END;
/
RindbaekSenior Consultant

Commented:
but I would guess that he could call a function/procedure that takes the new.value as an input or a script on os level.

Author

Commented:
Hi Guys,

Thanks for all the answers, I have gone the separate SP route. I have created a separate SP which I call from the trigger:

CREATE OR REPLACE PROCEDURE SP_SET_DIR_OBJECT
(OBJ_NAME IN VARCHAR2, PATH_NAME IN VARCHAR2)
IS BEGIN
   execute immediate 'CREATE OR REPLACE DIRECTORY '||OBJ_NAME||' AS '''||PATH_NAME||'''';
END;
/

I'm however getting a privilage error:

ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SP_SET_DIR_OBJECT", line 4
ORA-06512: at line 1

I have assigned the DBA role and given the user all EXECUTE privileges. I'm actually executing this SP as SYSTEM user and still no luck.

Commented:
Hi Pete,

In order to execute that procedure you need to have been granted the CREATE ANY DIRECTORY privilege.  And it must be a direct grant (i.e. not through a ROLE).

GRANT CREATE ANY DIRECTORY TO <user>;

Author

Commented:
Nope still no luck.

SQL> grant create any directory to system;

Grant succeeded.

ran again same error.
RindbaekSenior Consultant

Commented:
grant it to the user that owns the procedure.

Commented:
Sorry I wasn't particular clear in my last post.  

It's the owner of the procedure SP_SET_DIR_OBJECT that must have the required GRANT, not the caller (SYSTEM in your case).  In what schema is SP_SET_DIR_OBJECT?

Author

Commented:
Thanks guys all works well ....

Last question here.
The dbms_job.submit works fine however there is a snag. The update is not immediate. For me on my own DB it takes about 2-3 seconds to update. There are 2 issues with this:
1) The time will get even longer on a busy DB
2) If I need to use the value right after I do my insert statement then for the 1st few seconds I will be using the old value.

While I do realise that the job.submit fires off a thread which does it thing when it's ready to, is there a way to get this run RIGHT after I do the submit ?