Home » SQL & PL/SQL » SQL & PL/SQL » Validating a DDL before execution
Validating a DDL before execution [message #199463] Tue, 24 October 2006 09:48 Go to next message
yonieilon
Messages: 13
Registered: October 2006
Junior Member
I would like to know if there is a way to validate a DDL statement before it is executed and returns an error code.

I am referring not only to the validity of the DDL's syntax, but more important to such things as: 1. Trying to drop an object that does not exist.
2. Trying to drop a table with FK's referencing it's PK.
etc etc etc...

Is there any way of doing so without actually executing the DDL?
Re: Validating a DDL before execution [message #199481 is a reply to message #199463] Tue, 24 October 2006 14:07 Go to previous messageGo to next message
orausern
Messages: 817
Registered: December 2005
Senior Member
I guess that could be done via ddl triggers..did you try that?
Re: Validating a DDL before execution [message #199483 is a reply to message #199463] Tue, 24 October 2006 14:23 Go to previous messageGo to next message
jigar
Messages: 74
Registered: July 2002
Member
You can use checking different tables like

SELECT * FROM ALL_TABLES
WHERE TABLE_NAME = :TABLE_BEING_DROPPED
AND OWNER = :OWNER_OF_TABLE;

Re: Validating a DDL before execution [message #199540 is a reply to message #199481] Wed, 25 October 2006 01:21 Go to previous messageGo to next message
yonieilon
Messages: 13
Registered: October 2006
Junior Member
What do you mean exactly?

I am using DDL triggers, my problem is how to validate the DDL statement in these triggers...

Thanks for you reply,
Yoni.
Re: Validating a DDL before execution [message #199543 is a reply to message #199540] Wed, 25 October 2006 01:29 Go to previous messageGo to next message
orausern
Messages: 817
Registered: December 2005
Senior Member
Sorry, I guess that suggestion may not work. I was referring to something like what is given here:

http://www.jlcomp.demon.co.uk/faq/DDL_tracker.html

but I guess you have a different and more difficult requirement.
Re: Validating a DDL before execution [message #199546 is a reply to message #199543] Wed, 25 October 2006 01:36 Go to previous message
orausern
Messages: 817
Registered: December 2005
Senior Member
One link that might be of help...

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:1380003556171

Previous Topic: An initial blank line in a spool file
Next Topic: Syntax help
Goto Forum:
  


Current Time: Tue Dec 06 02:25:25 CST 2016

Total time taken to generate the page: 0.15357 seconds