1

I have a cursor in an ORACLE function that I am dynamically generating using the arguments of the function.

I define the text of the cursor and in certain cases I include the following statement:

      sql_stmt := sql_stmt || 'AND MOD ( LOG_ID, :logsetmax ) = :logset ';

I open the cursor and pass arguments in using the ORACLE OPEN-FOR-USING statement (e.g. with :logsetmax set to 3 and :logset set to 0).

When my program includes the constraint above my program hangs and I get ORA-03113 and ORA-03114 errors.

Is ORACLE preventing me from binding the cursor argument of :logsetmax (passed in with USING) into the MOD function of my cursor declaration and causing this unpredictable behaviour ?

I suspect my error is due to a divide by zero by a non-bound argument that is causing the ORACLE connection to time out ?

Any suggestions would be greatly appreciated.

UPDATE

As a practical solution I revised my SQL to remove these contraints from the cursor bind and hard coded them into the SQL as follows:

            sql_stmt := sql_stmt || 'AND MOD ( LOG_ID, ' || p_logsetmax || ' ) = ' || p_logset || ' ';
OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
David
  • 14,047
  • 24
  • 80
  • 101
  • 1
    Have you tried just to run the query without cursors and dynamic SQL manually. Does it cause same kind of problem, – Calmar Feb 01 '10 at 10:04
  • If I hard code the values into the problem constraint it works OK, the problem seems to be with the binding values. – David Feb 01 '10 at 10:35

1 Answers1

2

The ORA-03113: end-of-file on communication channel error is a generic error. The ORA-03113 error when connecting suggests that the connection was established, but lost later, like a timeout.

There are many possible causes for the ORA-03113 error.

The ORA-3113 could be signaled for any of these scenarios:

  • Machine crashed
  • Server process was killed at O/S level
  • Network problems
  • Oracle internal errors / aborts on the server
  • Client incorrectly handling multiple connections
  • etc.. lot of possible causes

Action: There was a communication error that requires further investigation. First, check for network problems and review the SQL*Net setup. Also, look in the alert.log file for any errors. Finally, test to see whether the server process is dead and whether a trace file was generated at failure time.

source: ora-code.com

R van Rijn
  • 909
  • 10
  • 16