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

Oracle Database 10g Enterprise Edition Release - 64bi
PL/SQL Release - Production
CORE      Production
TNS for 64-bit Windows: Version - Production
NLSRTL Version - 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  /
ORA-03114: not connected to ORACLE

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.


  1. Hi Alex,

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

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

  2. Hi Alex,

    Looks like it's fixed in too

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

    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. :)

  3. Hi Alex,

    And you don't have to be clueless, when you go look at the server's alert file in your 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.


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