Why does dbms_sql.parse containing incorrect PL/SQL block with bind variables succeed unexpectedly?

Question

The PL/SQL block below fails as expected:

SQL> declare
  2    i int;
  3  begin
  4    i := dbms_sql.open_cursor;
  5    dbms_sql.parse(i,'begin dontexist; dbms_output.put(''a''); end;',1);
  6    dbms_sql.close_cursor(i);
  7  end;
  8  /
declare
*
FOUT in regel 1:
.ORA-06550: Regel 1, kolom 7:
PLS-00201: identifier 'DONTEXIST' must be declared.
ORA-06550: Regel 1, kolom 7:
PL/SQL: Statement ignored.
ORA-06512: in "SYS.DBMS_SQL", regel 1120
ORA-06512: in regel 5

Because I don't have a procedure called DONTEXIST. My question is then why does this next PL/SQL block complete successfully?

SQL> declare
  2    i int;
  3  begin
  4    i := dbms_sql.open_cursor;
  5    dbms_sql.parse(i,'begin dontexist; dbms_output.put(:a); end;',1);
  6    dbms_sql.close_cursor(i);
  7  end;
  8  /

PL/SQL-procedure is geslaagd.

The difference is the use of the bind variable instead of a constant, but I'd like to know why this makes a difference.

This is Oracle 12.1.0.2

Does `dbms_sql.parse(i,'begin dbms_output.put(''a''); end;',1);` succeeds? — J. Chomel, Aug 22 '17 at 07:43
Same with Oracle 11g. Maybe a difference between "fail at compile time" and "fail at execution time" for bind variables; in second case, no execution time, or is there? — J. Chomel, Aug 22 '17 at 08:01

score 4 · Accepted Answer · answered Aug 23 '17 at 01:34

4

Looks like the parse is only syntactic for anon blocks with binds, and the full semantic check is deferred until execution.

Still, that's not a behaviour we want so Bug 26669757 raised.

answered Aug 23 '17 at 01:34

Connor McDonald

10,418
1
11
16

Why does dbms_sql.parse containing incorrect PL/SQL block with bind variables succeed unexpectedly?

1 Answers1