使用JDBC从Java使用Oracle数据库解析器 [英] Using the Oracle database parser from Java using JDBC

问题描述

我正在用Java编写一个工具，它将语句提交给数据库，这些数据库稍后运行。我正在使用JDBC连接到数据库。数据库是Oracle 10g。

I'm writing a tool in Java which submits statements to a database, which are later run. I'm using JDBC to connect to the database. The database is Oracle 10g.

在将语句写入数据库之前，我想解析它们以检查它们何时运行以后不会出现问题。我考虑使用ANTLR解决方案，因为有可用的语法，但肯定如果我有数据库的连接，必须有一种方法来使用解析器中内置的数据库。

Before the statements are written to the database I want to parse them to check when they run later there will be no problems. I looked into using an ANTLR solution to this as there are grammars available, but surely If I have a connection to the database there must be a way to use the databases built in parser.

基本上我的问题是：

有没有办法使用JDBC我可以调用数据库解析器传递一个SQL语句，它会返回给我一些一些反馈，告诉我它是否成功或有任何错误消息？

Is there a way using JDBC I can make a call to the database parser passing it an SQL statement and it will return me some sort of feedback, telling me if it was successful or any error messages?

非常感谢任何帮助，
非常感谢。

Any help is greatly appreciated, Many thanks.

编辑：

使用connection.prepareStatement似乎不起作用，例如此输出已成功解析！

Using connection.prepareStatement does not seem to work for instance this outputs parsed successfully!

String statement = "WHERE DISTINCT SELECT";
    Connection connection;
    try {
        connection = this.controller.getDataSource().getConnection();
        connection.prepareStatement(statement);
        connection.close();
        mainPanel.setPositiveText("Parsed Successfully!");
    } catch (Exception e) {
        mainPanel.setNegativeText("ERROR: " + e.getMessage());
        return;
    }

我使用的灵魂如下：

    String statement = "DECLARE "
            + "myNumber NUMBER; "
            + "BEGIN "
            + "myNumber := SYS.dbms_sql.open_cursor; "
            + "SYS.DBMS_SQL.PARSE(myNumber, '" + text + "', SYS.DBMS_SQL.NATIVE); "
            + "END;";

    Connection connection;
    try {
        connection = this.controller.getDataSource().getConnection();
        PreparedStatement preparedStatement = connection.prepareStatement(statement);
        preparedStatement.execute();
        connection.close();
        mainPanel.setPositiveText("Parsed Successfully!");
    } catch (Exception e) {
        mainPanel.setNegativeText("ERROR: " + e.getMessage());
        System.out.println(e.getMessage());
        return;
    }

推荐答案

我不知道到底是什么你想要实现但是你可能会尝试使用包 DBMS_SQL ，它的方法是 PARSE 。这仅适用于 DML 语句。这就是Oracle SQL Developer所做的。

I have no idea what exactly do you want to achiveve but maybe yo migh try to use package DBMS_SQL and it's method PARSE. This works only with DML statements only. This is what Oracle SQL Developer does.

这个解析器也可能用于DML语句。对于PL / SQL，它需要一些调整。据我所知，没有人花足够的时间为Oracle的DDL创建一个真正完全验证的解析器。

This parser might be used for DML statements too. For PL/SQL it will need some tweaking. As far as I know nobody spent enough time to create a real fully validating parser for Oracle's DDL.

这里是我如何使用它的一个例子：

Here is an example how I use it:

declare 
 l_cursor number := dbms_sql.open_cursor; 
 l_offset number := -1 ; 
begin 
  begin 
    dbms_sql.parse( l_cursor, :st, dbms_sql.native ); 
  exception when others then
   l_offset := dbms_sql.last_error_position;
  end;
dbms_sql.close_cursor( l_cursor );
  :off := l_offset;
end;

只需执行此块即可。传递一个类型为VARCHAR2（String）的输入参数（最大32KB）和一个输出参数NUMBER。

Simply execute this block. Pass one input parameter of type VARCHAR2(String) (max 32KB) and one output parameter NUMBER.

这篇关于使用JDBC从Java使用Oracle数据库解析器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！