One of the lesser used features in an application using Oracle Database installed on a UNIX server stopped working recently throwing the ORA-03113 error. The oerr utility provided the following information:

$ oerr ora 3113

ORA-03113: end-of-file on communication channel

Cause: The connection between Client and Server process was broken.

Action: There was a communication error that requires further investigation.

This meant that one of these most probable things had happened:

  • The network connection was lost
  • The server process had crashed

(Note that ORA-03113 is a generic error and there are over 1000 bugs related to it on Oracle Support!)

First possibility was quickly eliminated as the error wasn’t encountered just once or twice which might happen due to that rare, momentary glitch in network connectivity. But it did happen every single time that particular feature was used.

This meant that something in the function invoked, when the feature in question was run, was causing the server process to die. Running through the code using a test script led to the point of failure.

A simple dynamic SQL query was being executed without event.

-- The following query is being used multiple times later on
v_sqlstr := 'SELECT column1, column2, SUM( column_qty ) AS proposed_qty
             FROM a_global_temporary_table	-- or any table
             WHERE column1 = ' || column1_value ||
             ' GROUP BY column1, column2' ||
             ' ORDER BY MOD( SUM( column_qty ) )';

-- Count the number of rows returned by the "ordered" query above
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM (' || v_sqlstr || ')' INTO v_count;

But why?

Playing around with bits and pieces of the query stored in v_sqlstr led to the finding that when trying to get COUNT(*) using v_sqlstr as the source, including the MOD() in the ORDER BY clause of the query was causing the server process to crash. Weird!

To fix the issue, and because you don’t need an ordered result set to take a count, I removed the ORDER BY clause from the original query and appended it back only after taking the count, as shown below:

-- The following query is being used multiple times
v_sqlstr := 'SELECT column1, column2, SUM( column_qty ) AS proposed_qty
             FROM a_global_temporary_table	-- or any table
             WHERE column1 = ' || column1_value ||
             ' GROUP BY column1, column2';

-- Count the number of rows returned by the query above
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM (' || v_sqlstr || ')' INTO v_count;

-- Append the ORDER BY clause to the original query
v_sqlstr := v_sqlstr || ' ORDER BY MOD( SUM( column_qty ) )';

And things were back on track!

This is just one of the many possible causes and fixes for this error. I hope you find it useful in case you get stuck in a similar situation. Cheers!

4 thoughts on “ORA-03113: end-of-file on communication channel

  1. Hello Abhinav,

    i am getting the same error while executing from procedure from asp.net application but when executing the same procedure from database end it working fine, database that we are using is 12.1.0.2. can you please help me

    “ORA-03113: end-of-file on communication channel
    Process ID: 17334
    Session ID: 336 Serial number: 61485 at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure, Boolean bCheck, Int32 isRecoverable)”

    One more question as you given an example i have set serveroutput on but still not getting the dynamic query that i have stored in procedure during procedure execution while printing the refcursor.

    Thanks.

  2. It would be helpful to have the full error stack trace, Piyush. Most likely it is a connection issue, the problem might simply be that the application cannot communicate with the database. That would be my initial best guess without a stack trace.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.