Using the Oracle database parser from Java using J

问题:

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.

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.

So essentially my question is:

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.

Edit:

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

The soultion I used is as follows:

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

回答1:

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.

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;

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

回答2:

Without executing the Query, your cannot get a feedback(or resultset, about it. Connection.preparedStatement just return you a PreparedStatement object, which is just a handler.

PreparedStatement.executeQuery() is only what executes the query actually, pushing to the database.