Oracle 모든 테이블의 모든 열에서 문자열 검색

Oracle 모든 테이블의 모든 열에서 문자열 검색

이 질문에는 이미 다음과 같은 답이 있습니다.

모든 테이블의 모든 필드에서 특정 값 검색(Oracle) (17개 답변)

마감됨8년 전에.

오라클 데이터베이스에서 모든 테이블과 열에 있는 문자열을 검색해야 합니다.온라인에서 찾은 아래 쿼리가 있는데 실행하면 다음 오류가 발생합니다.

어떤 도움이든 감사합니다.

ORA-06550: line 6, column 31:
PL/SQL: ORA-00904: "COLUMN_NAME": invalid identifier
ORA-06550: line 6, column 12:
PL/SQL: SQL Statement ignored
ORA-06550: line 8, column 30:
PLS-00364: loop index variable 'T' use is invalid
ORA-06550: line 7, column 4:
PL/SQL: Statement ignored
ORA-06550: line 12, column 38:
PLS-00364: loop index variable 'T' use is invalid
ORA-06550: line 12, column 16:
PL/SQL: Statement ignored

BEGIN  
  FOR t IN (SELECT table_name, column_name FROM all_tables) LOOP   
   EXECUTE IMMEDIATE    
    'SELECT COUNT(*) FROM '||t.table_name||' WHERE '||t.column_name||' = :1'   
     INTO match_count  
       USING v_search_string; 
          IF match_count > 0 THEN 
               dbms_output.put_line( t.table_name ||' '||t.column_name||' '||match_count );
           END IF; 
  END LOOP;
END;
/

최소한 ALL_TABLES가 아닌 ALL_TAB_COLUMNS를 쿼리해야 합니다.

DECLARE
  match_count integer;
  v_search_string varchar2(4000) := <<string you want to search for>>;
BEGIN  
  FOR t IN (SELECT owner, table_name, column_name FROM all_tab_columns) LOOP   
    EXECUTE IMMEDIATE    
      'SELECT COUNT(*) FROM '||t.owner || '.' || t.table_name||
      ' WHERE '||t.column_name||' = :1'   
       INTO match_count  
      USING v_search_string; 
    IF match_count > 0 THEN 
      dbms_output.put_line( t.owner || '.' || t.table_name ||' '||t.column_name||' '||match_count );
    END IF; 
  END LOOP;
END;
/

그러나 문자열을 찾는 경우 문자열을 저장할 수 있는 열만 찾는 것이 좋습니다.예를 들어, DATE 열에서 문자열을 검색하는 것은 의미가 없습니다.또한 BLOB 열에 포함된 내용과 BLOB 열의 이진 형식을 구문 분석할 수 있는 기능에 대한 사전 지식이 충분하지 않으면 BLOB 열에서 문자열을 검색하는 것은 의미가 없습니다.그런 점을 감안할 때, 당신은 더 비슷한 것을 원할 것입니다.

DECLARE
  match_count integer;
  v_search_string varchar2(4000) := <<string you want to search for>>;
BEGIN  
  FOR t IN (SELECT owner,
                   table_name, 
                   column_name 
              FROM all_tab_columns
             WHERE data_type in ('CHAR', 'VARCHAR2', 'NCHAR', 'NVARCHAR2', 
                                 'CLOB', 'NCLOB') ) 
  LOOP   
    BEGIN
      EXECUTE IMMEDIATE    
        'SELECT COUNT(*) FROM '||t.owner || '.' || t.table_name||
        ' WHERE '||t.column_name||' = :1'   
         INTO match_count  
        USING v_search_string; 
      IF match_count > 0 THEN 
        dbms_output.put_line( t.owner || '.' || t.table_name ||' '||t.column_name||' '||match_count );
      END IF; 
    EXCEPTION
      WHEN others THEN
        dbms_output.put_line( 'Error encountered trying to read ' ||
                              t.column_name || ' from ' || 
                              t.owner || '.' || t.table_name );
    END;
  END LOOP;
END;
/

물론, 이 작업은 엄청나게 느릴 것입니다. 테이블의 모든 문자열 열에 대해 모든 테이블을 한 번씩 전체 스캔합니다.적당히 큰 테이블과 적당한 수의 문자열 열을 사용하면 시간이 꽤 걸릴 수 있습니다.

언급URL : https://stackoverflow.com/questions/6389666/oracle-search-all-tables-all-columns-for-string