REM Filename : search_long.sql

REM Author : Craig Richards

REM Created : 15-March-2007

REM Version : 1.0

REM Modifications :

REM

REM Description : Two Examples to search the long text field in DBA_VIEWS

PROMPT Edit the two pieces of code below to enable you to search the long field in DBA_VIEWS

PROMPT

PROMPT In the example below enter the search text in lower case

REM SET SERVEROUTPUT ON SIZE 10000

REM DECLARE

REM CURSOR c_dbv IS

REM SELECT owner,view_name,text

REM FROM all_views;

REM txt VARCHAR(32000);

REM searchstring VARCHAR(100) := 'scott.emp';

REM BEGIN

REM FOR ct IN c_dbv LOOP

REM IF INSTR(LOWER(ct.text), LOWER(searchstring)) > 0 THEN

REM DBMS_OUTPUT.PUT_LINE(ct.owner||'.'||ct.view_name);

REM END IF;

REM END LOOP;

REM END;

REM /

REM SET SERVEROUTPUT ON

REM

REM BEGIN

REM FOR x IN (SELECT * FROM dba_views ) LOOP

REM IF ( x.view_text LIKE '%customer_address%' ) THEN

REM DBMS_OUTPUT.PUT_LINE( x.view_name );

REM END IF;

REM END LOOP;

REM END;

REM /

PROMPT The above sample will work as long as the view text is 32k or less.

REM End of Script