BUG with Bind Variables in Labeled Block in 10gR2

March 31, 2009

Yesterday in my course I ran into a strange BUG in Oracle Express (XE) on WIN32 and Linux 32 bit, in fact one of my students,  Ralf Spiwoks, found it:

I have tried to run a  top level anonymous block in which assigned a value to a BIND variable and got kicked out of my session with an ORA-03113.

SQL> VAR lname VARCHAR2(4)

SQL> <<my_lable>>
 BEGIN
    :lname:=’LUTZ’;
   end;
/

ERROR:
ORA-03114: not connected to ORACLE
 <<my_lable>>
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel

THIS IS A BUG which exists in 10gR2.

Only top level blocks are affected by this BUG.
 After I wrapped the block into another block it worked.

Here is the workaround:

BEGIN
  <<my_lable>>
 BEGIN
 :lname:=’LUTZ’;
 END;
END;
/

PL/SQL procedure successfully completed.

 I have tested only WIN 32 and LINUX32 =>  XE and Enterprise Edition on Win32.  It has been fixed in the meentime, but I am not sure with which patch.
In EE 11gR1  it works.