Problem binding with OPEN-FOR-USING statement

Question

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 || ' ';

Answer 1

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