{"id":117,"date":"2011-10-12T01:51:00","date_gmt":"2011-10-11T23:51:00","guid":{"rendered":"http:\/\/www.salvis.com\/blog\/?p=117"},"modified":"2023-11-08T19:49:38","modified_gmt":"2023-11-08T18:49:38","slug":"using-utl_xml-parsequery-for-sql-dependency-analysis","status":"publish","type":"post","link":"https:\/\/www.salvis.com\/blog\/2011\/10\/12\/using-utl_xml-parsequery-for-sql-dependency-analysis\/","title":{"rendered":"Using UTL_XML.PARSEQUERY for SQL Dependency Analysis"},"content":{"rendered":"\n

Last week I had a talk at Oracle’s OpenWorld 2011 titled Modern PL\/SQL Code Checking and Dependency Analysis<\/a>.<\/p>\n\n\n\n

The problem I described in chapter 4 was to find all view columns using the column UNIT_COST of the table COSTS in the SH schema. Other usages of this column (e.g. in where or order by clauses) have to be ignored. To solve this problem within the Oracle Database Server 11.2 a parser is necessary (at least I’m not aware of another solution). Even a DBA_DEPENDENCY_COLUMNS view as described in Rob van Wijk’s post<\/a> is not enough to solve this problem.<\/p>\n\n\n\n

However, in this particular case, no custom or 3rd party parser is necessary. Oracle provides a procedure named PARSEQUERY in the PL\/SQL package UTL_XML which is in fact well-suited to solve this problem as I will show later. First, I’d like explain which columns should be found by a dependency analysis procedure based on some sample views.<\/p>\n\n\n\n

Oracle’s sales history demo schema SH provides a view named PROFITS, which is defined as follows:<\/p>\n\n\n\n

1) View PROFITS<\/span><\/path><\/path><\/svg><\/span>
CREATE OR REPLACE<\/span> <\/span>VIEW<\/span> <\/span>PROFITS<\/span> <\/span>AS<\/span><\/span>\nSELECT<\/span> s.channel_id,<\/span><\/span>\n       s.cust_id,<\/span><\/span>\n       s.prod_id,<\/span><\/span>\n       s.promo_id,<\/span><\/span>\n       s.time_id,<\/span><\/span>\n       c.unit_cost,<\/span><\/span>\n       c.unit_price,<\/span><\/span>\n       s.amount_sold,<\/span><\/span>\n       s.quantity_sold,<\/span><\/span>\n       c.unit_cost * s.quantity_sold TOTAL_COST<\/span><\/span>\n  <\/span>FROM<\/span> costs c, sales s<\/span><\/span>\n <\/span>WHERE<\/span> c.prod_id = s.prod_id<\/span><\/span>\n   <\/span>AND<\/span> c.time_id = s.time_id<\/span><\/span>\n   <\/span>AND<\/span> c.channel_id = s.channel_id<\/span><\/span>\n   <\/span>AND<\/span> c.promo_id = s.promo_id;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
The columns using COSTS.UNIT_COSTS are highlighted.<\/p>\n\n\n\n
The following view uses the column TOTAL_COSTS in GROSS_MARGIN (line 14) and GROSS_MARGIN_PERCENT (lines 14 and 15). The usage is not evident at the first glance since it is based on the column GROSS_MARGIN (line 4) of the named query GM and the column COST (line 8) in GM’s subquery. This kind of dependencies need to be identified.<\/p>\n\n\n\n
2) View GROSS_MARGIN<\/span><\/path><\/path><\/svg><\/span>
CREATE OR REPLACE<\/span> <\/span>VIEW<\/span> <\/span>GROSS_MARGINS<\/span> <\/span>AS<\/span><\/span>\nWITH<\/span> <\/span><\/span>\n   gm <\/span>AS<\/span> (<\/span><\/span>\n      <\/span>SELECT<\/span> time_id, revenue, revenue - cost <\/span>AS<\/span> gross_margin<\/span><\/span>\n        <\/span>FROM<\/span> (<\/span><\/span>\n           <\/span>SELECT<\/span> time_id,<\/span><\/span>\n                  unit_price * quantity_sold <\/span>AS<\/span> revenue,<\/span><\/span>\n                  total_cost <\/span>AS<\/span> cost<\/span><\/span>\n             <\/span>FROM<\/span> profits<\/span><\/span>\n        )<\/span><\/span>\n   )<\/span><\/span>\nSELECT<\/span> t.fiscal_year,<\/span><\/span>\n       <\/span>SUM<\/span>(revenue) <\/span>AS<\/span> revenue,<\/span><\/span>\n       <\/span>SUM<\/span>(gross_margin) <\/span>AS<\/span> gross_margin,<\/span><\/span>\n       <\/span>round<\/span>(<\/span>100<\/span> * <\/span>SUM<\/span>(gross_margin) \/ <\/span>SUM<\/span>(revenue), <\/span>2<\/span>) <\/span><\/span>\n          <\/span>AS<\/span> gross_margin_percent<\/span><\/span>\n  <\/span>FROM<\/span> gm<\/span><\/span>\n <\/span>INNER JOIN<\/span> <\/span>times<\/span> t <\/span>ON<\/span> t.time_id = gm.time_id<\/span><\/span>\n <\/span>GROUP BY<\/span> t.fiscal_year<\/span><\/span>\n <\/span>ORDER BY<\/span> t.fiscal_year;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
The next view does not present the data of COSTS.UNIT_COST as a column, even if the view depends on the table COSTS<\/p>\n\n\n\n
3) View REVENUES <\/span><\/path><\/path><\/svg><\/span>
CREATE OR REPLACE<\/span> <\/span>VIEW<\/span> <\/span>REVENUES<\/span> <\/span>AS<\/span><\/span>\nSELECT<\/span> fiscal_year, revenue<\/span><\/span>\n  <\/span>FROM<\/span> gross_margins;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
The last view uses COSTS.UNIT_COST but not as part of a column expression and therefore has not to be reported. The usage in the order by clause is considered safe.<\/p>\n\n\n\n
4) View SALES_ORDERED_BY_GM<\/span><\/path><\/path><\/svg><\/span>
CREATE OR REPLACE<\/span> <\/span>VIEW<\/span> <\/span>SALES_ORDERED_BY_GM<\/span> <\/span>AS<\/span><\/span>\nSELECT<\/span> channel_id,<\/span><\/span>\n       cust_id,<\/span><\/span>\n       prod_id,<\/span><\/span>\n       promo_id,<\/span><\/span>\n       time_id,<\/span><\/span>\n       amount_sold,<\/span><\/span>\n       quantity_sold<\/span><\/span>\n  <\/span>FROM<\/span> profits<\/span><\/span>\n <\/span>ORDER BY<\/span> (unit_price - unit_cost) <\/span>DESC<\/span>;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
So, the following result of the dependency analysis is expected:<\/p>\n\n\n\n\n\n\n\t\n\n\t\n\t\n\t\n\t
SCHEMA<\/th>VIEW<\/th>COLUMN<\/th>\n<\/tr>\n<\/thead>\n
SH<\/td>PROFITS<\/td>UNIT_COST<\/td>\n<\/tr>\n
SH<\/td>PROFITS<\/td>TOTAL_COST<\/td>\n<\/tr>\n
SH<\/td>GROSS_MARGINS<\/td>GROSS_MARGIN<\/td>\n<\/tr>\n
SH<\/td>GROSS_MARGINS<\/td>GROSS_MARGIN_PERCENT<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\n\n\n
Exactly this result is created by the following query<\/p>\n\n\n\n
5) Query Column Depenendencies<\/span><\/path><\/path><\/svg><\/span>
SELECT<\/span> *<\/span><\/span>\n  <\/span>FROM<\/span> <\/span>TABLE<\/span>(coldep_pkg.get_dep(<\/span>'sh'<\/span>, <\/span>'costs'<\/span>, <\/span>'unit_cost'<\/span>));<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Now I just list all the code snippets I’ve written to create this result. Please note, that this is considered just a proof-of-concept code to show how UTL_XML.PARSEQUERY could be used for SQL dependency analysis in conjunction with Oracle dictionary views. This means that this is not a complete implementation. For example, wild cards (*) are not handled which may lead to missing dependencies. Additionally, table\/view sources are not checked which may lead to false positives (in case a column is used in multiple view\/table sources). – Please feel free to complete the code. However, an update is highly appreciated ;-)<\/p>\n\n\n\n
6) Grants<\/span><\/path><\/path><\/svg><\/span>
GRANT<\/span> <\/span>EXECUTE<\/span> <\/span>ON<\/span> SYS.UTL_XML <\/span>TO<\/span> SH;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
7) Object Type COLDEP_TYP<\/span><\/path><\/path><\/svg><\/span>
CREATE OR REPLACE<\/span> <\/span>TYPE<\/span> "<\/span>SH<\/span>".<\/span>"COLDEP_TYP"<\/span> <\/span>AS<\/span> <\/span><\/span>\nOBJECT<\/span> (schema_name <\/span>VARCHAR2<\/span>(<\/span>30<\/span>), <\/span><\/span>\n        view_name <\/span>varchar2<\/span>(<\/span>30<\/span>), <\/span><\/span>\n        column_name <\/span>VARCHAR2<\/span>(<\/span>30<\/span>))<\/span><\/span>\n\/<\/span><\/span>\nCREATE OR REPLACE<\/span> <\/span>TYPE<\/span> "<\/span>SH<\/span>".<\/span>"COLDEP_L"<\/span> <\/span>IS<\/span> <\/span>TABLE<\/span> OF coldep_typ<\/span><\/span>\n\/<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
8) PL\/SQL Package COLDEP_PKG<\/span><\/path><\/path><\/svg><\/span>
CREATE OR REPLACE<\/span> <\/span>PACKAGE<\/span> <\/span>"SH"."COLDEP_PKG"<\/span> <\/span>IS<\/span><\/span>\n   <\/span>FUNCTION<\/span> <\/span>parse_query<\/span>(p_query <\/span>IN<\/span> <\/span>VARCHAR2<\/span>) <\/span>RETURN<\/span> <\/span>xmltype<\/span>;<\/span><\/span>\n<\/span>\n   <\/span>FUNCTION<\/span> <\/span>get_dep<\/span>(p_schema_name <\/span>IN<\/span> <\/span>VARCHAR2<\/span>,<\/span><\/span>\n                    p_object_name <\/span>IN<\/span> <\/span>VARCHAR2<\/span>,<\/span><\/span>\n                    p_column_name <\/span>IN<\/span> <\/span>VARCHAR2<\/span>) <\/span>RETURN<\/span> coldep_l<\/span><\/span>\n      PIPELINED;<\/span><\/span>\n<\/span>\n   <\/span>FUNCTION<\/span> <\/span>process_view<\/span>(p_schema_name <\/span>IN<\/span> <\/span>VARCHAR2<\/span>,<\/span><\/span>\n                         p_view_name   <\/span>IN<\/span> <\/span>VARCHAR2<\/span>,<\/span><\/span>\n                         p_column_name <\/span>IN<\/span> <\/span>VARCHAR2<\/span>,<\/span><\/span>\n                         p_query       <\/span>IN<\/span> <\/span>CLOB<\/span>) <\/span>RETURN<\/span> coldep_l;<\/span><\/span>\nEND<\/span> coldep_pkg;<\/span><\/span>\n\/<\/span><\/span>\nCREATE OR REPLACE<\/span> <\/span>PACKAGE BODY<\/span> <\/span>"SH"."COLDEP_PKG"<\/span> <\/span>IS<\/span><\/span>\n   <\/span>FUNCTION<\/span> <\/span>parse_query<\/span>(p_query <\/span>IN<\/span> <\/span>VARCHAR2<\/span>) <\/span>RETURN<\/span> <\/span>xmltype<\/span> <\/span>IS<\/span><\/span>\n      v_clob <\/span>CLOB<\/span>;<\/span><\/span>\n      v_xml  <\/span>xmltype<\/span>;<\/span><\/span>\n   <\/span>BEGIN<\/span><\/span>\n      <\/span>dbms_lob.<\/span>createtemporary<\/span>(v_clob, <\/span>TRUE<\/span>);<\/span><\/span>\n      <\/span>-- parse query and get XML as CLOB<\/span><\/span>\n      sys.<\/span>utl_xml.<\/span>parsequery<\/span>(<\/span>USER<\/span>, p_query, v_clob);<\/span><\/span>\n      <\/span>-- create XMLTYPE from CLOB <\/span><\/span>\n      v_xml := <\/span>xmltype<\/span>.createxml(v_clob);<\/span><\/span>\n      <\/span>dbms_lob.<\/span>freetemporary<\/span>(v_clob);<\/span><\/span>\n      <\/span>RETURN<\/span> v_xml;<\/span><\/span>\n   <\/span>END<\/span> parse_query;<\/span><\/span>\n<\/span>\n   <\/span>FUNCTION<\/span> <\/span>get_dep<\/span>(p_schema_name <\/span>IN<\/span> <\/span>VARCHAR2<\/span>,<\/span><\/span>\n                    p_object_name <\/span>IN<\/span> <\/span>VARCHAR2<\/span>,<\/span><\/span>\n                    p_column_name <\/span>IN<\/span> <\/span>VARCHAR2<\/span>) <\/span>RETURN<\/span> coldep_l<\/span><\/span>\n      PIPELINED <\/span>IS<\/span><\/span>\n   <\/span>BEGIN<\/span><\/span>\n      <\/span>-- query dictionary dependencies<\/span><\/span>\n      <\/span>FOR<\/span> v_dep <\/span>IN<\/span> (<\/span>SELECT<\/span> d.owner <\/span>AS<\/span> schema_name,<\/span><\/span>\n                           d.<\/span>name<\/span>  <\/span>AS<\/span> view_name,<\/span><\/span>\n                           v.text  <\/span>AS<\/span> query_text<\/span><\/span>\n                      <\/span>FROM<\/span> all_dependencies d<\/span><\/span>\n                     INNER <\/span>JOIN<\/span> all_views v<\/span><\/span>\n                        <\/span>ON<\/span> v.owner = d.owner<\/span><\/span>\n                           <\/span>AND<\/span> v.view_name = d.<\/span>name<\/span><\/span>\n                     <\/span>WHERE<\/span> d.referenced_owner = <\/span>upper<\/span>(p_schema_name)<\/span><\/span>\n                           <\/span>AND<\/span> d.referenced_name = <\/span>upper<\/span>(p_object_name)<\/span><\/span>\n                           <\/span>AND<\/span> d.<\/span>type<\/span> = <\/span>'VIEW'<\/span>)<\/span><\/span>\n      <\/span>LOOP<\/span><\/span>\n         <\/span>-- process every fetched view<\/span><\/span>\n         <\/span>FOR<\/span> v_views <\/span>IN<\/span> (<\/span><\/span>\n            <\/span>SELECT<\/span> VALUE(pv) coldep<\/span><\/span>\n              <\/span>FROM<\/span> <\/span>TABLE<\/span>(process_view(v_dep.schema_name,<\/span><\/span>\n                                      v_dep.view_name,<\/span><\/span>\n                                      p_column_name,<\/span><\/span>\n                                      v_dep.query_text)) pv)<\/span><\/span>\n         <\/span>LOOP<\/span><\/span>\n            <\/span>-- return column usages in v_dep.view_name<\/span><\/span>\n            PIPE <\/span>ROW<\/span>(v_views.coldep);<\/span><\/span>\n            <\/span>-- get column usages of views using v_dep.view_name (recursive calls)<\/span><\/span>\n            <\/span>FOR<\/span> v_recursive <\/span>IN<\/span> (<\/span><\/span>\n               <\/span>SELECT<\/span> VALUE(dep) coldep<\/span><\/span>\n                 <\/span>FROM<\/span> <\/span>TABLE<\/span>(get_dep(v_views.coldep.schema_name,<\/span><\/span>\n                                    v_views.coldep.view_name,<\/span><\/span>\n                                    v_views.coldep.column_name)) dep)<\/span><\/span>\n            <\/span>LOOP<\/span><\/span>\n               <\/span>-- return column usages of recursive call<\/span><\/span>\n               PIPE <\/span>ROW<\/span>(v_recursive.coldep);<\/span><\/span>\n            <\/span>END LOOP<\/span>;<\/span><\/span>\n         <\/span>END LOOP<\/span>;<\/span><\/span>\n      <\/span>END LOOP<\/span>;<\/span><\/span>\n   <\/span>END<\/span> get_dep;<\/span><\/span>\n<\/span>\n   <\/span>FUNCTION<\/span> <\/span>process_view<\/span>(p_schema_name <\/span>IN<\/span> <\/span>VARCHAR2<\/span>,<\/span><\/span>\n                         p_view_name   <\/span>IN<\/span> <\/span>VARCHAR2<\/span>,<\/span><\/span>\n                         p_column_name <\/span>IN<\/span> <\/span>VARCHAR2<\/span>,<\/span><\/span>\n                         p_query       <\/span>IN<\/span> <\/span>CLOB<\/span>) <\/span>RETURN<\/span> coldep_l <\/span>IS<\/span><\/span>\n      v_search_l       coldep_l := coldep_l(coldep_typ(<\/span>NULL<\/span>,<\/span><\/span>\n                                                       <\/span>NULL<\/span>,<\/span><\/span>\n                                                       p_column_name));<\/span><\/span>\n      v_xml            <\/span>xmltype<\/span>;<\/span><\/span>\n      v_previous_count <\/span>INTEGER<\/span> := <\/span>0<\/span>;<\/span><\/span>\n      v_coldep_l       coldep_l := coldep_l();<\/span><\/span>\n   <\/span>BEGIN<\/span><\/span>\n      <\/span>-- parse view query<\/span><\/span>\n      v_xml := parse_query(p_query);<\/span><\/span>\n      <\/span>-- get inline dependencies from secondary select lists<\/span><\/span>\n      <\/span>-- TODO: handle table\/view source and wildcard properly <\/span><\/span>\n      <\/span>WHILE<\/span> v_previous_count < v_search_l<\/span>.count<\/span><\/span>\n      <\/span>LOOP<\/span><\/span>\n         v_previous_count := v_search_l<\/span>.count<\/span>;<\/span><\/span>\n         <\/span>FOR<\/span> v_secondary <\/span>IN<\/span> (<\/span><\/span>\n            <\/span>SELECT<\/span> <\/span>nvl<\/span>(x.alias_name, x.column_reference) <\/span>AS<\/span> alias_name<\/span><\/span>\n              <\/span>FROM<\/span> (<\/span>SELECT<\/span> t.select_list_item,<\/span><\/span>\n                           t.alias_name,<\/span><\/span>\n                           <\/span>extractvalue<\/span>(VALUE(<\/span>c<\/span>), <\/span>'COLUMN'<\/span>) <\/span>AS<\/span> column_reference<\/span><\/span>\n                      <\/span>FROM<\/span> <\/span>xmltable<\/span>(<\/span>'\/\/SELECT_LIST_ITEM[ancestor::FROM or ancestor::WITH]'<\/span><\/span>\n                              passing v_xml <\/span><\/span>\n                              columns select_list_item <\/span>xmltype<\/span> <\/span>path<\/span> <\/span>'\/\/SELECT_LIST_ITEM'<\/span>,<\/span><\/span>\n                                      alias_name <\/span>VARCHAR2<\/span>(<\/span>30<\/span>) <\/span>path<\/span> <\/span>'\/\/COLUMN_ALIAS'<\/span>) t,<\/span><\/span>\n                           <\/span>TABLE<\/span>(<\/span>xmlsequence<\/span>(<\/span>extract<\/span>(select_list_item, <\/span>'\/\/COLUMN'<\/span>))) <\/span>c<\/span>) x<\/span><\/span>\n             <\/span>WHERE<\/span> <\/span>upper<\/span>(x.column_reference) <\/span>IN<\/span> (<\/span>SELECT<\/span> <\/span>upper<\/span>(column_name) <\/span><\/span>\n                                                   <\/span>FROM<\/span> <\/span>TABLE<\/span>(v_search_l))<\/span><\/span>\n               <\/span>AND<\/span> <\/span>upper<\/span>(alias_name) <\/span>NOT<\/span> <\/span>IN<\/span> (<\/span>SELECT<\/span> <\/span>upper<\/span>(column_name)<\/span><\/span>\n                                              <\/span>FROM<\/span> <\/span>TABLE<\/span>(v_search_l)))<\/span><\/span>\n         <\/span>LOOP<\/span><\/span>\n            <\/span>-- add internal column usage<\/span><\/span>\n            v_search_l<\/span>.extend<\/span>;<\/span><\/span>\n            v_search_l(v_search_l<\/span>.count<\/span>) := coldep_typ(<\/span>NULL<\/span>,<\/span><\/span>\n                                                       <\/span>NULL<\/span>,<\/span><\/span>\n                                                       v_secondary.alias_name);<\/span><\/span>\n         <\/span>END LOOP<\/span>;<\/span><\/span>\n      <\/span>END LOOP<\/span>;<\/span><\/span>\n      <\/span>-- analyze primary select list<\/span><\/span>\n      <\/span>-- TODO: handle table\/view source and wildcard properly <\/span><\/span>\n      <\/span>FOR<\/span> v_primary <\/span>IN<\/span> (<\/span><\/span>\n         <\/span>SELECT<\/span> x.column_id, atc.column_name<\/span><\/span>\n           <\/span>FROM<\/span> (<\/span>SELECT<\/span> t.select_list_item,<\/span><\/span>\n                        t.column_id,<\/span><\/span>\n                        <\/span>extractvalue<\/span>(VALUE(<\/span>c<\/span>), <\/span>'COLUMN'<\/span>) <\/span>AS<\/span> column_reference<\/span><\/span>\n                   <\/span>FROM<\/span> <\/span>xmltable<\/span>(<\/span>'\/\/SELECT_LIST_ITEM[not (ancestor::FROM) and not (ancestor::WITH)]'<\/span><\/span>\n                           passing v_xml <\/span><\/span>\n                           columns column_id <\/span>FOR<\/span> ordinality,<\/span><\/span>\n                                   select_list_item <\/span>xmltype<\/span> <\/span>path<\/span> <\/span>'\/\/SELECT_LIST_ITEM'<\/span>) t,<\/span><\/span>\n                        <\/span>TABLE<\/span>(<\/span>xmlsequence<\/span>(<\/span>extract<\/span>(select_list_item, <\/span>'\/\/COLUMN'<\/span>))) <\/span>c<\/span>) x<\/span><\/span>\n                  INNER <\/span>JOIN<\/span> all_tab_columns atc<\/span><\/span>\n                     <\/span>ON<\/span> atc.owner = p_schema_name<\/span><\/span>\n                    <\/span>AND<\/span> atc.table_name = p_view_name<\/span><\/span>\n                    <\/span>AND<\/span> atc.column_id = x.column_id<\/span><\/span>\n                  <\/span>WHERE<\/span> <\/span>upper<\/span>(x.column_reference) <\/span>IN<\/span> (<\/span>SELECT<\/span> <\/span>upper<\/span>(column_name)<\/span><\/span>\n                                                        <\/span>FROM<\/span> <\/span>TABLE<\/span>(v_search_l))<\/span><\/span>\n                  <\/span>ORDER BY<\/span> x.column_id)<\/span><\/span>\n      <\/span>LOOP<\/span><\/span>\n         <\/span>-- add external column usage<\/span><\/span>\n         v_coldep_l<\/span>.extend<\/span>;<\/span><\/span>\n         v_coldep_l(v_coldep_l<\/span>.count<\/span>) := coldep_typ(p_schema_name,<\/span><\/span>\n                                                    p_view_name,<\/span><\/span>\n                                                    v_primary.column_name);<\/span><\/span>\n      <\/span>END LOOP<\/span>;<\/span><\/span>\n      <\/span>-- return column dependencies   <\/span><\/span>\n      <\/span>RETURN<\/span> v_coldep_l;<\/span><\/span>\n   <\/span>END<\/span> process_view;<\/span><\/span>\nEND<\/span> coldep_pkg;<\/span><\/span>\n\/<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Below you find the XML parser output of the query defined in the view GROSS_MARGINS. The model becomes quite clear, even if I could not find a schema description.<\/p>\n\n\n\n
9) XML Result<\/span><\/path><\/path><\/svg><\/span>
<<\/span>QUERY<\/span>><\/span><\/span>\n  <\/span><<\/span>WITH<\/span>><\/span><\/span>\n    <\/span><<\/span>WITH_ITEM<\/span>><\/span><\/span>\n      <\/span><<\/span>QUERY_ALIAS<\/span>><\/span>GM<\/span><\/<\/span>QUERY_ALIAS<\/span>><\/span><\/span>\n      <\/span><<\/span>QUERY<\/span>><\/span><\/span>\n        <\/span><<\/span>SELECT<\/span>><\/span><\/span>\n          <\/span><<\/span>SELECT_LIST<\/span>><\/span><\/span>\n            <\/span><<\/span>SELECT_LIST_ITEM<\/span>><\/span><\/span>\n              <\/span><<\/span>COLUMN_REF<\/span>><\/span><\/span>\n                <\/span><<\/span>COLUMN<\/span>><\/span>TIME_ID<\/span><\/<\/span>COLUMN<\/span>><\/span><\/span>\n              <\/span><\/<\/span>COLUMN_REF<\/span>><\/span><\/span>\n            <\/span><\/<\/span>SELECT_LIST_ITEM<\/span>><\/span><\/span>\n            <\/span><<\/span>SELECT_LIST_ITEM<\/span>><\/span><\/span>\n              <\/span><<\/span>COLUMN_REF<\/span>><\/span><\/span>\n                <\/span><<\/span>COLUMN<\/span>><\/span>REVENUE<\/span><\/<\/span>COLUMN<\/span>><\/span><\/span>\n              <\/span><\/<\/span>COLUMN_REF<\/span>><\/span><\/span>\n            <\/span><\/<\/span>SELECT_LIST_ITEM<\/span>><\/span><\/span>\n            <\/span><<\/span>SELECT_LIST_ITEM<\/span>><\/span><\/span>\n              <\/span><<\/span>SUB<\/span>><\/span><\/span>\n                <\/span><<\/span>COLUMN_REF<\/span>><\/span><\/span>\n                  <\/span><<\/span>COLUMN<\/span>><\/span>REVENUE<\/span><\/<\/span>COLUMN<\/span>><\/span><\/span>\n                <\/span><\/<\/span>COLUMN_REF<\/span>><\/span><\/span>\n                <\/span><<\/span>COLUMN_REF<\/span>><\/span><\/span>\n                  <\/span><<\/span>COLUMN<\/span>><\/span>COST<\/span><\/<\/span>COLUMN<\/span>><\/span><\/span>\n                <\/span><\/<\/span>COLUMN_REF<\/span>><\/span><\/span>\n              <\/span><\/<\/span>SUB<\/span>><\/span><\/span>\n              <\/span><<\/span>COLUMN_ALIAS<\/span>><\/span>GROSS_MARGIN<\/span><\/<\/span>COLUMN_ALIAS<\/span>><\/span><\/span>\n            <\/span><\/<\/span>SELECT_LIST_ITEM<\/span>><\/span><\/span>\n          <\/span><\/<\/span>SELECT_LIST<\/span>><\/span><\/span>\n        <\/span><\/<\/span>SELECT<\/span>><\/span><\/span>\n        <\/span><<\/span>FROM<\/span>><\/span><\/span>\n          <\/span><<\/span>FROM_ITEM<\/span>><\/span><\/span>\n            <\/span><<\/span>QUERY<\/span>><\/span><\/span>\n              <\/span><<\/span>SELECT<\/span>><\/span><\/span>\n                <\/span><<\/span>SELECT_LIST<\/span>><\/span><\/span>\n                  <\/span><<\/span>SELECT_LIST_ITEM<\/span>><\/span><\/span>\n                    <\/span><<\/span>COLUMN_REF<\/span>><\/span><\/span>\n                      <\/span><<\/span>TABLE<\/span>><\/span>PROFITS<\/span><\/<\/span>TABLE<\/span>><\/span><\/span>\n                      <\/span><<\/span>COLUMN<\/span>><\/span>TIME_ID<\/span><\/<\/span>COLUMN<\/span>><\/span><\/span>\n                    <\/span><\/<\/span>COLUMN_REF<\/span>><\/span><\/span>\n                  <\/span><\/<\/span>SELECT_LIST_ITEM<\/span>><\/span><\/span>\n                  <\/span><<\/span>SELECT_LIST_ITEM<\/span>><\/span><\/span>\n                    <\/span><<\/span>MUL<\/span>><\/span><\/span>\n                      <\/span><<\/span>COLUMN_REF<\/span>><\/span><\/span>\n                        <\/span><<\/span>TABLE<\/span>><\/span>PROFITS<\/span><\/<\/span>TABLE<\/span>><\/span><\/span>\n                        <\/span><<\/span>COLUMN<\/span>><\/span>UNIT_PRICE<\/span><\/<\/span>COLUMN<\/span>><\/span><\/span>\n                      <\/span><\/<\/span>COLUMN_REF<\/span>><\/span><\/span>\n                      <\/span><<\/span>COLUMN_REF<\/span>><\/span><\/span>\n                        <\/span><<\/span>TABLE<\/span>><\/span>PROFITS<\/span><\/<\/span>TABLE<\/span>><\/span><\/span>\n                        <\/span><<\/span>COLUMN<\/span>><\/span>QUANTITY_SOLD<\/span><\/<\/span>COLUMN<\/span>><\/span><\/span>\n                      <\/span><\/<\/span>COLUMN_REF<\/span>><\/span><\/span>\n                    <\/span><\/<\/span>MUL<\/span>><\/span><\/span>\n                    <\/span><<\/span>COLUMN_ALIAS<\/span>><\/span>REVENUE<\/span><\/<\/span>COLUMN_ALIAS<\/span>><\/span><\/span>\n                  <\/span><\/<\/span>SELECT_LIST_ITEM<\/span>><\/span><\/span>\n                  <\/span><<\/span>SELECT_LIST_ITEM<\/span>><\/span><\/span>\n                    <\/span><<\/span>COLUMN_REF<\/span>><\/span><\/span>\n                      <\/span><<\/span>TABLE<\/span>><\/span>PROFITS<\/span><\/<\/span>TABLE<\/span>><\/span><\/span>\n                      <\/span><<\/span>COLUMN<\/span>><\/span>TOTAL_COST<\/span><\/<\/span>COLUMN<\/span>><\/span><\/span>\n                    <\/span><\/<\/span>COLUMN_REF<\/span>><\/span><\/span>\n                    <\/span><<\/span>COLUMN_ALIAS<\/span>><\/span>COST<\/span><\/<\/span>COLUMN_ALIAS<\/span>><\/span><\/span>\n                  <\/span><\/<\/span>SELECT_LIST_ITEM<\/span>><\/span><\/span>\n                <\/span><\/<\/span>SELECT_LIST<\/span>><\/span><\/span>\n              <\/span><\/<\/span>SELECT<\/span>><\/span><\/span>\n              <\/span><<\/span>FROM<\/span>><\/span><\/span>\n                <\/span><<\/span>FROM_ITEM<\/span>><\/span><\/span>\n                  <\/span><<\/span>TABLE<\/span>><\/span>PROFITS<\/span><\/<\/span>TABLE<\/span>><\/span><\/span>\n                <\/span><\/<\/span>FROM_ITEM<\/span>><\/span><\/span>\n              <\/span><\/<\/span>FROM<\/span>><\/span><\/span>\n            <\/span><\/<\/span>QUERY<\/span>><\/span><\/span>\n          <\/span><\/<\/span>FROM_ITEM<\/span>><\/span><\/span>\n        <\/span><\/<\/span>FROM<\/span>><\/span><\/span>\n      <\/span><\/<\/span>QUERY<\/span>><\/span><\/span>\n    <\/span><\/<\/span>WITH_ITEM<\/span>><\/span><\/span>\n  <\/span><\/<\/span>WITH<\/span>><\/span><\/span>\n  <\/span><<\/span>SELECT<\/span>><\/span><\/span>\n    <\/span><<\/span>SELECT_LIST<\/span>><\/span><\/span>\n      <\/span><<\/span>SELECT_LIST_ITEM<\/span>><\/span><\/span>\n        <\/span><<\/span>COLUMN_REF<\/span>><\/span><\/span>\n          <\/span><<\/span>TABLE_ALIAS<\/span>><\/span>T<\/span><\/<\/span>TABLE_ALIAS<\/span>><\/span><\/span>\n          <\/span><<\/span>COLUMN<\/span>><\/span>FISCAL_YEAR<\/span><\/<\/span>COLUMN<\/span>><\/span><\/span>\n        <\/span><\/<\/span>COLUMN_REF<\/span>><\/span><\/span>\n      <\/span><\/<\/span>SELECT_LIST_ITEM<\/span>><\/span><\/span>\n      <\/span><<\/span>SELECT_LIST_ITEM<\/span>><\/span><\/span>\n        <\/span><<\/span>SUM<\/span>><\/span><\/span>\n          <\/span><<\/span>COLUMN_REF<\/span>><\/span><\/span>\n            <\/span><<\/span>COLUMN<\/span>><\/span>REVENUE<\/span><\/<\/span>COLUMN<\/span>><\/span><\/span>\n          <\/span><\/<\/span>COLUMN_REF<\/span>><\/span><\/span>\n        <\/span><\/<\/span>SUM<\/span>><\/span><\/span>\n        <\/span><<\/span>COLUMN_ALIAS<\/span>><\/span>REVENUE<\/span><\/<\/span>COLUMN_ALIAS<\/span>><\/span><\/span>\n      <\/span><\/<\/span>SELECT_LIST_ITEM<\/span>><\/span><\/span>\n      <\/span><<\/span>SELECT_LIST_ITEM<\/span>><\/span><\/span>\n        <\/span><<\/span>SUM<\/span>><\/span><\/span>\n          <\/span><<\/span>COLUMN_REF<\/span>><\/span><\/span>\n            <\/span><<\/span>COLUMN<\/span>><\/span>GROSS_MARGIN<\/span><\/<\/span>COLUMN<\/span>><\/span><\/span>\n          <\/span><\/<\/span>COLUMN_REF<\/span>><\/span><\/span>\n        <\/span><\/<\/span>SUM<\/span>><\/span><\/span>\n        <\/span><<\/span>COLUMN_ALIAS<\/span>><\/span>GROSS_MARGIN<\/span><\/<\/span>COLUMN_ALIAS<\/span>><\/span><\/span>\n      <\/span><\/<\/span>SELECT_LIST_ITEM<\/span>><\/span><\/span>\n      <\/span><<\/span>SELECT_LIST_ITEM<\/span>><\/span><\/span>\n        <\/span><<\/span>ROUND<\/span>><\/span><\/span>\n          <\/span><<\/span>DIV<\/span>><\/span><\/span>\n            <\/span><<\/span>MUL<\/span>><\/span><\/span>\n              <\/span><<\/span>LITERAL<\/span>><\/span>100<\/span><\/<\/span>LITERAL<\/span>><\/span><\/span>\n              <\/span><<\/span>SUM<\/span>><\/span><\/span>\n                <\/span><<\/span>COLUMN_REF<\/span>><\/span><\/span>\n                  <\/span><<\/span>COLUMN<\/span>><\/span>GROSS_MARGIN<\/span><\/<\/span>COLUMN<\/span>><\/span><\/span>\n                <\/span><\/<\/span>COLUMN_REF<\/span>><\/span><\/span>\n              <\/span><\/<\/span>SUM<\/span>><\/span><\/span>\n            <\/span><\/<\/span>MUL<\/span>><\/span><\/span>\n            <\/span><<\/span>SUM<\/span>><\/span><\/span>\n              <\/span><<\/span>COLUMN_REF<\/span>><\/span><\/span>\n                <\/span><<\/span>COLUMN<\/span>><\/span>REVENUE<\/span><\/<\/span>COLUMN<\/span>><\/span><\/span>\n              <\/span><\/<\/span>COLUMN_REF<\/span>><\/span><\/span>\n            <\/span><\/<\/span>SUM<\/span>><\/span><\/span>\n          <\/span><\/<\/span>DIV<\/span>><\/span><\/span>\n          <\/span><<\/span>LITERAL<\/span>><\/span>2<\/span><\/<\/span>LITERAL<\/span>><\/span><\/span>\n        <\/span><\/<\/span>ROUND<\/span>><\/span><\/span>\n        <\/span><<\/span>COLUMN_ALIAS<\/span>><\/span>GROSS_MARGIN_PERCENT<\/span><\/<\/span>COLUMN_ALIAS<\/span>><\/span><\/span>\n      <\/span><\/<\/span>SELECT_LIST_ITEM<\/span>><\/span><\/span>\n    <\/span><\/<\/span>SELECT_LIST<\/span>><\/span><\/span>\n  <\/span><\/<\/span>SELECT<\/span>><\/span><\/span>\n  <\/span><<\/span>FROM<\/span>><\/span><\/span>\n    <\/span><<\/span>FROM_ITEM<\/span>><\/span><\/span>\n      <\/span><<\/span>JOIN<\/span>><\/span><\/span>\n        <\/span><<\/span>INNER<\/span>\/><\/span><\/span>\n        <\/span><<\/span>JOIN_TABLE_1<\/span>><\/span><\/span>\n          <\/span><<\/span>QUERY_ALIAS<\/span>><\/span>GM<\/span><\/<\/span>QUERY_ALIAS<\/span>><\/span><\/span>\n        <\/span><\/<\/span>JOIN_TABLE_1<\/span>><\/span><\/span>\n        <\/span><<\/span>JOIN_TABLE_2<\/span>><\/span><\/span>\n          <\/span><<\/span>TABLE<\/span>><\/span>TIMES<\/span><\/<\/span>TABLE<\/span>><\/span><\/span>\n          <\/span><<\/span>TABLE_ALIAS<\/span>><\/span>T<\/span><\/<\/span>TABLE_ALIAS<\/span>><\/span><\/span>\n        <\/span><\/<\/span>JOIN_TABLE_2<\/span>><\/span><\/span>\n        <\/span><<\/span>ON<\/span>><\/span><\/span>\n          <\/span><<\/span>EQ<\/span>><\/span><\/span>\n            <\/span><<\/span>COLUMN_REF<\/span>><\/span><\/span>\n              <\/span><<\/span>TABLE<\/span>><\/span>TIMES<\/span><\/<\/span>TABLE<\/span>><\/span><\/span>\n              <\/span><<\/span>TABLE_ALIAS<\/span>><\/span>T<\/span><\/<\/span>TABLE_ALIAS<\/span>><\/span><\/span>\n              <\/span><<\/span>COLUMN<\/span>><\/span>TIME_ID<\/span><\/<\/span>COLUMN<\/span>><\/span><\/span>\n            <\/span><\/<\/span>COLUMN_REF<\/span>><\/span><\/span>\n            <\/span><<\/span>COLUMN_REF<\/span>><\/span><\/span>\n              <\/span><<\/span>TABLE_ALIAS<\/span>><\/span>GM<\/span><\/<\/span>TABLE_ALIAS<\/span>><\/span><\/span>\n              <\/span><<\/span>COLUMN<\/span>><\/span>TIME_ID<\/span><\/<\/span>COLUMN<\/span>><\/span><\/span>\n            <\/span><\/<\/span>COLUMN_REF<\/span>><\/span><\/span>\n          <\/span><\/<\/span>EQ<\/span>><\/span><\/span>\n        <\/span><\/<\/span>ON<\/span>><\/span><\/span>\n      <\/span><\/<\/span>JOIN<\/span>><\/span><\/span>\n    <\/span><\/<\/span>FROM_ITEM<\/span>><\/span><\/span>\n  <\/span><\/<\/span>FROM<\/span>><\/span><\/span>\n  <\/span><<\/span>GROUP_BY<\/span>><\/span><\/span>\n    <\/span><<\/span>EXPRESSION_LIST<\/span>><\/span><\/span>\n      <\/span><<\/span>EXPRESSION_LIST_ITEM<\/span>><\/span><\/span>\n        <\/span><<\/span>COLUMN_REF<\/span>><\/span><\/span>\n          <\/span><<\/span>TABLE_ALIAS<\/span>><\/span>T<\/span><\/<\/span>TABLE_ALIAS<\/span>><\/span><\/span>\n          <\/span><<\/span>COLUMN<\/span>><\/span>FISCAL_YEAR<\/span><\/<\/span>COLUMN<\/span>><\/span><\/span>\n        <\/span><\/<\/span>COLUMN_REF<\/span>><\/span><\/span>\n      <\/span><\/<\/span>EXPRESSION_LIST_ITEM<\/span>><\/span><\/span>\n    <\/span><\/<\/span>EXPRESSION_LIST<\/span>><\/span><\/span>\n  <\/span><\/<\/span>GROUP_BY<\/span>><\/span><\/span>\n  <\/span><<\/span>ORDER_BY<\/span>><\/span><\/span>\n    <\/span><<\/span>ORDER_BY_LIST<\/span>><\/span><\/span>\n      <\/span><<\/span>ORDER_BY_LIST_ITEM<\/span>><\/span><\/span>\n        <\/span><<\/span>COLUMN_REF<\/span>><\/span><\/span>\n          <\/span><<\/span>TABLE_ALIAS<\/span>><\/span>T<\/span><\/<\/span>TABLE_ALIAS<\/span>><\/span><\/span>\n          <\/span><<\/span>COLUMN<\/span>><\/span>FISCAL_YEAR<\/span><\/<\/span>COLUMN<\/span>><\/span><\/span>\n        <\/span><\/<\/span>COLUMN_REF<\/span>><\/span><\/span>\n      <\/span><\/<\/span>ORDER_BY_LIST_ITEM<\/span>><\/span><\/span>\n    <\/span><\/<\/span>ORDER_BY_LIST<\/span>><\/span><\/span>\n  <\/span><\/<\/span>ORDER_BY<\/span>><\/span><\/span>\n<\/<\/span>QUERY<\/span>><\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Please note that UTL_XML.PARSEQUERY is suited for extended query dependency analysis only. DML may be parsed, but the resulting model is incomplete with 11.2.0.2 (e.g. clauses missing in the select statement are not included in the model, like the SET clause in an update statement). If you need to analyze PL\/SQL beyond PL\/Scope you still may need a 3rd<\/sup> party parser.<\/p>\n","protected":false},"excerpt":{"rendered":"
Last week I had a talk at Oracle’s OpenWorld 2011 titled Modern PL\/SQL Code Checking and Dependency Analysis. The problem I described in chapter 4 was to find all view columns using the column UNIT_COST of the table COSTS in the SH schema. Other usages of this column (e.g. in where or [\u2026]<\/span><\/p>\n","protected":false},"author":1,"featured_media":750,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[86,13,85],"class_list":["post-117","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-oracle","tag-code-analysis","tag-plsql","tag-sql"],"yoast_head":"\nUsing UTL_XML.PARSEQUERY for SQL Dependency Analysis - Philipp Salvisberg's Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.salvis.com\/blog\/2011\/10\/12\/using-utl_xml-parsequery-for-sql-dependency-analysis\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Using UTL_XML.PARSEQUERY for SQL Dependency Analysis - Philipp Salvisberg's Blog\" \/>\n<meta property=\"og:description\" content=\"Last week I had a talk at Oracle’s OpenWorld 2011 titled Modern PL\/SQL Code Checking and Dependency Analysis. The problem I described in chapter 4 was to find all view columns using the column UNIT_COST of the table COSTS in the SH schema. Other usages of this column (e.g. in where or [\u2026]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.salvis.com\/blog\/2011\/10\/12\/using-utl_xml-parsequery-for-sql-dependency-analysis\/\" \/>\n<meta property=\"og:site_name\" content=\"Philipp Salvisberg's Blog\" \/>\n<meta property=\"article:published_time\" content=\"2011-10-11T23:51:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-11-08T18:49:38+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/edd\/2013\/08\/tvdca_scope.png\" \/>\n\t<meta property=\"og:image:width\" content=\"963\" \/>\n\t<meta property=\"og:image:height\" content=\"437\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Philipp Salvisberg\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@phsalvisberg\" \/>\n<meta name=\"twitter:site\" content=\"@phsalvisberg\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Philipp Salvisberg\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2011\\\/10\\\/12\\\/using-utl_xml-parsequery-for-sql-dependency-analysis\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2011\\\/10\\\/12\\\/using-utl_xml-parsequery-for-sql-dependency-analysis\\\/\"},\"author\":{\"name\":\"Philipp Salvisberg\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#\\\/schema\\\/person\\\/34352245c48678b1a5a05d4bc1339515\"},\"headline\":\"Using UTL_XML.PARSEQUERY for SQL Dependency Analysis\",\"datePublished\":\"2011-10-11T23:51:00+00:00\",\"dateModified\":\"2023-11-08T18:49:38+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2011\\\/10\\\/12\\\/using-utl_xml-parsequery-for-sql-dependency-analysis\\\/\"},\"wordCount\":560,\"commentCount\":9,\"publisher\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#\\\/schema\\\/person\\\/34352245c48678b1a5a05d4bc1339515\"},\"image\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2011\\\/10\\\/12\\\/using-utl_xml-parsequery-for-sql-dependency-analysis\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/edd\\\/2013\\\/08\\\/tvdca_scope.png\",\"keywords\":[\"Code Analysis\",\"PL\\\/SQL\",\"SQL\"],\"articleSection\":[\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2011\\\/10\\\/12\\\/using-utl_xml-parsequery-for-sql-dependency-analysis\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2011\\\/10\\\/12\\\/using-utl_xml-parsequery-for-sql-dependency-analysis\\\/\",\"url\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2011\\\/10\\\/12\\\/using-utl_xml-parsequery-for-sql-dependency-analysis\\\/\",\"name\":\"Using UTL_XML.PARSEQUERY for SQL Dependency Analysis - Philipp Salvisberg's Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2011\\\/10\\\/12\\\/using-utl_xml-parsequery-for-sql-dependency-analysis\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2011\\\/10\\\/12\\\/using-utl_xml-parsequery-for-sql-dependency-analysis\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/edd\\\/2013\\\/08\\\/tvdca_scope.png\",\"datePublished\":\"2011-10-11T23:51:00+00:00\",\"dateModified\":\"2023-11-08T18:49:38+00:00\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2011\\\/10\\\/12\\\/using-utl_xml-parsequery-for-sql-dependency-analysis\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2011\\\/10\\\/12\\\/using-utl_xml-parsequery-for-sql-dependency-analysis\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2011\\\/10\\\/12\\\/using-utl_xml-parsequery-for-sql-dependency-analysis\\\/#primaryimage\",\"url\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/edd\\\/2013\\\/08\\\/tvdca_scope.png\",\"contentUrl\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/edd\\\/2013\\\/08\\\/tvdca_scope.png\",\"width\":963,\"height\":437},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/2011\\\/10\\\/12\\\/using-utl_xml-parsequery-for-sql-dependency-analysis\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Using UTL_XML.PARSEQUERY for SQL Dependency Analysis\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/\",\"name\":\"Philipp Salvisberg's Blog\",\"description\":\"Database-centric development\",\"publisher\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#\\\/schema\\\/person\\\/34352245c48678b1a5a05d4bc1339515\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/#\\\/schema\\\/person\\\/34352245c48678b1a5a05d4bc1339515\",\"name\":\"Philipp Salvisberg\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2010\\\/11\\\/phs_trivadis4.jpg\",\"url\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2010\\\/11\\\/phs_trivadis4.jpg\",\"contentUrl\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2010\\\/11\\\/phs_trivadis4.jpg\",\"width\":400,\"height\":400,\"caption\":\"Philipp Salvisberg\"},\"logo\":{\"@id\":\"https:\\\/\\\/www.salvis.com\\\/blog\\\/wp-content\\\/uploads\\\/2010\\\/11\\\/phs_trivadis4.jpg\"},\"sameAs\":[\"http:\\\/\\\/www.salvis.com\\\/\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Using UTL_XML.PARSEQUERY for SQL Dependency Analysis - Philipp Salvisberg's Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.salvis.com\/blog\/2011\/10\/12\/using-utl_xml-parsequery-for-sql-dependency-analysis\/","og_locale":"en_US","og_type":"article","og_title":"Using UTL_XML.PARSEQUERY for SQL Dependency Analysis - Philipp Salvisberg's Blog","og_description":"Last week I had a talk at Oracle’s OpenWorld 2011 titled Modern PL\/SQL Code Checking and Dependency Analysis. The problem I described in chapter 4 was to find all view columns using the column UNIT_COST of the table COSTS in the SH schema. Other usages of this column (e.g. in where or [\u2026]","og_url":"https:\/\/www.salvis.com\/blog\/2011\/10\/12\/using-utl_xml-parsequery-for-sql-dependency-analysis\/","og_site_name":"Philipp Salvisberg's Blog","article_published_time":"2011-10-11T23:51:00+00:00","article_modified_time":"2023-11-08T18:49:38+00:00","og_image":[{"width":963,"height":437,"url":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/edd\/2013\/08\/tvdca_scope.png","type":"image\/png"}],"author":"Philipp Salvisberg","twitter_card":"summary_large_image","twitter_creator":"@phsalvisberg","twitter_site":"@phsalvisberg","twitter_misc":{"Written by":"Philipp Salvisberg","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.salvis.com\/blog\/2011\/10\/12\/using-utl_xml-parsequery-for-sql-dependency-analysis\/#article","isPartOf":{"@id":"https:\/\/www.salvis.com\/blog\/2011\/10\/12\/using-utl_xml-parsequery-for-sql-dependency-analysis\/"},"author":{"name":"Philipp Salvisberg","@id":"https:\/\/www.salvis.com\/blog\/#\/schema\/person\/34352245c48678b1a5a05d4bc1339515"},"headline":"Using UTL_XML.PARSEQUERY for SQL Dependency Analysis","datePublished":"2011-10-11T23:51:00+00:00","dateModified":"2023-11-08T18:49:38+00:00","mainEntityOfPage":{"@id":"https:\/\/www.salvis.com\/blog\/2011\/10\/12\/using-utl_xml-parsequery-for-sql-dependency-analysis\/"},"wordCount":560,"commentCount":9,"publisher":{"@id":"https:\/\/www.salvis.com\/blog\/#\/schema\/person\/34352245c48678b1a5a05d4bc1339515"},"image":{"@id":"https:\/\/www.salvis.com\/blog\/2011\/10\/12\/using-utl_xml-parsequery-for-sql-dependency-analysis\/#primaryimage"},"thumbnailUrl":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/edd\/2013\/08\/tvdca_scope.png","keywords":["Code Analysis","PL\/SQL","SQL"],"articleSection":["Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.salvis.com\/blog\/2011\/10\/12\/using-utl_xml-parsequery-for-sql-dependency-analysis\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.salvis.com\/blog\/2011\/10\/12\/using-utl_xml-parsequery-for-sql-dependency-analysis\/","url":"https:\/\/www.salvis.com\/blog\/2011\/10\/12\/using-utl_xml-parsequery-for-sql-dependency-analysis\/","name":"Using UTL_XML.PARSEQUERY for SQL Dependency Analysis - Philipp Salvisberg's Blog","isPartOf":{"@id":"https:\/\/www.salvis.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.salvis.com\/blog\/2011\/10\/12\/using-utl_xml-parsequery-for-sql-dependency-analysis\/#primaryimage"},"image":{"@id":"https:\/\/www.salvis.com\/blog\/2011\/10\/12\/using-utl_xml-parsequery-for-sql-dependency-analysis\/#primaryimage"},"thumbnailUrl":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/edd\/2013\/08\/tvdca_scope.png","datePublished":"2011-10-11T23:51:00+00:00","dateModified":"2023-11-08T18:49:38+00:00","breadcrumb":{"@id":"https:\/\/www.salvis.com\/blog\/2011\/10\/12\/using-utl_xml-parsequery-for-sql-dependency-analysis\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.salvis.com\/blog\/2011\/10\/12\/using-utl_xml-parsequery-for-sql-dependency-analysis\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.salvis.com\/blog\/2011\/10\/12\/using-utl_xml-parsequery-for-sql-dependency-analysis\/#primaryimage","url":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/edd\/2013\/08\/tvdca_scope.png","contentUrl":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/edd\/2013\/08\/tvdca_scope.png","width":963,"height":437},{"@type":"BreadcrumbList","@id":"https:\/\/www.salvis.com\/blog\/2011\/10\/12\/using-utl_xml-parsequery-for-sql-dependency-analysis\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.salvis.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Using UTL_XML.PARSEQUERY for SQL Dependency Analysis"}]},{"@type":"WebSite","@id":"https:\/\/www.salvis.com\/blog\/#website","url":"https:\/\/www.salvis.com\/blog\/","name":"Philipp Salvisberg's Blog","description":"Database-centric development","publisher":{"@id":"https:\/\/www.salvis.com\/blog\/#\/schema\/person\/34352245c48678b1a5a05d4bc1339515"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.salvis.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/www.salvis.com\/blog\/#\/schema\/person\/34352245c48678b1a5a05d4bc1339515","name":"Philipp Salvisberg","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2010\/11\/phs_trivadis4.jpg","url":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2010\/11\/phs_trivadis4.jpg","contentUrl":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2010\/11\/phs_trivadis4.jpg","width":400,"height":400,"caption":"Philipp Salvisberg"},"logo":{"@id":"https:\/\/www.salvis.com\/blog\/wp-content\/uploads\/2010\/11\/phs_trivadis4.jpg"},"sameAs":["http:\/\/www.salvis.com\/"]}]}},"_links":{"self":[{"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/posts\/117","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/comments?post=117"}],"version-history":[{"count":6,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/posts\/117\/revisions"}],"predecessor-version":[{"id":12746,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/posts\/117\/revisions\/12746"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/media\/750"}],"wp:attachment":[{"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/media?parent=117"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/categories?post=117"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.salvis.com\/blog\/wp-json\/wp\/v2\/tags?post=117"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}