OraFAQ Forum: General » Need help to find out variable in all places

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)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Need help to find out variable in all places [message #644193]

Thu, 29 October 2015 10:12

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

Report message to a moderator

Re: Need help to find out variable in all places [message #644194 is a reply to message #644193]

Thu, 29 October 2015 10:17

gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK

Senior Member

SQL> SELECT text
     FROM dba_source
     WHERE type IN ('PACKAGE','PROCEDURE','FUNCION') AND LOWER(text) LIKE LOWER('%<column_name>%');

Report message to a moderator

Re: Need help to find out variable in all places [message #644195 is a reply to message #644194]

Thu, 29 October 2015 10:45

Xandot
Messages: 235
Registered: January 2014
Location: India

Senior Member

Thanks Gazzag..

Report message to a moderator

Re: Need help to find out variable in all places [message #644196 is a reply to message #644195]

Thu, 29 October 2015 11:20

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

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.

Report message to a moderator

Re: Need help to find out variable in all places [message #644197 is a reply to message #644196]

Thu, 29 October 2015 11:35

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

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.

Report message to a moderator

Re: Need help to find out variable in all places [message #644199 is a reply to message #644197]

Thu, 29 October 2015 13:27

Littlefoot
Messages: 21823
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);

Report message to a moderator

Previous Topic:	unable to use data access control (oradc.ocx) with VB6
Next Topic:	Need your suggest to make image

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Nov 26 03:54:02 CST 2024