DBMS_SQL – When PARSE means EXECUTE

by Granville Bonyata on March 25, 2012

Dynamic SQL, while not the first tool to pick when solving a problem, can be invaluable. However, it can have some hidden dangers besides allowing SQL injection. One such danger is that sometimes dbms_sql.parse also executes the statement:

It’s in the documentation for 11g: “All statements are parsed immediately. In addition, DDL statements are run immediately when parsed.”

So in my case, I was writing a utility that would read a SQL script and validate each statement using the dbms_sql.parse but I dropped that plan after seeing this feature.

Here’s the proof. Note that the table gets created, and after running the parse the table is gone:

SQL> CREATE TABLE test_parse AS SELECT * FROM all_tables WHERE ROWNUM < 100; Table created. SQL> SELECT COUNT(*) FROM test_parse;

COUNT(*)
----------
99

SQL> DECLARE

v_statement VARCHAR2(100) := 'drop table test_parse';
v_cur NUMBER;

BEGIN
   v_cur := dbms_sql.open_cursor;
   dbms_sql.parse(v_cur, v_statement, DBMS_SQL.NATIVE);
   dbms_sql.close_cursor(v_cur);

END;

PL/SQL procedure successfully completed.

SQL> SELECT COUNT(*) FROM test_parse;

SELECT COUNT(*) FROM test_parse
*
ERROR at line 1:
ORA-00942: table or view does not exist

Previous post:

Next post: