ORA-24338: statement handle not executed

[straygrey]

Hi,

I have a stored procedure which has a REF cursor output parameter depending on whether a condition is met or not. This REF cursor is then called from my PHP code. The procedure also has one IN parameter.

The scenarios are something like this:

IF condition1 is true
 
/* do something */
 
ELSE
 
/* Open REF CURSOR for return OUT parameter */
 
END IF;

Now when the condition is goes into the else statement and the REF CURSOR is returned my PHP code works fine and I can display all the records from the cursor. But the problem is that when the IF condition evaluates to true and no REF CURSOR is returned then I get the error below:

OraOLEDB error '80004005'
ORA-24338: statement handle not executed


Can someone suggest how to workaround this issue. I cannot return a REF cursor when the IF condition is true.

Thanks

[simply_dba]

Well.... in case when condition 1 is true, you are not opening any ref cursor,but you are calling it from your PHP code.This is not allowed.

As a workaround, can your PHP code predict the outcome of condition1 ? If yes, then call the ref cursor based on the outcome of condition 1.

[straygrey]

hi simply_dba thanks for your reply. No actually i can't predict in my code whether a REF cursor would be returned or not. However I noticed one thing that the error actually occurs when I try to execute the result set in php. So it doesn't seem to be a problem with oracle not returning a REF cursor, but I might be wrong.

I am considering modifying th procedure to open a dummy REF cursor so that one Cursor is always returned. Let me know your thoughts on this.

[simply_dba]

Well......
try it

[straygrey]

hi i finally got the code to work. So if someone else faces the same problem the issue is very simple, if you are not opening a cursor inside the stored procedure then don't try to execute it in your calling application code.

What I did was, I used an additional return variable in the OUT parameter list of my stored procedure. I used a variable v_cursor_open which returns 1/0, depending on whethermy REF cursor opening condition was met or not.

Now in my applicatopn code after executing the stored procedure I checked the value of v_cursor_open. When it was 1, then only I am executing the returned cursor variable.

[rajavu]

You can also try the DUMMY ref cursor as suggested by yourself.

IF condition1 is true
 
/*  Open Dummy REF CURSOR for return OUT parameter */
 
ELSE
 
/* Open REF CURSOR for return OUT parameter */
 
END IF;

Dummy REF CURSOR could be like

SELECT NULL , NULL , NULL,....from dual;

(Same number of null list as in actual ref cursor)

[simply_dba]

As I said before .........