Pete2003
asked on
Oracle directory object create within trigger
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
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
You might need to execute this as an autonomous transaction.
I'll get you an example.
I'll get you an example.
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'
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'
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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;
/
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;
/
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.
ASKER
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.
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.
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>;
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>;
ASKER
Nope still no luck.
SQL> grant create any directory to system;
Grant succeeded.
ran again same error.
SQL> grant create any directory to system;
Grant succeeded.
ran again same error.
grant it to the user that owns the procedure.
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?
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?
ASKER
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 ?
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 ?
execute immediate 'CREATE OR REPLACE DIRECTORY DATA_PATH AS '''||:new.value||''';
Note: There are 3 single quotes on either side of :new.value