Home » SQL & PL/SQL » SQL & PL/SQL » drop column p1 if this exists
drop column p1 if this exists [message #213515] Thu, 11 January 2007 01:55 Go to next message
steffeli
Messages: 112
Registered: July 2006
Senior Member
hello

I want to drop the column p1 if it exists in the table tab (it does not always exist in this procedure):
alter table tab
drop ( p1 )


If p1 does not exist, I get ERROR: ORACLE execute error: ORA-00904: "p1": invalid identifier.

How can I prevent this error?

Thanks
Stefan
Re: drop column p1 if this exists [message #213520 is a reply to message #213515] Thu, 11 January 2007 02:03 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
The solution is quite simple: Only drop the column from tables where it exists.
Re: drop column p1 if this exists [message #213522 is a reply to message #213515] Thu, 11 January 2007 02:06 Go to previous messageGo to next message
tries
Messages: 3
Registered: January 2007
Junior Member
I'll suggest to check first the system tables.
You can find in the view sys.USER_TAB_COLUMNS which colums are defined for which tables.
Re: drop column p1 if this exists [message #213534 is a reply to message #213522] Thu, 11 January 2007 03:06 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
Or attempt to drop it regardless and handle ORA-00904 errors.
Re: drop column p1 if this exists [message #213627 is a reply to message #213515] Thu, 11 January 2007 10:00 Go to previous message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
By the way, I hope that you are NOT using tab as a table name. This is the name of an oracle view that is a shortcut to a list of all your tables and views.
Previous Topic: select all but one column from a large table
Next Topic: one question!
Goto Forum:
  


Current Time: Sun Dec 04 08:42:09 CST 2016

Total time taken to generate the page: 0.11680 seconds