Problem binding with OPEN-FOR-USING statement
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 || ' ';
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
精彩评论