在oracle数据库中动态搜索2个值

我对你的代码做了一些小修改:

DECLARE
   match_count       INTEGER;
   v_search_string   VARCHAR2 (11) := 'JOHN';
   v_search_date      DATE          := date '1984-01-01';
BEGIN
   FOR t IN (
     SELECT A.owner, A.table_name, A.column_name text_column_name, B.column_name date_column_name
     FROM   all_tab_columns A
            JOIN all_tab_columns B
               ON  A.TABLE_NAME = B.TABLE_NAME
               AND A.OWNER = B.OWNER
               AND A.COLUMN_NAME IN ('NAME', 'FULLNAME')
               AND B.DATA_TYPE = 'DATE'
               AND A.TABLE_NAME LIKE 'DATA%'
   ) LOOP
      BEGIN
         EXECUTE IMMEDIATE
               'SELECT count(*) FROM '
            || t.owner
            || '.'
            || t.table_name
            || ' WHERE '
            || t.text_column_name
            || ' = :1'
            || ' and '
            || t.date_column_name
            || ' = :2'
            INTO match_count
            USING v_search_string, v_search_date;

         IF match_count > 0
         THEN
            DBMS_OUTPUT.put_line (
                'Found! '||t.table_name
               );
         ELSE
            DBMS_OUTPUT.put_line (
                'No matches for '||t.table_name||'('||t.text_column_name||','||t.date_column_name||')'
               );
         END IF;
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line (
                  'Error encountered trying to read from '
               || t.owner
               || '.'
               || t.table_name);
      END;
   END LOOP;
END;
/

您可以尝试使用 all_tab_columns 并在动态where子句中查询相应的表。

SET serveroutput ON 
DECLARE 
    v_count INTEGER; 
BEGIN 
    FOR r IN (SELECT a.table_name, 
                     ' WHERE ' 
                     || LISTAGG(CASE data_type 
                                  WHEN 'DATE' THEN column_name 
                                                   || ' = DATE ' 
                                                   || '''1984-01-01''' 
                                  ELSE column_name 
                                       || ' = ' 
                                       || '''JOHN''' 
                                END, ' or ') 
                          WITHIN GROUP( ORDER BY column_name ) AS where_clause 
              FROM   user_tab_columns a 
              WHERE  column_name IN ( 'NAME', 'FULLNAME' ) 
                      OR ( data_type = 'DATE' 
                           AND column_name IN ( 'DTTM', 'BDAY', 'DATEFLD', 
                                                'DTFIELD' 
                                              ) ) 
              GROUP  BY table_name) LOOP 
        EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM '|| r.table_name|| 
        r.where_clause 
        INTO 
        v_count; 

        IF v_count > 0 THEN 
          dbms_output.Put_line(r.table_name); 
        END IF; 
    END LOOP; 
END; 

/ 

注意,我给你的是一个例子,它给了你一些想法,你可以构造它并调整隐式游标循环的查询(在 r in () )为了满足你的需要 AND/OR/JOIN 条件 INTERSECT 不管你要什么接线员等。我这样做是为了从我这边测试。

编辑 :另一个选项是强制方法,即使用动态where子句搜索数据库中的所有表,如果在表中找不到where子句中的列,则忽略异常。

SET serveroutput ON 
DECLARE 
    v_count        NUMBER; 
    v_where_clause VARCHAR2(400) := 
    ' WHERE DTTM = DATE ''1984-01-01'' AND FULLNAME = ''JOHN'''; 
BEGIN 
    FOR r IN (SELECT owner, 
                     table_name 
              FROM   all_tables) LOOP 
        BEGIN 
            EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM '|| r.owner|| '.'|| 
            r.table_name 
            || 
            v_where_clause INTO v_count; 

            IF v_count > 0 THEN 
              dbms_output.put_line(r.owner||'.'||r.table_name); 
            END IF; 
        EXCEPTION 
            WHEN OTHERS THEN 
              NULL; --It is ok in this case as you know why it fails. 
        END; 
    END LOOP; 
END; 
/