Ferhat's Blog

There will be only one database

Archive for July, 2016

Finding out the tables in a query: a free SQLParser

Posted by fsengonul on July 27, 2016

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

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.

   CURSOR c1
        SELECT is_rep_name,
               DBMS_XMLGEN.CONVERT (
                  REPLACE (
                     REPLACE (
                        XMLAGG (XMLELEMENT ("a", sqlstatement)
                        ORDER BY sqlstatementseq ASC NULLS LAST).getClobVal (),
                  AS sql_text
      GROUP BY is_rep_name;

   cursor_name   INTEGER;
   my_sql_id     VARCHAR2 (32);

   FOR sql_rec IN c1

      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);

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

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 🙂


Posted in oracle | Leave a Comment »