19 March 2012

ORA-03113 and Bulk Collect

It can be quite frustrating when you are working on a database package and your session is terminated time and time again and leaves you clueless as too why this is happening. First of all, this is reproducible on an Oracle 10g Enterprise Edition - haven't tried other versions (yet).
SQL> @../utils/rel

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for 64-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
The following simple block of PL/SQL code will cause an ORA-03113: end-of-file on communication channel - as you can see from the copy-paste example below.
SQL> declare
  2     type rec is record
  3        (num number
  4        ,str varchar2(10)
  5        ,dte  date
  6        );
  7     rec_t rec;
  8  begin
  9     select rownum
 10          , 'testing'
 11          , sysdate
 12       bulk collect
 13       into rec_t
 14       from dual
 15     ;
 16  end;
 17  /
ERROR:
ORA-03114: not connected to ORACLE


declare
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 0
Session ID: 25 Serial number: 4602
On lines 2 through 6 a record type is declared, with three attributes. All the attributes have a different datatype, but that's just for show.
Line 7 defines a local variable of the just defined record type.
On line 9 through 15 I do a BULK COLLECT into the just declared local variable.

There is nothing special going on, but you're loosing your connection anyway... What is causing the problem? The block of code is doing a BULK COLLECT (many records possibly) into a Record type which can only hold a single record. Removing line 12 from the code resolves the ORA-03113.

6 comments:

  1. Hi Alex,

    Running your code snippet in both 11.2.0.1 and 11.2.0.2 I get the following PL/SQL error:
    ---
    PLS-00497: cannot mix between single row and multi-row (BULK) in INTO list
    ---
    No ORA-03113!

    ReplyDelete
    Replies
    1. Thanks for checking other versions,.. you beat me to it! ;)
      Good to know that this "feature" is resolved in Oracle 11R2.

      Delete
  2. Hi Alex,

    Looks like it's fixed in 10.2.0.5 too

    "PLS-00497: cannot mix between single row and multi-row (BULK) in INTO list"

    ReplyDelete
    Replies
    1. Thanks Kevan. Good to know that it is fixed.
      Good thing I included the banner to show the version on which I encountered this. :)

      Delete
  3. Hi Alex,

    And you don't have to be clueless, when you go look at the server's alert file in your 10.2.0.4 database. ORA-03113 means your server process serving your client session has died, and the client session cannot reach it anymore. Just prior to dying, the server process has saved the relevant information in its alert file. You'll very likely see the PLS-00497 or something equivalent in there.

    Regards,
    Rob.

    ReplyDelete
    Replies
    1. Good point, Rob. Thanks for this info. I'll see if I can track down the alert file and will post it.

      Delete