We help IT Professionals succeed at work.
Get Started

Oracle directory object create within trigger

Pete2003
Pete2003 asked
on
1,134 Views
Last Modified: 2013-12-18
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
Commented:
This problem has been solved!
Unlock 1 Answer and 12 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE