Parse SQL via Oracle JDBC Driver

Question

I would like to check if a given SQL statement is syntactically and semantically valid (i.e. no syntax errors and errors in a field).

For most of the databases Connection.prepareStatement and PreparedStatement.getMetaData trick will be performed (without exception == good query). Unfortunately, the newest Oracle driver only parses such SELECT queries, but not other queries. Older drivers do not.

Is there any other tool provided by Oracle for parsing SQL queries?

Answer 1

You can use the Oracle DBMS_SQL package to parse the statement contained in a string. For example:

 SQL> declare 2 c integer; 3 l_statement varchar2(4000) := 'insert into mytable (col) values (1,2)'; 4 begin 5 c := dbms_sql.open_cursor; 6 dbms_sql.parse(c,l_statement,dbms_sql.native); 7 dbms_sql.close_cursor(c); 8 end; 9 / declare * ERROR at line 1: ORA-00913: too many values ORA-06512: at "SYS.DBMS_SYS_SQL", line 824 ORA-06512: at "SYS.DBMS_SQL", line 32 ORA-06512: at line 6 

You can wrap this in a stored function that just returned, for example. 1 if the statement was valid, 0 if it is invalid, for example:

 function sql_is_valid ( p_statement varchar2 ) return integer is c integer; begin c := dbms_sql.open_cursor; dbms_sql.parse(c,p_statement,dbms_sql.native); dbms_sql.close_cursor(c); return 1; exception when others then return 0; end; 

Then you can use it as an example of this PL / SQL:

 :n := sql_is_valid('insert into mytable (col) values (1,2)');