Search notes:

Oracle: Semantical parsing of SQL statements with UTL_XML.PARSEQUERY

utl_xml provides the function parseQuery that allows to semantically parse an SQL statement. Unfortunately, this function is protected by an ACCESSIBLE BY (PACKAGE SYS.DBMS_METADATA) clause.
However, Philipp Salvisberg provided a work around so that this function still can be used.

Allow using UTL_XML_LIB

A DBA must grant execute on utl_xml_lib to the user that is going to use the functionality: 
connect / as sysdba
grant execute on utl_xml_lib to rene;
Github repository Oracle-Patterns, path: /Installed/utl/xml/parse/grant-execute.sql

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
Github repository Oracle-Patterns, path: /Installed/utl/xml/parse/utl_xml_wrapper.sql

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;
Github repository Oracle-Patterns, path: /Installed/utl/xml/parse/parse-query.sql

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>

See also

utl_xml

Index