ORACLE dictionary (all_tab_cols)

CREATE OR REPLACE PROCEDURE sp_search_all_column (v_owner IN VARCHAR, v_search IN VARCHAR, v_type IN VARCHAR)

IS

   v_sql            LONG;

   v_search_count   NUMBER := 0;

 

   CURSOR v_cursor_info

   IS

      SELECT table_name,

             column_name,

             data_type

      FROM   all_tab_cols

      WHERE  owner = v_owner;

/******************************************************************************

   NAME:       sp_search_all_column

   PURPOSE: 오라클 딕셔네리로 부터 해당되는 값이 존재하는 컬럼을 검색한다.

   REVISIONS:

   Ver        Date        Author           Description

   ---------  ----------  ---------------  ------------------------------------

   1.0        2007-11-12          1. Created this procedure.

 

   NOTES: v_search 1 이면 varchar2, number 컬럼 검색, 2이면 varchar2 컬럼만 검색..

 

   Automatically available Auto Replace Keywords:

      Object Name:     sp_search_all_column

      Sysdate:         2007-11-12

      Date and Time:   2007-11-12, 오후 10:06:23, and 2007-11-12 오후 10:06:23

      Username:         fransis lee

      Table Name:       oralce dictionary

 

******************************************************************************/

BEGIN

   DBMS_OUTPUT.put_line (TO_CHAR (SYSDATE, 'yyyy-mm-dd hh24:mi:ss'));

   DBMS_OUTPUT.put_line ('v_search : ' || v_search);

 

 -- Cursor를 FOR문에서 실행시킨다

   FOR v_info_list IN v_cursor_info

   LOOP

      IF v_type = '1'

      THEN

         -- 데이터 타입이 varchar2나 number이라면..

         IF v_info_list.data_type = 'VARCHAR2'

            OR v_info_list.data_type = 'NUMBER'

         THEN

            v_sql :=

               'SELECT COUNT(*) FROM ' || v_info_list.table_name || ' ' || 'WHERE ' || v_info_list.column_name || '='''

               || v_search || '''';

 

            -- DBMS_OUTPUT.put_line ('쿼리 : ' || v_sql);

            EXECUTE IMMEDIATE v_sql

            INTO              v_search_count;

 

              --DBMS_OUTPUT.put_line ('v_search_count : ' || v_search_count);

            -- 쿼리의 결과수가 0보다 크다면..

            IF v_search_count > 0

            THEN

               DBMS_OUTPUT.put_line ('table.column : ' || v_info_list.table_name || '.' || v_info_list.column_name);

            END IF;

         END IF;

      ELSE

         -- 데이터 타입이 varchar2라면..

         IF v_info_list.data_type = 'VARCHAR2'

         THEN

            v_sql :=

               'SELECT COUNT(*) FROM ' || v_info_list.table_name || ' ' || 'WHERE ' || v_info_list.column_name || '='''

               || v_search || '''';

 

            -- DBMS_OUTPUT.put_line ('쿼리 : ' || v_sql);

            EXECUTE IMMEDIATE v_sql

            INTO              v_search_count;

 

              --DBMS_OUTPUT.put_line ('v_search_count : ' || v_search_count);

            -- 쿼리의 결과수가 0보다 크다면..

            IF v_search_count > 0

            THEN

               DBMS_OUTPUT.put_line ('table.column : ' || v_info_list.table_name || '.' || v_info_list.column_name);

            END IF;

         END IF;

      END IF;

   END LOOP;

EXCEPTION

   WHEN NO_DATA_FOUND

   THEN

      DBMS_OUTPUT.put_line ('error message : no data found...');

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.put_line ('error code : ' || TO_CHAR (SQLCODE) || '...');

      DBMS_OUTPUT.put_line ('error message : ' || SQLERRM || '...');

      RAISE;

END sp_search_all_column;

/