Parse SQL via Oracle's JDBC driver

Question

I'd like to test whether given SQL statement is syntactically and semantically valid (ie. no syntax errors and no field misspellings).

For most databases Connection.prepareStatement and PreparedStatement.getMetaData would do the trick (no exception == good query). Unfortunately Oracle's newest driver only parses like this only SELECT queries, but not other kind of queries. Older drivers don't do even that.

Is there some other facility provided by Oracle for parsing SQL statements?

Answer 1

You can use the Oracle DBMS_SQL package to parse a statement held 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 could wrap that up into a stored function that just returned e.g. 1 if the statement was valid, 0 if invalid, like this:

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;

You could then use it something like this PL/SQL example:

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