Last active
June 30, 2016 10:14
-
-
Save PhilippSalvisberg/3665abdd03adc555a5b6ae3e64c9234b to your computer and use it in GitHub Desktop.
Parse simple insert statement via PL/SQL Analyzer, preserve order of insert and select columns (to answer question via LinkedIn Messaging)
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
$ 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. |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT cap_id, cap_source, parse_tree | |
FROM tvd_captured_sql_v; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?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> |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment