Accessing an Oracle reference cursor using OScript SQL pacakge

I've been trying to access a reference cursor using OScript, but keep getting very un-informative error msgs like 'Error opening cursor.'My code is simple. 1. Allocate the connection2. Create a SqlConnection using Sql.AllocFromConnect// Actual code begins hereInteger status = 0String stmt = "SELECT testPackage.testFunction() from dual"SqlCursor curse = Sql.Declare( sqlConnection, stmt ) status = Sql.Open( curse )// Error gets thrown here// If I try to not open the cursor and go right to Execute // because my oracle function should return an open // cursor, I still thorw an errorstatus = Sql.Execute( curse )// ErrorHas anyone gotten this to work? Does anyone know if you can access reference cursor using OScript or do I need to find an alternative? Any help would be appreciated.Thanks,Greg

Find more posts tagged with

Comments

Donna Nalls

Hello Greg'ry --Is that a stored procedure that you are calling in your sql statement? If so, that might be your problem. Try your sql cursor with a simple select stmt to determine if it is your cursor or the sp.If that is a sp, there is a work around for calling Oracle stored procedures - I found it on the KC a couple of months ago. If this is what the problem is and you don't find the work around let me know and i will track down my code and forward to you.There is a script in $DBWizApi called SetUpCursor - you might try using that to return a valid cursor if you still believe the problem is with your cursor.Hope all is well with you.Donna

Gerald_Bulmash

Hi Donna.My code works great with a simple sql stmt. That was the first thing I got to work and then I added the more stickier parts of the stmt in. No luck. I'll give a look to your SetUpCursor script , although, I'm pretty sure I looked at all the scripts in that package. That is a nice little script to streamline all the cursor setup calls, thanks. I also found a work around on the Oracle side. Normally to call a stored procedure, it isn't a SQL statement but just a call i.e. ora_package.sp_name( input_values ) Of course, I get an error on the LL side. Even using the CAPI.ExecSP. Which is misleading because a lot of people confuse stored procedures with functions. That leads me to my work around. My work around for this was to change my stored procedures to oracle functions so that the syntax to call would be:SELECT ora_package.func_name( input_values ) FROM DUALThe difference in the two is not only syntax, but the function returns, in addition to your expected output, a Boolean (0 or 1) if the function completed successfully. So this would require you to do a little more error handling on the OScript side. The reason I was trying to use the stored procedure instead was because if it threw an oracle error, Livelink would catch it automatically i.e. CAPIERR, just like LL works with Oracle now with simple SQL stmts that error out.I would still like to know if stored procedures will work for future reference/projects. I'll keep looking :/Thanks again, DonnaGreg

Donna Nalls

Hi Greg,Glad you were able to get it to work. Your assessment is absolutely correct - and the work around, of course, is that you have to use a function call to make a call to an Oracle stored procedure from an OScript method ( because of the return value).Hope all is going well with you,Donna