title: Search 2 values in Oracle DB Dynamically
body: <p>Good day folks,</p>
  
  <p>Trying to wrap my head around this and manage to get it working to some extent - but still struggling a bit.</p>
  
  <p>I want to find two values in two columns for this example:
  VARCHAR and DATE
  JOHN 1984-01-01</p>
  
  <p>Let's say this is John's birth date.</p>
  
  <p>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.</p>
  
  <p>The following selects all the tables with the two column names which I require.</p>
  
  <pre><code>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')
  </code></pre>
  
  <p>So, now I get the tables I need which has the data type DATE where the column names NAME and FULLNAME exists.</p>
  
  <p>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.</p>
  
  <p>I want the TABLE_NAME where these two values exists in the same row, nothing else.</p>
  
  <p>I have looked at these type of examples:</p>
  
  <p><a href="https://stackoverflow.com/questions/208493/search-all-fields-in-all-tables-for-a-specific-value-oracle">Search All Fields In All Tables For A Specific Value (Oracle)</a></p>
  
  <p>But keep getting issues. What am I missing?</p>
  
  <pre><code>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 &gt; 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;
  /
  </code></pre>