Home » SQL & PL/SQL » SQL & PL/SQL » Invalid DDL operation in system triggers
Invalid DDL operation in system triggers [message #243193] Wed, 06 June 2007 07:39 Go to next message
saibal
Messages: 111
Registered: March 2007
Location: India
Senior Member
The AFTER SUSPEND triggers cannot perform certain DDLs like Alter tablespace etc. to actually fix the problems they diagnose. They will raise the error ORA-30511. Can anyone suggest a workaround this?
Re: Invalid DDL operation in system triggers [message #243214 is a reply to message #243193] Wed, 06 June 2007 08:36 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Put the statement in a job.
Instead of "execute immediate 'blabla';"
Use: "dbms_job.submit(job,'begin execute immediate ''blabla''; end;'); commit;".
Of course with a "pragma autonomous_transaction".

Regards
Michel
Re: Invalid DDL operation in system triggers [message #243255 is a reply to message #243214] Wed, 06 June 2007 11:07 Go to previous messageGo to next message
saibal
Messages: 111
Registered: March 2007
Location: India
Senior Member
I found two other workarounds. Can you share your opinion on these?
1)Create a PL/SQL package that reads SQL statements from the table and executes them.
2) Have the AFTER SUSPEND trigger write the SQL statement necessary to fix a problem in a table.
Re: Invalid DDL operation in system triggers [message #243265 is a reply to message #243255] Wed, 06 June 2007 11:56 Go to previous message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
But who read the "statement table"? A job!
I think it is better to spawn a job from the trigger instead of waiting that your polling job reaches your statement.
But why not if you reach a suspend event many times.

Regards
Michel
Previous Topic: How to generate SQL query
Next Topic: What is the difference between SOME and ANY
Goto Forum:
  


Current Time: Tue Feb 11 13:37:00 CST 2025