12 July, 2012

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

1 comment:

  1. Wait around! Or perhaps definitely not! I merely composed to Luis’s
    e-mail correct from a several place and this also occasion I didnt get
    an blunder communication. This implies this individual in all probability acquired the knowledge he or she desired out of me (checking account variety, etcetera) and
    now blocked the e-mail correct through an oversight communication which
    in turn affirms “Eric’s e-mail deal with will not be logical anymore”, nevertheless
    it’s likely up and running once again.
    Confirmation is usually My partner and i
    provided some text to be able to the handle from a diverse location (among our alternative
    e-mail handles) and that i did not experience an blunder message… darn…tghthgthgg Charles please help myself abide by my own application
    with your company. I have already routed my own application to the corporation
    e-mail, you need to I needed your own pressing reply.
    Feel free to visit my homepage Genital Warts Cream

    ReplyDelete

How to Pass Parameters to ActionListener in ADF

In some cases, it is required to pass a value to ActionListener of ADF Button. The method that can be invoked by actionListeners has only...