Parse simple insert statement via PL/SQL Analyzer, preserve order of insert and select columns (to answer question via LinkedIn Messaging)

01_insert_sql.sql

INSERT
  INTO tvd_captured_sql_t (
          cap_id,
          cap_source
       )
VALUES (
          tvd_captured_sql_seq.nextval,
          q'[ 
             insert into tab1 (x,y,z) select a,b,c from tab2 where tab2.n = 'N';
          ]'
       );
COMMIT;

02_tvdca_output.txt

$ tvdca.sh user=tvdca password=... host=localhost service=... schema=TVDCA captured=true objects=false

Trivadis PL/SQL Analyzer Version 1.0.9 (2016-05-05 09:31:24 +0200 [1865])
Copyright 2010-2016 by Philipp Salvisberg (philipp.salvisberg@trivadis.com)
Trivadis AG (www.trivadis.com)

Trial/Preview version
- valid thru: 2016-12-04
- valid for versions less than: 2
- max. parsed objects: 50
- max. captured SQLs: 20

Connected as tvdca via jdbc:oracle:thin:@localhost:1521/xe.oracle.docker to
Oracle Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production.

Deleting extinct objects from tvd_parsed_objects_t (cleanup)... 0 rows deleted.

Updating table tvd_captured_sql_t:
- Reading tvd_captured_sql_t... 1 unprocessed rows found.
- Parsing cap_id 1... update... done.

Refresh completed within 4.377 seconds.

03_query_parse_result.sql

SELECT cap_id, cap_source, parse_tree 
 FROM tvd_captured_sql_v;

04_parse_tree.xml

<?xml version="1.0" encoding="UTF-8" standalone='no'?>
<plsql:PLSQLFile xmlns:plsql="http://www.trivadis.com/oracle/plsql/PLSQL" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <command xsi:type="plsql:Insert">
    <insert>
      <singleTableInsert>
        <intoClause>
          <dmlExpressionClause>
            <dmlName value="tab1"/>
          </dmlExpressionClause>
          <columns>
            <names value="x"/>
          </columns>
          <columns>
            <names value="y"/>
          </columns>
          <columns>
            <names value="z"/>
          </columns>
        </intoClause>
        <subquery>
          <query>
            <query xsi:type="plsql:QueryBlock">
              <selectList>
                <selected xsi:type="plsql:SingleColumnExpression">
                  <expression value="a" xsi:type="plsql:SimpleExpressionNameValue"/>
                </selected>
                <selected xsi:type="plsql:SingleColumnExpression">
                  <expression value="b" xsi:type="plsql:SimpleExpressionNameValue"/>
                </selected>
                <selected xsi:type="plsql:SingleColumnExpression">
                  <expression value="c" xsi:type="plsql:SimpleExpressionNameValue"/>
                </selected>
              </selectList>
              <fromList>
                <elements>
                  <tableReference>
                    <queryTableExpression>
                      <qteName value="tab2"/>
                    </queryTableExpression>
                    <tableAlias/>
                  </tableReference>
                </elements>
              </fromList>
              <whereClause>
                <condition xsi:type="plsql:BinaryComparisonConditionLevel4">
                  <left binaryOperator="." xsi:type="plsql:BinaryCompoundExpressionLevel6">
                    <left value="tab2" xsi:type="plsql:SimpleExpressionNameValue"/>
                    <right value="n" xsi:type="plsql:SimpleExpressionNameValue"/>
                  </left>
                  <operator xsi:type="plsql:EQOperator"/>
                  <right value="N" xsi:type="plsql:SimpleExpressionStringValue"/>
                </condition>
              </whereClause>
              <groupByClause/>
            </query>
            <rowLimitingClause/>
          </query>
        </subquery>
      </singleTableInsert>
    </insert>
  </command>
</plsql:PLSQLFile>

05_xquery_example.sql

-- Simplified analysis of previously created XML
SELECT tcap.cap_id,
       'SELECT' AS operation,
       tab.table_owner, 
       tab.table_name, 
       tab.column_name,
       tab.pos
  FROM tvd_captured_sql_v tcap,
       XMLTABLE('
          let $set := for $i in //queryTableExpression[qteName/@value]
                      let $alias := if($i/../tableAlias/alias[@value]) then upper-case($i/../tableAlias/alias/@value)
                                    else upper-case($i/qteName/@value)
                      let $qb := $i/../../../..
                      let $singleColumnExpressions := for $col in $qb//selected//expression[@xsi:type = "plsql:SimpleExpressionNameValue"]
                                                      return <column tableName="{ $i/qteName/@value }" tableSchema="{ $i/schema/@value }" columnName="{ $col/@value }"/>
                      return <set>{ $singleColumnExpressions }</set>
          for $j in $set//column
          return <column tableName="{ $j/@tableName }" tableSchema="{ $j/@tableSchema }" columnName="{ $j/@columnName }"/>
      '
      PASSING tcap.parse_tree
      COLUMNS table_name     VARCHAR2(32) PATH '@tableName',
              table_owner    VARCHAR2(32) PATH '@tableSchema',
              column_name    VARCHAR2(32) PATH '@columnName',
              pos FOR ORDINALITY) tab
UNION ALL
SELECT tcap.cap_id,
       'INSERT' AS operation,
       tab.table_owner, 
       tab.table_name, 
       tab.column_name,
       pos
  FROM tvd_captured_sql_v tcap,
       XMLTABLE('
          let $set := for $i in //dmlExpressionClause[dmlName/@value and ancestor::command/@xsi:type = "plsql:Insert"]
                      let $insCol := for $col in $i/..//columns/names[position() = last()]
                                     return <column tableName="{ $i/dmlName/@value }" tableSchema="{ $i/schema/@value }" columnName="{ $col/@value }"/>
                      return <set>{ $insCol }</set>
          for $j in $set//column
          return <column tableName="{ $j/@tableName }" tableSchema="{ $j/@tableSchema }" columnName="{ $j/@columnName }"/>
      '
      PASSING tcap.parse_tree
      COLUMNS table_name     VARCHAR2(32) PATH '@tableName',
              table_owner    VARCHAR2(32) PATH '@tableSchema',
              column_name    VARCHAR2(32) PATH '@columnName',
              pos FOR ORDINALITY) tab;

06_xquery_example_result.txt

    CAP_ID OPERAT TABLE_OWNER                      TABLE_NAME                       COLUMN_NAME                             POS
---------- ------ -------------------------------- -------------------------------- -------------------------------- ----------
         1 SELECT                                  tab2                             a                                         1
         1 SELECT                                  tab2                             b                                         2
         1 SELECT                                  tab2                             c                                         3
         1 INSERT                                  tab1                             x                                         1
         1 INSERT                                  tab1                             y                                         2
         1 INSERT                                  tab1                             z                                         3

 6 rows selected