开发者

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

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜