среда, 14 сентября 2011 г.

Поиск текстового значения в таблицах схемы Oracle RDBMS

  1. Создать следующую функцию:
    sql> CREATE OR REPLACE FUNCTION FIND_STR_VALUE(  
        SCHEMA_NAME IN VARCHAR2 ,  
        FIND_STR    IN VARCHAR2 )  
       RETURN VARCHAR2  
      AS  
       match_count   INTEGER;  
       query_str     VARCHAR2(300);  
       result_string VARCHAR2(32767);  
      BEGIN  
       result_string:='TABLE_NAME COLUMN_NAME COUNT'||chr(13)||chr(10);  
       FOR t IN  
       (SELECT table_name, column_name, owner  
       FROM all_tab_columns a  
       WHERE a.owner=SCHEMA_NAME AND 
             a.data_type IN ( 'VARCHAR2', 'NVARCHAR2','VARCHAR','CHAR','NCHAR'))  
       LOOP  
        match_count := 0;  
        query_str  := 'SELECT COUNT(*) FROM '||t.owner||'.' || t.table_name || 
                      ' WHERE to_char(' || t.column_name || ') LIKE :1';  
        EXECUTE IMMEDIATE query_str INTO match_count USING '%'||FIND_STR||'%';  
        IF match_count > 0 THEN  
         result_string:=result_string|| t.table_name ||' '||t.column_name||
                        ' '||match_count||chr(13)||chr(10);  
        END IF;  
       END LOOP;  
       RETURN result_string;  
      END FIND_STR_VALUE;  
    
  2. Запустить выполнение функции (первый параметр имя схемы, второй - строка для поиска):
    sql> SELECT find_str_value('APEX_040100','welcome') FROM dual; 
    
  3. Пример выполненного запроса:
    TABLE_NAME | COLUMN_NAME | COUNT  
    WWV_FLOW_STEP_ITEMS | ITEM_DEFAULT | 1
    WWV_FLOW_STEP_ITEM_HELP | HELP_TEXT | 2 
    APEX_APPLICATION_PAGE_DB_ITEMS | HELP_TEXT | 1 
    APEX_APPLICATION_PAGE_ITEMS | ITEM_HELP_TEXT | 2  
    APEX_APPLICATION_PAGE_ITEMS | COMPONENT_SIGNATURE | 1  
    APEX_APPLICATION_PAGE_ITEMS | ITEM_DEFAULT | 1