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

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!