Find Some Text in TRIGGERs, PROCEDUREs, FUNCTION, PACKAGE BODY

Posted: March 7, 2009 in Oracle, PL/SQL
Tags: , , , , ,
That day I was working on some change in which I needed to find all the stored procedures, triggers, functions and Packages which may contain a variable called “vUserID”. Surprised
Guys to tell you the database of this application was big enough to take me ages to do this task. So what do to…Thinking
After doing some research I found a way to do this using following script.Smile
 

select

DISTINCT(name) from user_source

where

type = PROCEDURE

and

text like ‘%vUserID%’ ;

 _________________________________________________
 

select DISTINCT(name) from user_source

where

type = ‘TRIGGER’

and

text like ‘%vUserID%’ ;

  _________________________________________________
 
select DISTINCT(name) from user_source

where

type = FUNCTION

and

text like ‘%vUserID%’ ;

  _________________________________________________
 

select DISTINCT(name) from user_source

where

type = PACKAGE BODY

and

text like ‘%vUserID%’ ;

 _________________________________________________
And suddenly things became. The above scripts will shows names of all PROCEDURE, TRIGGER, FUNCTION and PACKAGE with the text line “vUserId”. Like wise you can also find the any text in these database objects.
 
So as I always say:- Keep reading, learning and sharing. Please do post your suggestions.
Advertisements
Comments
  1. Joel says:

    fyi, you’ll almost always want to UPPER in those queries, both ends. I rarely know exactly what case things are in 😉

    • vijvipin says:

      Yes Joel, you are quite right :). In fact it is always better to use the UPPER at both ends.
      In my case I was actually pretty sure about the case so it worked for me.

  2. M. Moula says:

    You are a genius. Really. Even Tom Kyte, Oracle guru, is no match – see:

    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:315118200346261192

  3. P. Bergstrand says:

    This is quite brilliant. Thank you for sharing – this will prove useful to me many times in the future I am sure.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s