Home » Other » General » Need help to find out variable in all places (Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production)
Need help to find out variable in all places [message #644193] Thu, 29 October 2015 10:12 Go to next message
Xandot
Messages: 235
Registered: January 2014
Location: India
Senior Member
Hello All,

I have one requirement to change table column size before it was varchar(50) now client wants to change this with varchar2(200).

Its simple to change the size of table column but my problem is I don't know how many package/functions/procedures in application using this columns with some hard coded varchar2(50) size not the %type. my question is how to find out or change the size of variable in all the places ?

Thanks
Re: Need help to find out variable in all places [message #644194 is a reply to message #644193] Thu, 29 October 2015 10:17 Go to previous messageGo to next message
gazzag
Messages: 906
Registered: November 2010
Location: Bristol, UK
Senior Member
SQL> SELECT text
     FROM dba_source
     WHERE type IN ('PACKAGE','PROCEDURE','FUNCION') AND LOWER(text) LIKE LOWER('%<column_name>%');
Re: Need help to find out variable in all places [message #644195 is a reply to message #644194] Thu, 29 October 2015 10:45 Go to previous messageGo to next message
Xandot
Messages: 235
Registered: January 2014
Location: India
Senior Member
Thanks Gazzag..
Re: Need help to find out variable in all places [message #644196 is a reply to message #644195] Thu, 29 October 2015 11:20 Go to previous messageGo to next message
Michel Cadot
Messages: 65254
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Of course, this assumes the column name is never given as a parameter value and used in a dynamic statement or things like that.
And this, of course, does not address the case of the column usage in the application code itself.

Re: Need help to find out variable in all places [message #644197 is a reply to message #644196] Thu, 29 October 2015 11:35 Go to previous messageGo to next message
Michel Cadot
Messages: 65254
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And, of course, the "client" should know itself were the column is used reading the specification as well as searching in the code they have in their code repository.

Re: Need help to find out variable in all places [message #644199 is a reply to message #644197] Thu, 29 October 2015 13:27 Go to previous message
Littlefoot
Messages: 21266
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Next time, consider declaring your variables as
l_ename emp.ename%type;

instead of
l_ename varchar2(50);
Previous Topic: unable to use data access control (oradc.ocx) with VB6
Next Topic: Need your suggest to make image
Goto Forum:
  


Current Time: Fri Dec 15 00:43:41 CST 2017

Total time taken to generate the page: 0.02282 seconds