Parse SQL via Oracle's JDBC driver
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 valuesORA-06512: at "SYS.DBMS_SYS_SQL", line 824ORA-06512: at "SYS.DBMS_SQL", line 32ORA-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 integeris 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)');