title: Dynamic column query for test script
body: <p>I am testing tables (and data therein) acquired into our data lake against the source application tables.  We do not transform any of the data on acquisition but we do not always acquire all columns of a table and the acquisition process adds several data lake columns to the table (date acquired etc.)</p>
  <p>So I have to compare two tables where most of the columns are the same but some aren't.  Obviously I can deal with this by manually specifying the columns for each <code>SELECT</code> statement.  I want to make a testing script that will do this automatically, comparing the common columns and then allowing me to do further queries using that list of columns.</p>
  <p>I already test common columns to ensure data type integrity between columns:</p>
  <pre><code>SELECT /*fixed*/
      b.column_name,
      a.data_type   AS source_data_type,
      b.data_type   AS acquired_data_type,
      CASE
          WHEN a.data_type = b.data_type   THEN 'Pass'
          ELSE 'Fail'
      END AS DATA_TYPE_TEST
  FROM
      all_tab_cols@&amp;sourcelink a
      INNER JOIN all_tab_cols b ON a.column_name = b.column_name
  WHERE
      a.owner = '&amp;sourceschema'
      AND b.owner = 'DATALAKE'
      AND a.table_name = '&amp;tableName'
      AND b.table_name = '&amp;tableName'; 
  </code></pre>
  <p>The above works as intended and gets only common columns.  How can I save this list of common columns so that when I'm querying the tables directly I can use them in a further query, such as:</p>
  <pre><code>SELECT
       &lt;my dynamic list of columns here&gt;
   FROM
       &amp;sourceschema..&amp;tablename@&amp;sourcelink a
  INNER JOIN datalake.&amp;tablename b ON a.id = b.id;
  </code></pre>
  <p>Is this possible with Oracle PL/SQL or should I use python instead?</p>