Oracle SQL 解析器
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5735791/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Parser for Oracle SQL
提问by Will
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.
对于我当前的项目，我需要一个解析 Oracle SQL 语句的 SQL 解析器。目前我一直在使用 jsqlparser，它适用于简单的查询。但是当特定函数发生时（例如 cast() 或 (+)）解析器失败。
Can anyone suggest a parser that is fully compliant to Oracle SQL?
任何人都可以建议一个完全符合 Oracle SQL 的解析器吗？
Best, Will
最好的，威尔
采纳答案by ChrisH
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.
您是否考虑过通用 SQL 解析器？我自己没有任何经验，但浏览他们的网站有潜力。就个人而言，我已经在 Eclipse Data Tools Platform 中的解析器上推出了自己的构建（对不起，我不能分享，它是专有的），但现在我必须评估我上面链接的那个，因为它声称对 Oracle SQL 的覆盖范围比我的解析器可以。
回答by SimonJ
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:
ANTLR ( v3, v4) 解析器生成器已经为它编写了许多 Oracle SQL 和 PL/SQL 语法；有关详细信息，请参阅语法列表(v3)。那些：
- I've used Andrey Kharitonkin's "Oracle PL/SQL Grammar for ANTLR v3"; from memory it supported most SQL and PL/SQL syntax from the 8i era, with a few bits and pieces that appeared in 9i and 10g
- Patrick Higgins' "PL/SQL"grammar is newer and claims to support most 11g syntax, but it appears to just swallow most DML statements - not too useful if you're specifically interested in SQL
- 我使用过 Andrey Kharitonkin 的“Oracle PL/SQL Grammar for ANTLR v3”；从内存来看，它支持 8i 时代的大多数 SQL 和 PL/SQL 语法，只有一些零碎的东西出现在 9i 和 10g 中
- Patrick Higgins 的“PL/SQL”语法更新并声称支持大多数 11g 语法，但它似乎只能吞下大多数 DML 语句 - 如果您对 SQL 特别感兴趣，则不太有用
回答by cristi
After working the same issue, I managed to get a SQL parser working:
在解决了同样的问题后，我设法让 SQL 解析器正常工作：
- Download Oracle SQL Developer from http://www.oracle.com/technetwork/developer-tools/sql-developer/overview/index.html.
- Oracle SQL developer contains a set jars and some of the APIs are documented as part of Oracle Fusion Middleware Java API Reference for Oracle Extension SDK (a link to the javadoc: http://docs.oracle.com/middleware/1212/jdev/ESDKJ/index.html?oracle/javatools/db/sql/class-use/SQLQueryBuilder.SQLQueryObjectSet.html).
- One of the available classes is oracle.javatools.db.sql.SQLQueryBuilder.
- 从http://www.oracle.com/technetwork/developer-tools/sql-developer/overview/index.html下载 Oracle SQL Developer 。
- Oracle SQL 开发人员包含一组 jar，其中一些 API 被记录为 Oracle 扩展 SDK 的 Oracle 融合中间件 Java API 参考的一部分（指向 javadoc 的链接：http: //docs.oracle.com/middleware/1212/jdev/ ESDKJ/index.html?oracle/javatools/db/sql/class-use/SQLQueryBuilder.SQLQueryObjectSet.html）。
- 可用的类之一是oracle.javatools.db.sql.SQLQueryBuilder。
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:
实现这一目标的挑战是：
- Getting the Oracle SQL Developer is a challenge. To do this, you will need to hack the files Oracle SQL Developer creates to persist those connections; the connection.xmlin the above example looks something like this:
- 获得 Oracle SQL Developer 是一项挑战。为此，您需要破解 Oracle SQL Developer 创建的文件以保持这些连接；上面例子中的connection.xml看起来像这样：
<?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.
要获得这样的文件，您需要深入到存储 Oracle SQL Developer 设置的文件夹中，然后将该内容复制粘贴到您自己的文件中。
- Next challenge is to define the database password. That is encrypted but this post (https://community.oracle.com/message/12359081) should be very helpful - just check the answer at the end of the post.
- 下一个挑战是定义数据库密码。这是加密的，但这篇文章 ( https://community.oracle.com/message/12359081) 应该非常有帮助 - 只需查看文章末尾的答案即可。
Now, assuming you managed to get this far here are the problems and the points where I got disappointed by the end solution:
现在，假设你设法做到了这里是问题和我对最终解决方案感到失望的地方：
- The API in the builder is decent but parsing will perform a query execution(and this might be a big issue - in my case I needed the parsing to be fast).
- The API is not officially public. Without being able to quote here the precise wording, I got an Oracle answer that stated there is no officially supported Oracle parser (the alluded reason was that this is a very valuable technology that will not be sold or licensed).
- While this is more of a hack than a solution, I realized that it might be useful for some cases (not mine). I consider that using it in real life scenarios might be highly risky from both technical and legal perspectives.
- 构建器中的 API 不错，但解析将执行查询（这可能是一个大问题 - 在我的情况下，我需要快速解析）。
- API并未正式公开。由于无法在这里引用准确的措辞，我得到了一个 Oracle 的回答，其中指出没有官方支持的 Oracle 解析器（暗示的原因是这是一项非常有价值的技术，不会出售或许可）。
- 虽然这更像是一种破解而不是解决方案，但我意识到它可能对某些情况（不是我的）有用。我认为，从技术和法律的角度来看，在现实生活场景中使用它可能具有很高的风险。
The reason I posted this answer is to drive community attention to the fact that having an Oracle SQL parser is perfectly feasibleand 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).
我发布这个答案的原因是为了让社区关注这样一个事实，即拥有一个Oracle SQL 解析器是完全可行的，也许有一天 Oracle 会考虑将解析器作为一种竞争优势来公开（我相信那里的用户会很乐意支付获得许可证的一些费用）。
回答by ik_zelf
Will, why not use the Oracle parser?
请问，为什么不用oracle解析器呢？
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.
您确实需要一个 oracle 数据库连接。如果输出是您想要的，这是获得您想要的最简单方法，而无需为轮子重新发明其他颜色。在本例中，我将 sql 限制为 4000 个字符，但您可以将 varchar2 的 pl/sql 数组提供给 dbms_sql.parse 函数，这样做可以让您解析难以想象的大小的 sql。
回答by Justin Cave
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.
鉴于当两者具有不同的 SQL 解析器时，Oracle Corporation 无法保持 SQL 和 PL/SQL VM 的 SQL 解析器同步，因此第三方不太可能创建“完全兼容”的解析器。
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.
您试图从查询中提取哪些数据？Oracle 数据库本身可能具有其他功能，可让您无需先解析查询即可提取该信息。
回答by Ira Baxter
Our DMS Software Reengineering Toolkitcan 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.
我们的DMS 软件再造工具包可以通过 Oracle PLSQL 解析器或 SQL 2011 解析器获得。DMS 提供了一个解析器，构建了一个 AST，让你可以任意调查/转换树，如果你想这样做的话，还可以将 AST 重新生成为源代码。
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.
您可以通过从网站下载 PLSQL 格式化程序来测试解析器；使用相同的底层 DMS 机制；只是不分析/转换树。
You might need to wrap the SQL statements in a simple PLSQL procedure.
您可能需要将 SQL 语句包装在一个简单的 PLSQL 过程中。
回答by ibre5041
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.
试试这个http://www.ibrezina.net/OracleSQL.tgz。它是 Oracle PL/SQL 的 ANTLR3.3 语法。该语法适用于 C 目标，但可以轻松转换为 Java 或 C#。您的任务，查询中包含的表列表已作为示例包含在内。
