Home » SQL & PL/SQL » SQL & PL/SQL » removing a column
removing a column [message #284278] Thu, 29 November 2007 05:17 Go to next message
khresmoi
Messages: 24
Registered: October 2007
Junior Member
Hi,

I've to remove a column from a view and I need to check if that will affect any SP,function or any other view. Is there any system table which stores columns used in view/SP/function defenition? is there any other method?

Thanks,
Khresmoi

Re: removing a column [message #284281 is a reply to message #284278] Thu, 29 November 2007 05:26 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member

you should have tried it by now.


regards,
Re: removing a column [message #284284 is a reply to message #284281] Thu, 29 November 2007 05:30 Go to previous messageGo to next message
khresmoi
Messages: 24
Registered: October 2007
Junior Member
Sorry, I didn't get it..
Re: removing a column [message #284288 is a reply to message #284284] Thu, 29 November 2007 05:36 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I believe that user_dependencies/all_dependencies can help you out. Next time, check out that fine online documentation Oracle has made available for us. All is in there.

MHE

[Updated on: Thu, 29 November 2007 05:36]

Report message to a moderator

Re: removing a column [message #284294 is a reply to message #284278] Thu, 29 November 2007 05:53 Go to previous messageGo to next message
khresmoi
Messages: 24
Registered: October 2007
Junior Member
Thanks Maaher.

An info for the benefit of others - User_source table will be of help for SP/function/trigger.
Re: removing a column [message #284297 is a reply to message #284294] Thu, 29 November 2007 05:57 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
but is USER_SOURCE helpful in your case.it will just show the text of your sub programs.as Maaher said USER_DEPENDENCIES will show the dependencies among objects.USER_OBJECTS will show you the status of the objets when changes are made to the base objects on which it depends.

updated :typo error

regards,

[Updated on: Thu, 29 November 2007 06:01]

Report message to a moderator

Re: removing a column [message #284306 is a reply to message #284297] Thu, 29 November 2007 06:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And of course, if you use dynamic SQL all dependency tracking are impossible.

Regards
Michel
Re: removing a column [message #284308 is a reply to message #284297] Thu, 29 November 2007 06:09 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:


but is USER_SOURCE helpful in your case?



It might be wise to check it, too.

Some smeghead eeehh.... I mean some other developer might have used the column in a way the dependencies don't show, like in dynamic SQL.

( I would also run a recursive grep on important script / application source directories, if it's a critical application. ) Wink

[Edit: Whoops, Michel was faster again]

[Updated on: Thu, 29 November 2007 06:12]

Report message to a moderator

Re: removing a column [message #284319 is a reply to message #284278] Thu, 29 November 2007 06:31 Go to previous message
khresmoi
Messages: 24
Registered: October 2007
Junior Member
USER_SOURCE was of help to me before Maaher posted abt ALL_DEPENDENCIES. It just has the code for all subprograms, so i ran a query with LIKE '%MyColumnName%'.

yes, of course, USER_SOURCE will not give any additional info from what ALL_DEPENDENCIES.
Previous Topic: Simple SQL Question
Next Topic: TRIGGER
Goto Forum:
  


Current Time: Sun Dec 11 00:43:34 CST 2016

Total time taken to generate the page: 0.08553 seconds