Parser for Oracle SQL

Question

For my current project I need a SQL parser that parses Oracle SQL statements. Currently I've been using jsqlparser, which worked well for simple queries. But when specific functions occur (e.g. cast() or (+)) the parser fails.

Can anyone suggest a parser that is fully compliant to Oracle SQL?

Best, Will

Answer 1

Will, why not use the Oracle parser?

create global temporary table plans as select * from table(dbms_xplan.display_cursor());
--/
declare
c number;
i varchar2(30);
l number;
stmt varchar2(4000);
begin
delete from plans;
stmt:= 'select z.* from z,skew1 where z.z = skew1.fillblocks';
l:= length(stmt);
c:=dbms_sql.open_cursor();
dbms_sql.parse (c, stmt,dbms_sql.native);
select distinct sql_id into i from v$open_cursor where sid in (select sid from v$mystat) and substr(sql_text,1,l) = substr(stmt,1,l);
insert into plans select * from table(dbms_xplan.display_cursor(i));
dbms_output.put_Line ('sql_id:'||i);
end;
/
select * from plans;

PLAN_TABLE_OUTPUT                                                             
----------------------------------------------------------------------------  
SQL_ID  97qc3ynmw1pa4, child number 0                                         
-------------------------------------                                         
select z.* from z,skew1 where z.z = skew1.fillblocks                          

Plan hash value: 942457544                                                    

----------------------------------------------------------------------------  
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |  
----------------------------------------------------------------------------  
|   0 | SELECT STATEMENT   |       |       |       |    85 (100)|          |  
|*  1 |  HASH JOIN         |       |     1 |   410 |    85   (2)| 00:00:02 |  
|   2 |   TABLE ACCESS FULL| Z     |     1 |     9 |     2   (0)| 00:00:01 |  
|   3 |   TABLE ACCESS FULL| SKEW1 |  6000 |  2349K|    82   (0)| 00:00:01 |  
----------------------------------------------------------------------------  

Predicate Information (identified by operation id):                           
---------------------------------------------------                           

   1 - access("Z"."Z"=INTERNAL_FUNCTION("SKEW1"."FILLBLOCKS"))   

You do need an oracle database connection. If the output is what you want, it is the easiest way to get what you want, without re-inventing an other color for a wheel. In this example I limited the sql to 4000 characters but you could feed a pl/sql array of varchar2 into to dbms_sql.parse function, doing so allows you to parse sql's of unimaginable sizes.