Search 2 values in Oracle DB Dynamically

Question

Good day folks,

Trying to wrap my head around this and manage to get it working to some extent - but still struggling a bit.

I want to find two values in two columns for this example: VARCHAR and DATE JOHN 1984-01-01

Let's say this is John's birth date.

I want to be able to find the value JOHN with the DATE value in lets say 2000 tables and to complicate things the column names are not all the same. The data types are.

The following selects all the tables with the two column names which I require.

SELECT A.TABLE_NAME, A.COLUMN_NAME, B.COLUMN_NAME
  FROM all_tab_columns A
       JOIN all_tab_columns B
          ON     A.TABLE_NAME = B.TABLE_NAME
             AND B.DATA_TYPE = 'DATE'
             AND A.COLUMN_NAME IN ('NAME', 'FULLNAME')

So, now I get the tables I need which has the data type DATE where the column names NAME and FULLNAME exists.

So now of the 2000 tables I have 300 that match my criteria. I want to search through the tables found for JOHN with the date 1984-01-01 where the JOHN could be in FULLNAME or NAME and the 1984-01-01 could be in any column name ie. DTTM, BDAY,DATEFLD,DTFIELD etc.

I want the TABLE_NAME where these two values exists in the same row, nothing else.

I have looked at these type of examples:

Search All Fields In All Tables For A Specific Value (Oracle)

But keep getting issues. What am I missing?

DECLARE
   match_count       INTEGER;
   v_search_string   VARCHAR2 (11) := 'JOHN';
BEGIN
   FOR t
      IN (SELECT A.owner, A.table_name, A.column_name
            FROM all_tab_columns A
                 JOIN all_tab_columns B
                    ON     A.TABLE_NAME = B.TABLE_NAME
                       AND A.COLUMN_NAME IN ('NAME', 'FULLNAME')
                       AND B.DATA_TYPE = 'DATE'
                       AND A.TABLE_NAME LIKE 'DATA%')
   LOOP
      BEGIN
         EXECUTE IMMEDIATE
               'SELECT * 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.table_name
               );
         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;
/

"I have looked ... but keep getting issues" What issues? We don't know. — Kamil Gosciminski, Jun 18 '18 at 17:07
I don't know how to search two values with the link posted. It only works for one value... How do you search two values? That is my question... — XantorAmnobius, Jun 18 '18 at 17:09
Well, use AND operator in WHERE clause and adjust the definition of statement to LOOP within for two data types. Show us what have you tried. — Kamil Gosciminski, Jun 18 '18 at 17:14

score 1 · Accepted Answer · answered Jun 19 '18 at 05:38

I did small corrections to your code:

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;
/

This sir, worked perfectly - turns out I missed a little bit here and there. Thank you for the help! — XantorAmnobius, Jun 19 '18 at 13:39

Kaushik Nayak · Answer 2 · 2018-06-18T19:02:31.603

What you could try to do is to construct the where clause using a select from the all_tab_columns and query the corresponding table in a dynamic where clause.

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; 

/

Note that what I have given you is an example that gives you some idea and you may construct it and tweak the query for the implicit cursor loop ( within r in () )to suit your needs using AND/OR/JOIN conditions INTERSECT operator etc however you want.I did it this way in order to test it from my end.

EDIT : Another option is a brute force approach, i.e to simply search for all the tables in the database with a dynamic where clause and ignore the exception if columns in the where clause are not found in a table.

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; 
/

Search 2 values in Oracle DB Dynamically

2 Answers2