Finding out the tables in a query: a free SQLParser

We have hundreds of free-hand ,’ quick and dirty’ ,   MicroStrategy reports and we need to find out the tables that are accessed from them.   Most of the queries exceed hundreds of lines , with clauses,views, inline views  ,etc… The db is 11.2.0.4.

So, the ‘quick and dirty’ way seems as using oracle dbms_sql.parse   for parsing  and then querying the v$sql_plan table. Using the test environment would be the best practice, yet there is a security issue in the code that I’ll explain in the end.

The metadata table of MicroStrategy (derived as sql_source)  keeps the sqls in multiple rows because of the varchar limitation. I need to concat them but  listagg  also suffers from the infamous ORA-01489 error . Therefore I’ve used XMLAGG to concat  the rows in  c1 cursor.  Sqlstatements in varchar2 or CLOB format is the only input, so c1 would be changed as desired.

I’ve created 2 tables for keeping the sql’s and the execution plans for further analysis.

drop table my_sql;
create table my_sql (sql_id VARCHAR2(13),sql_fulltext CLOB);

drop table my_sql_plan;
create table my_sql_plan as select * from v$sql_plan where 1=0;

The rest is getting the sqlstatements from the table, parsing them (not executing) and getting the sqlids and execution plans into our final tables.

DECLARE
   CURSOR c1
   IS
        SELECT is_rep_name,
               DBMS_XMLGEN.CONVERT (
                  REPLACE (
                     REPLACE (
                        XMLAGG (XMLELEMENT ("a", sqlstatement)
                        ORDER BY sqlstatementseq ASC NULLS LAST).getClobVal (),
                        '<a>',
                        ''),
                     '</a>',
                     ''),
                  1)
                  AS sql_text
          FROM SQL_SOURCE
         WHERE IS_DB_INST1_NAME LIKE '%ODS%'
      GROUP BY is_rep_name;

   cursor_name   INTEGER;
   my_sql_id     VARCHAR2 (32);

BEGIN
   FOR sql_rec IN c1
   LOOP

      cursor_name := DBMS_SQL.open_cursor;
      DBMS_SQL.PARSE (cursor_name, sql_rec.sql_text, DBMS_SQL.NATIVE);

      SELECT sql_id
        INTO my_sql_id
        FROM v$sql
       WHERE DBMS_LOB.compare (sql_fulltext, sql_rec.sql_text) = 0;

      INSERT INTO my_sql_plan
         SELECT sp.*
           FROM v$sql_plan sp
          WHERE sp.sql_id = my_sql_id;

      INSERT INTO my_sql values(my_sql_id,sql_rec.sql_text);
      COMMIT;

     -- DBMS_OUTPUT.PUT_LINE (my_sql_id);
      DBMS_SQL.CLOSE_CURSOR (cursor_name);
   END LOOP;
END;

Finally we’ve got the resulting table to query in anyway we like.  FILTER and PROJECTION predicates may also be helpful for further lineage analysis. We may also check the indexes for fast full index scan.

MY_SQL table is useful for joining the tables to the sql queries.

-- Get all the tables accessed:
select  distinct object_owner,object_name from my_sql_plan where object_type = 'TABLE' ;
--return to the sql_statement.
select  to_Char(substr(sql_fulltext,1,30)),object_owner,object_name from my_sql_plan msp,my_Sql ms where msp.sql_id=ms.sql_id and msp.object_type = 'TABLE'

Finaly, dbms_sql.parse is great for the queries and DML , but unfortunately the DDL  is executed even you only parse it. If there is any DDL in the input table , it is executed! So the code is open for sql injection .

I will search  the way to understand or restrict  whether the sql is ddl or dml  before the parse?  Any ideas?

Until then, stay on the test 🙂