Oracle 10g - Get list of tables from query i'm executing

Question

I am working on internal tools of my organization, I have a automation need currently.

Example input query:

Select name,userid,url,address_line_1 from user join address on 
user.user_id = address.user_id where userid = 'xxyy';

what I need is list of tables

user
address

Is there a built in way in Oracle 10G to get the list of tables from this query? Or Is there a python parser that can help me with the list of tables from the query?

Note: This is a basic example, my queries run in several lines and are more complex.

Answer 1

You could create a package procedure to accept a SQL statement as input. What it would do is wrap the SQL in a CREATE VIEW and then analyze the dependencies of the resulting view.

Here is the source code.

CREATE OR REPLACE PACKAGE matt_analysis_pkg IS
  PROCEDURE analyze_sql ( p_sql CLOB );
END matt_analysis_pkg;
/

CREATE OR REPLACE PACKAGE BODY matt_analysis_pkg AS

  PROCEDURE analyze_sql (p_sql CLOB) IS
    PRAGMA AUTONOMOUS_TRANSACTION;
    l_sql CLOB;    
    l_column_count          INTEGER;
    l_view_name VARCHAR2(30);
    l_view_columns   VARCHAR2(4000);
  BEGIN
    DBMS_OUTPUT.put_line ('Current SQL: ' || p_sql);

    -- Parse the SQL to get the column count
    DECLARE
  l_cursor                INTEGER;
  l_column_descriptions   SYS.DBMS_SQL.desc_tab;

  BEGIN
    l_cursor   := sys.DBMS_SQL.open_cursor;

  -- parse SQL
  sys.DBMS_SQL.parse (c => l_cursor, statement => p_sql, language_flag => sys.DBMS_SQL.native);

  -- Describe columns
  sys.DBMS_SQL.describe_columns (c => l_cursor, col_cnt => l_column_count, desc_t => l_column_descriptions);

  sys.DBMS_SQL.close_cursor (l_cursor);

END;
  DBMS_OUTPUT.PUT_LINE('Column count = ' || l_column_count);

  -- Build view columns.  We need to do this because the column names in the SQL are not necessarily unique.    
  SELECT listagg('C' || lpad(rownum,4,'0'),',') within group ( order by rownum )
  INTO    l_view_columns 
  FROM   dual 
  CONNECT BY rownum <= l_column_count;

  DBMS_OUTPUT.PUT_LINE('l_view_columns = ' || l_view_columns);

  l_view_name := 'matt_analysis_view_' || lpad(matt_analysis_view_s.nextval,6,'0') || '$';
  DBMS_OUTPUT.PUT_LINE('l_view_name = ' || l_view_name);

  l_sql := 'CREATE OR REPLACE FORCE VIEW ' || l_view_name || ' (' || l_view_columns || ') AS ' || p_sql;

  EXECUTE IMMEDIATE l_sql;

  DBMS_OUTPUT.PUT_LINE('Objects referenced by current SQL: ');
  FOR r IN ( select referenced_owner || '.' || referenced_name || ' (' || referenced_type || ')' reference_info 
             from user_dependencies where name = upper(l_view_name)
             AND  referenced_name not like 'MATT_ANALYSIS%' ) LOOP
    DBMS_OUTPUT.PUT_LINE(r.reference_info);
  END LOOP;

  EXECUTE IMMEDIATE 'DROP VIEW ' || l_view_name;

  COMMIT;

  END analyze_sql;
END matt_analysis_pkg;
/

Tester

BEGIN
   matt_analysis_pkg.analyze_sql(p_sql => 
      'WITH oel AS ( SELECT * 
                     FROM oe_order_lines 
                     WHERE ship_from_org_id = 88 ) 
      SELECT oel.line_id, msi.segment1 
      FROM oel INNER JOIN mtl_system_items msi 
         ON msi.organization_id = 92 and msi.inventory_item_id = oel.inventory_item_id');
END;

Objects referenced by current SQL: 
APPS.MTL_SYSTEM_ITEMS (SYNONYM)
APPS.OE_ORDER_LINES (SYNONYM)

Answer 2

I am still waiting for the solution, but, for now, I came up with a quick and dirty way to get the table names along with aliases using the following python.

    from copy import deepcopy

    import cx_Oracle


    ############################################################################
    ####################### Database connection instantiation###################
    ############################################################################

    #connObj = pyodbc.connect(connString,autocommit=True)

    qry = """
                Select name,userid,url,address_line_1 from user join address on 
user.user_id = address.user_id where userid = 'xxyy'
            """


    def getConn():
        connObj = cx_Oracle.connect('asap', 'sdfssa', cx_Oracle.makedsn('DBLDEV03', 1521, '23432'))
        return connObj

    def destroy(connObj):
        connObj.commit()
        connObj.close()

        del connObj
    #####################       Logic  ########################################
    import datetime

    def getTablesFromQuery(qry):
        listTables = []
        listAliases = []
        connObj = getConn()
        cursor = connObj.cursor()
        listSpaceItems = qry.split(" ")
        found =False
        for spaceItem in listSpaceItems:
            if spaceItem != '' and spaceItem!='\n':
                spaceItem = spaceItem.replace("\n",'')
                listCommaItems = spaceItem.split(",")
                if found == True:
                    ##### We are assuming that the next item is always alias, the sql query should follow that rule to
                    ##### get the aliases properly.
                    listAliases.append(spaceItem)
                    found = False

                for commaItem in listCommaItems:
                    if commaItem != '':
                        item = commaItem

                        if "." in commaItem:
                            item=commaItem.split(".")[1]


                        cursor.execute('select * from all_tables where table_name=\''+item.upper()+'\'')
                        res = cursor.fetchall()
                        if res is not None and res.__len__()>0:
                            listTables.append(commaItem)
                            found = True
        destroy(connObj)
        return listTables,listAliases

    try:
        listTables, listAliases = getTablesFromQuery(qry)
        for item in listTables:
            print(''+item)

    except:
        print('Exception..')


############################################################################
####################### close database connection###########################
############################################################################

Answer 3

Interesting question.

You could build a little SQL analyzer in PL/SQL using DBMS_FGA. The idea would be:

• Automatically modify the input SQL to also use a table with a FGA policy on it
• In that FGA policy, you will have access to the current SQL (the first 32K of it, anyway. That's a limitation...)
• Use the current SQL to build a throw-away view on the current SQL
• Read the throw-away view's dependencies from USER_DEPENDENCIES
• Drop the throw-away view.

Here is an example of how it would work:

(I apologize for putting my first name in all the objects; I share this database with others.)

-- Tester
BEGIN
   matt_analysis_pkg.analyze_sql(p_sql => 
      'WITH oel AS ( SELECT * 
                     FROM oe_order_lines 
                     WHERE ship_from_org_id = 88 ) 
      SELECT oel.line_id, msi.segment1 
      FROM oel INNER JOIN mtl_system_items msi 
         ON msi.organization_id = 92 and msi.inventory_item_id = oel.inventory_item_id');
END;
/

Objects referenced by current SQL: 
APPS.MTL_SYSTEM_ITEMS (SYNONYM)
APPS.OE_ORDER_LINES (SYNONYM)

The example (below) just reports the 1st level of dependencies. You could use DBA_DEPENDENCIES recursively to get more depth. Also, this version just writes to DBMS_OUTPUT.

As others have reported, just because a SQL depends on an object doesn't mean Oracle will actually access that object at run-time. Still, I think this is pretty close to what you were asking for.

Also, I noticed you tagged your question with Oracle 10g. I think the only thing in my solution that won't work in 10g is my direct access of a sequence. You'll have to replace that part with SELECT ... INTO to get the current sequence value.

Anyway, here is the source code for it (Oracle 12c):

-- This table doesn't do anything other than have a FGA policy on it.
CREATE TABLE matt_analysis_tab ( dummy varchar2(1) );

INSERT INTO matt_analysis_tab (dummy) VALUES ('X');

-- Sequence so we can create unique view names, in case two people analyze at the same time.
CREATE SEQUENCE matt_analysis_view_s;

-- Package to do the work.
CREATE OR REPLACE PACKAGE matt_analysis_pkg IS
  PROCEDURE analyze_sql ( p_sql CLOB );
  PROCEDURE analyze_current_sql (schema_name VARCHAR2, table_name VARCHAR2, policy_name VARCHAR2);
END matt_analysis_pkg;
/

CREATE OR REPLACE PACKAGE BODY matt_analysis_pkg AS

  PROCEDURE analyze_sql (p_sql CLOB) IS
    l_modified_sql CLOB := 'WITH v1$ AS ( SELECT /*+ MATERIALIZE */ dummy FROM matt_analysis_tab ) SELECT v1$.dummy, v2$.* FROM v1$, ( ' || p_sql || ') v2$';
  BEGIN
    DBMS_OUTPUT.PUT_LINE('l_modified_sql := ' || l_modified_sql);
    EXECUTE IMMEDIATE l_modified_sql;
  END analyze_sql;


  PROCEDURE analyze_current_sql (schema_name VARCHAR2, table_name VARCHAR2, policy_name VARCHAR2) IS
    PRAGMA AUTONOMOUS_TRANSACTION;
    l_sql CLOB;    
    l_column_count          INTEGER;
    l_view_name VARCHAR2(30);
    l_view_columns   VARCHAR2(4000);
  BEGIN
    l_sql := SYS_CONTEXT ('userenv', 'CURRENT_SQL',4000) 
    || SYS_CONTEXT ('userenv', 'CURRENT_SQL1',4000)
    || SYS_CONTEXT ('userenv', 'CURRENT_SQL2',4000)
    || SYS_CONTEXT ('userenv', 'CURRENT_SQL3',4000)
    || SYS_CONTEXT ('userenv', 'CURRENT_SQL4',4000)
    || SYS_CONTEXT ('userenv', 'CURRENT_SQL5',4000)
    || SYS_CONTEXT ('userenv', 'CURRENT_SQL6',4000)
    || SYS_CONTEXT ('userenv', 'CURRENT_SQL7',4000)
    ;
    DBMS_OUTPUT.put_line ('Current SQL: ' || l_sql);
    DBMS_OUTPUT.put_line ('Current SQL length (calc): ' || length(l_sql));
    DBMS_OUTPUT.put_line ('Current SQL length (userenv): ' || SYS_CONTEXT('userenv','CURRENT_SQL_LENGTH'));

    -- Parse the SQL to get the column count
    DECLARE
  l_cursor                INTEGER;
  l_column_descriptions   SYS.DBMS_SQL.desc_tab;

  BEGIN
    l_cursor   := sys.DBMS_SQL.open_cursor;

  -- parse SQL
  sys.DBMS_SQL.parse (c => l_cursor, statement => l_sql, language_flag => sys.DBMS_SQL.native);

  -- Describe columns
  sys.DBMS_SQL.describe_columns (c => l_cursor, col_cnt => l_column_count, desc_t => l_column_descriptions);

  sys.DBMS_SQL.close_cursor (l_cursor);

END;
  DBMS_OUTPUT.PUT_LINE('Column count = ' || l_column_count);

  -- Build view columns.  We need to do this because the column names in the SQL are not necessarily unique.    
  SELECT listagg('C' || lpad(rownum,4,'0'),',') within group ( order by rownum )
  INTO    l_view_columns 
  FROM   dual 
  CONNECT BY rownum <= l_column_count;

  DBMS_OUTPUT.PUT_LINE('l_view_columns = ' || l_view_columns);

  l_view_name := 'matt_analysis_view_' || lpad(matt_analysis_view_s.nextval,6,'0') || '$';
  DBMS_OUTPUT.PUT_LINE('l_view_name = ' || l_view_name);

  l_sql := 'CREATE OR REPLACE FORCE VIEW ' || l_view_name || ' (' || l_view_columns || ') AS ' || l_sql;

  EXECUTE IMMEDIATE l_sql;

  DBMS_OUTPUT.PUT_LINE('Objects referenced by current SQL: ');
  FOR r IN ( select referenced_owner || '.' || referenced_name || ' (' || referenced_type || ')' reference_info 
             from user_dependencies where name = upper(l_view_name)
             AND  referenced_name not like 'MATT_ANALYSIS%' ) LOOP
    DBMS_OUTPUT.PUT_LINE(r.reference_info);
  END LOOP;

  EXECUTE IMMEDIATE 'DROP VIEW ' || l_view_name;

  COMMIT;

  END analyze_current_sql;
END matt_analysis_pkg;
/

-- Create the FGA policy 
BEGIN
  DBMS_FGA.add_policy (
    object_schema     => NULL,  -- My current schema
    object_name       => 'MATT_ANALYSIS_TAB',
    policy_name       => 'MATT_ANALYSIS_POLICY',
    audit_condition   => NULL,
    audit_column      => NULL,
    handler_schema    => NULL,  -- My current schema
    handler_module    => 'matt_analysis_pkg.analyze_current_sql',
    enable            => TRUE);
END;
/

-- Script to drop the policy, just in case
--EXEC  DBMS_FGA.drop_policy (NULL, 'MATT_ANALYSIS_TAB', 'MATT_ANALYSIS_POLICY');