Re: electronically searching for string in VIEWS

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Wed, 11 Jul 2007 16:22:45 -0700
Message-ID: <1184196165.222568.119420@57g2000hsv.googlegroups.com>

On Jul 11, 5:41 pm, y..._at_vtn1.victoria.tc.ca (Malcolm Dew-Jones) wrote:
> jobs (j..._at_webdos.com) wrote:
>
> : I just noticed view code is not include in user_source. Any way to
> : search for a string in my views?
>
> : Thanks for any help or information.
>
> USER_VIEWS ALL_VIEWS etc, column TEXT.
>
> However the TEXT is a LONG, so I don't know how you can most easily search
> in it.

You can read the long into a pl/sql varchar2 variable if the length of the long is 32k or less and then use instr to seach the variable. I have written code to perform the equilivent task before and it is fairly easy.

For views with text length values greater than 32k you should be able to use a CLOB variable and perform an instr on it. CLOB data types did not exist when I did the above but it should be a straightforward change.

Alternately you can extract the view source into a file and seach the file. You can either just read the view text via dba_views or use dbms_metadata to generate the view source.

HTH -- Mark D Powell -- Received on Wed Jul 11 2007 - 18:22:45 CDT