Using the Oracle database parser from Java using JDBC

Question

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

The Statement execQuery or execUpdate methods will throw the exception. — Gilbert Le Blanc
– Gilbert Le Blanc, Commented Dec 23, 2013 at 14:38

ibre5041 · Accepted Answer · 2013-12-23 15:11:53Z

7

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.

edited Dec 23, 2013 at 15:11

answered Dec 23, 2013 at 14:36

ibre5041

5,3261 gold badge24 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

John Walker Over a year ago

Hi Ivan, dbms_sql.parse seems exactly like what I need. Could you give me a small example of its usage please? I want any variables used to be temporary, there should be nothing left behind in the database after the statements are executed.

Maheswaran Ravisankar Over a year ago

Can this be used with jdbc , I believe we need to do execute(). SO, will it help?

ibre5041 Over a year ago

You have to executed if with JDBC as an anonymous PL/SQL block.

ibre5041 Over a year ago

Beware you really should call dbms_sql.close_cursor( l_cursor ); in the PL/SQL block. Otherwise you will leak resources on DB server side. (See DB paramater OPEN_CURSORS)

Devaraj Mahesan · Accepted Answer · 2013-12-23 15:21:17Z

1

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.

answered Dec 23, 2013 at 15:21

Devaraj Mahesan

4781 gold badge7 silver badges15 bronze badges

Collectives™ on Stack Overflow

Using the Oracle database parser from Java using JDBC

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related