Search about Text in Schema

I will present today solution help us in searching about specific text in entire schema.

Suppose you want to search about 'MANAGER' string at entire tables in your schema.
You will do select statement against every table in your schema and you will will identify every column in table at select statement.

So I developed generic procedure has input search text and generate select statement against every table in schema and execute it and return the result in DBMS OUTPUT console.

The procedure return ROWD per every table has search text in any of its own columns and print it in DBMS OUTUT console in below format

<<ROWID>> IN TABLE <<TABLE_NAME>>

The procedure Code

 CREATE OR REPLACE PROCEDURE SEARCH (IN_TXT VARCHAR2)  
 IS  
   LC$WHERE_CLAUSE  VARCHAR2 (4000);  
   LC$OUTPUT     VARCHAR2 (100);  
   TYPE REF_CUR IS REF CURSOR;  
   LCUR$REF_CUR   REF_CUR;  
   CURSOR LCUR$TABLES  
   IS  
    SELECT TABLE_NAME FROM USER_ALL_TABLES;  
   CURSOR LCUR$TAB_COLUMNS (IN_TABLE_NAME VARCHAR2)  
   IS  
    SELECT COLUMN_NAME, ROWNUM COL_ORDER  
     FROM USER_TAB_COLS  
     WHERE TABLE_NAME = IN_TABLE_NAME AND DATA_TYPE LIKE '%CHAR%';  
 BEGIN  
  <<TABLES_LOOP>>  
   FOR LREC$TABLES IN LCUR$TABLES  
   LOOP  
    <<COLS_LOOP>>  
    FOR LREC$TAB_COLUMNS IN LCUR$TAB_COLUMNS (LREC$TABLES.TABLE_NAME)  
    LOOP  
      IF LREC$TAB_COLUMNS.COL_ORDER = 1  
      THEN  
       LC$WHERE_CLAUSE :=  
          ' WHERE '  
         || LREC$TAB_COLUMNS.COLUMN_NAME  
         || ' LIKE''%'  
         || IN_TXT  
         || '%''';  
      ELSE  
       LC$WHERE_CLAUSE :=  
          LC$WHERE_CLAUSE  
         || ' OR '  
         || LREC$TAB_COLUMNS.COLUMN_NAME  
         || ' LIKE ''%'  
         || IN_TXT  
         || '%''';  
      END IF;  
    END LOOP;  
    IF LC$WHERE_CLAUSE IS NOT NULL  
    THEN  
      OPEN LCUR$REF_CUR FOR  
         'SELECT ROWID ||'' IN TABLE '  
       || LREC$TABLES.TABLE_NAME  
       || ''' FROM '  
       || LREC$TABLES.TABLE_NAME  
       || LC$WHERE_CLAUSE;  
      FETCH LCUR$REF_CUR INTO LC$OUTPUT;  
     <<FETCH_LOOP>>  
      LOOP  
       EXIT WHEN LCUR$REF_CUR%NOTFOUND;  
       DBMS_OUTPUT.PUT_LINE (LC$OUTPUT);  
       FETCH LCUR$REF_CUR INTO LC$OUTPUT;  
      END LOOP;  
      CLOSE LCUR$REF_CUR;  
      LC$WHERE_CLAUSE :=NULL;  
    END IF;  
   END LOOP;  
 END;  

Now Let's Test procedure in SCOTT schema

I will run below code

 EXEC SEARCH('MANAGER');  

The output will be

Thanks