receiving recordset from oracle stored procedure

[Lorand]

sorry, something went wrong

Message Edited by Lorand on 11-29-2007 04:31 PM

[KoosKoets]

Hi Lorand,

you said : "sorry, something went wrong" .. does this mean that it didn't work?

I am not a labview programmer, so one of the programmers is working on a case i have build in the database. He also made the suggestion of setting this parameter to true.

The major problem of returning a selection state in string format, is the allocation of space used by the string. So you have to allocate a major block of bytes, because you don't know how many bytes you will receive.

At the moment I have made a case in which a procedure returns TYPE ... %ROWTYPE and still we wanna try the TYPE ... REF CURSOR

I will keep this forumn up to date until there is a sollution to build on ..

[Lorand]

Hi KoosKoets,

might be a bit confusing. I was talking about the set of postings. One posting failed. So I wrote it again. I don't know why now there are multiple entries.

Setting the PLSQLRSet-Attribute did work!!!

###...and still we wanna try the TYPE ... REF CURSOR ###
=> It works also with REF CURSOR

I am now testing the effect on other procedures returning scalar types.

Lorand

[KoosKoets]

Ha so it works also for a REF CURSOR ... hmmm now how is this implemented in LabView ? could you please point me out?

Best regards

[Lorand]

Just set up a parameterized query, described in the toolkit-documentation.

OraOLEDB documentation:
Because there is no predefined data type for REF CURSOR in the OLE DB specification, the consumer mus not bind this parameter.
=> That means: No Questionmark for the OUT-Parameter (REF CURSOR returnvalue)

Hope this helps,

Lorand

[KoosKoets]

Lorand, this works. I got the news from the main developer. He achieved to retrieve the reference cursor and to use it as a recordset.

Superb ...

There is another question bothering me. What will happen with the cursor as soon as the cursor is retrieved by the client. Will it remain open or will it implicitly be closed by Oracle.

Best regards .. KoosKoets

[Lorand]

It seems that the cursor doesn't have to be closed, but I'm not sure. The exception "cursor already opened" will not be raised, when you open a already opened ref cursor as it does when you repeatedly open a static cursor.

I have started an oracle-srq about this...

Lorand

[KoosKoets]

Lorand,

We got it all working now .. but it seems that it can't handle overloaded procedures and/or functions. So for procedures with different argument types but same functionality, the names of these procedures must differ.

[Lorand]

Thanks,

I will keep this in mind.

...how do I rate your reply?

Lorand

[KoosKoets]

Lorand, sorry i didn't reply any sooner; i don't know either how to rate replies .. so it is okay