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
The ANTLR (v3, v4) parser generator has had a number of Oracle SQL and PL/SQL grammars written for it; see the grammar list (v3) for details. Of those:
After working the same issue, I managed to get a SQL parser working:
My code looks like this:
import oracle.jdeveloper.db.DatabaseConnections;
import oracle.javatools.db.sql.SQLQueryBuilder;
import oracle.javatools.db.Database;
...
// load the database connections
// this is specific to Oracle SQL developer
DatabaseConnections connections = DatabaseConnections.getPrivateInstance(
(new File("src/test/resources/connection.xml")).toURI().toURL(),
"somePassword");
// get the one we are interested in
Database database = connections.getDatabase("the-name-of-a-sqldeveloper-connection");
SQLQueryBuilder queryBuilder = SQLQueryBuilderFactory.createBuilder(
database, new Schema("OPTIONAL_SCHEMA"), "select * from some_table");
The challenges to get this working are:
<?xml version = '1.0' encoding = 'UTF-8'?>
<References xmlns="http://xmlns.oracle.com/adf/jndi">
<Reference name="the-name-of-a-sqldeveloper-connection" className="oracle.jdeveloper.db.adapter.DatabaseProvider" xmlns="">
<Factory className="oracle.jdevimpl.db.adapter.DatabaseProviderFactory1212"/>
<RefAddresses>
<StringRefAddr addrType="password">
<Contents>HSx10FtlsPc=</Contents>
</StringRefAddr>
<StringRefAddr addrType="oraDriverType">
<Contents>thin</Contents>
</StringRefAddr>
...
To get such a file you will need to dig into the folder where Oracle SQL Developer settings are stored and just copy-paste that content into your own file.
Now, assuming you managed to get this far here are the problems and the points where I got disappointed by the end solution:
The reason I posted this answer is to drive community attention to the fact that having an Oracle SQL parser is perfectly feasible and maybe one day Oracle will consider exposing the parser as a competitive advantage (I am sure there are users out there that would happily pay some fees to get a license).
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.
Given that Oracle Corporation couldn't keep the SQL parser for the SQL and PL/SQL VM's in sync when the two had different SQL parsers, it's unlikely that a third party would be able to create a "fully compliant" parser.
What data are you trying to extract from the query? The Oracle database itself may have other facilities that would allow you to extract that information without parsing the query first.
Have you considered General SQL Parser? I don't have any experience with it myself but browsing their website it has potential. Personally I have rolled my own built on the parser in Eclipse Data Tools Platform (sorry I can't share, it's proprietary), but now I will have to evaluate the one I linked above because it claims to have more coverage of Oracle SQL than my parser does.
Our DMS Software Reengineering Toolkit can be obtained with an Oracle PLSQL parser, or a SQL 2011 parser. DMS provides a parser, builds an AST, lets you investigate/transform the tree arbitrarily, and regenerate the AST as source code if you want to do that.
You can test out the parser by downloading the PLSQL formatter available from the website; that uses the same underlying DMS machinery; just doesn't analyze/transform the tree.
You might need to wrap the SQL statements in a simple PLSQL procedure.
Try this http://www.ibrezina.net/OracleSQL.tgz. It's ANTLR3.3 grammar for Oracle's PL/SQL. The grammar is intended for C target but can be easily converted into Java or C#. Your task, list of tables included in a query is already included as an example.
jOOQ has a built-in parser that can parse a lot of the SQL by any of its supported database products, including Oracle SQL. It can't parse all of it, but it may be enough for most use-cases. For example, it can parse the Oracle outer join operator (+) and even transform that to ANSI SQL. A parser and translator demo can be seen here, for example:
-- Input
SELECT *
FROM t, u
WHERE t.id = u.id(+)
-- Output
SELECT *
FROM t
LEFT OUTER JOIN u
ON t.id = u.id
You can use the parser from the website, as a CLI, or programmatically using Java, Kotlin, Scala, etc:
Select<?> select = ctx.parser().parseSelect(
"""
SELECT *
FROM t, u
WHERE t.id = u.id(+)
"""
);
The parse output is ordinary jOOQ API, which you can transform to other SQL using jOOQ's model API
FlowHigh ships with an SQL parser that supports the Oracle SQL dialect including non ANSI joins. You can use the UI or access it programmatically for automation using the Python SDK.
As input you give it an SQL statement and as output it generates a JSON or XML message.
The SDK also ships with a bunch of abstractions to satisfy common use cases, e.g. give me all the tables, joins, columns, filters etc. in a query
It can be used for static code analysis to detect bad SQL and SQL anti patterns such as self joins, SELECT * etc.
Here is a Medium blog post that describes it in more detail https://medium.com/@sonradata/parsing-oracle-sql-with-flowhigh-e366dae23920
Disclaimer: I work for Sonra
