Using Oracle Database Parser from Java Using JDBC

Question

I am writing a tool in Java that sends instructions to a database that are later launched. I am using JDBC to connect to the database. The database is Oracle 10g.

Before the instructions are written to the database, I want to analyze them to check when they will work later, and there will be no problems. I studied the use of the ANTLR solution for this, since there are grammars available, but of course, if I have a database connection, there should be a way to use the databases created in the parser.

So essentially my question is:

Is there a way to use JDBC I can make a database parser call by passing it an SQL statement and it will give me some feedback telling me if it was successful or any error messages?

Any help is appreciated, thanks a lot.

Edit:

Using connection.prepareStatement does not seem to work, for example, these results are successfully parsed!

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

Answer 1

I have no idea what exactly you want to achieve, but maybe try using the DBMS_SQL package and its PARSE method. This only works with DML statements. This is what Oracle SQL Developer does.

This parser can also be used for DML statements. For PL / SQL, this will require some configuration. As far as I know, no one has spent enough time to create a real fully validating parser for Oracle DDL.

Here is an example of 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; 

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

Answer 2

Without fulfilling the request, you cannot receive feedback (or resultet, about this. Connection.preparedStatement just return the PreparedStatement object, which is only a handler.

PreparedStatement.executeQuery() is what actually executes the query by clicking on the database.