Create a wrapper package
The user who wants to parse SQL statements then needs to create a «wrapper» PL/SQL package:
connect rene/rene
create or replace package utl_xml_wrapper as
--
-- Philipp Salvisberg
--
-- https://github.com/PhilippSalvisberg/plscope-utils/blob/main/database/utils/package/parse_util.pkb
--
procedure parseQuery (
currentUserid in number,
schema in varchar2,
query in clob,
result in out nocopy clob
);
--
-- No idea how this function is supposed to be used:
--
procedure parseExpr (
currentUserid in number,
schema in varchar2,
tab in varchar2,
sqltext in clob,
arith in binary_integer, -- non-0 = sqltext is an arithmetic expression
lobloc in out nocopy clob
);
end utl_xml_wrapper;
/
create or replace package body utl_xml_wrapper as
procedure parseQuery (
currentUserid in number,
schema in varchar2,
query in clob,
result in out nocopy clob
) is
language c
library sys.utl_xml_lib
name "kuxParseQuery"
with context parameters (
context,
currentUserid OCINumber , currentUserid indicator,
schema OCIString , schema indicator,
query OCILobLocator, query indicator,
result OCILobLocator, result indicator
);
procedure parseExpr (
currentUserid in number,
schema in varchar2,
tab in varchar2,
sqltext in clob,
arith in binary_integer, -- non-0 = sqltext is an arithmetic expression
lobloc in out nocopy clob -- otherwise it's a boolean expression
) is
language c
library sys.utl_xml_lib
name "kuxParseExpr"
with context parameters (
context,
currentUserid OCINumber, currentUserid indicator sb4,
schema OCIString, schema indicator sb4,
tab OCIString, tab indicator sb4,
sqltext OCILoblocator, sqltext indicator sb4,
arith UB4, arith indicator sb4,
lobloc OCILoblocator, lobloc indicator sb4
);
end utl_xml_wrapper;
/
show errors
Using the function
Finally, this package can be used to parse a given SQL statement:
create table parse_test_one (
id integer primary key,
num_1 number,
txt_1 varchar2(10)
);
create table parse_test_two (
id_one integer references parse_test_one,
num_2 number,
txt_2 varchar2(10)
);
set lines 9999
declare
parsed clob;
begin
sys.dbms_lob.createTemporary(parsed, true);
utl_xml_wrapper.parseQuery(
sys_context('userenv', 'current_userid'),
user,
'select
a.*,
a.num_1 + b.num_2 as total,
a.txt_1 || b.txt_2 as text
from
parse_test_one a left join
parse_test_two b on a.id = b.id_one
',
parsed);
dbms_output.put_line(parsed);
sys.dbms_lob.freetemporary(parsed);
end;
/
drop table parse_test_two purge;
drop table parse_test_one purge;
Output
Calling
parseQuery
returns an
XML document that (for the example above) looks like so:
<QUERY>
<SELECT>
<SELECT_LIST>
<SELECT_LIST_ITEM>
<COLUMN_REF>
<TABLE_ALIAS>A</TABLE_ALIAS>
<COLUMN>ID</COLUMN>
</COLUMN_REF>
</SELECT_LIST_ITEM>
<SELECT_LIST_ITEM>
<COLUMN_REF>
<TABLE_ALIAS>A</TABLE_ALIAS>
<COLUMN>NUM_1</COLUMN>
</COLUMN_REF>
</SELECT_LIST_ITEM>
<SELECT_LIST_ITEM>
<COLUMN_REF>
<TABLE_ALIAS>A</TABLE_ALIAS>
<COLUMN>TXT_1</COLUMN>
</COLUMN_REF>
</SELECT_LIST_ITEM>
<SELECT_LIST_ITEM>
<ADD>
<COLUMN_REF>
<TABLE_ALIAS>A</TABLE_ALIAS>
<COLUMN>NUM_1</COLUMN>
</COLUMN_REF>
<COLUMN_REF>
<TABLE_ALIAS>B</TABLE_ALIAS>
<COLUMN>NUM_2</COLUMN>
</COLUMN_REF>
</ADD>
<COLUMN_ALIAS>TOTAL</COLUMN_ALIAS>
</SELECT_LIST_ITEM>
<SELECT_LIST_ITEM>
<CAT>
<COLUMN_REF>
<TABLE_ALIAS>A</TABLE_ALIAS>
<COLUMN>TXT_1</COLUMN>
</COLUMN_REF>
<COLUMN_REF>
<TABLE_ALIAS>B</TABLE_ALIAS>
<COLUMN>TXT_2</COLUMN>
</COLUMN_REF>
</CAT>
<COLUMN_ALIAS>TEXT</COLUMN_ALIAS>
</SELECT_LIST_ITEM>
</SELECT_LIST>
</SELECT>
<FROM>
<FROM_ITEM>
<JOIN>
<INNER/>
<JOIN_TABLE_1>
<TABLE>PARSE_TEST_ONE</TABLE>
<TABLE_ALIAS>A</TABLE_ALIAS>
</JOIN_TABLE_1>
<JOIN_TABLE_2>
<TABLE>PARSE_TEST_TWO</TABLE>
<TABLE_ALIAS>B</TABLE_ALIAS>
</JOIN_TABLE_2>
<ON>
<EQ>
<COLUMN_REF>
<TABLE>PARSE_TEST_ONE</TABLE>
<TABLE_ALIAS>A</TABLE_ALIAS>
<COLUMN>ID</COLUMN>
</COLUMN_REF>
<COLUMN_REF>
<TABLE>PARSE_TEST_TWO</TABLE>
<TABLE_ALIAS>B</TABLE_ALIAS>
<COLUMN>ID_ONE</COLUMN>
</COLUMN_REF>
</EQ>
</ON>
</JOIN>
</FROM_ITEM>
</FROM>
</QUERY>