receiving recordset from oracle stored procedure

[KoosKoets]

Hi all, I am currently working on the newest release of labview and one of the database developers created a stored procedure on which i can retrieve data. Now the data is passed as a recordset; so the OUT parameter of the stored procedure is of a type define REF CURSOR. I have no trouble passing IN primitive datatypes like : string, longs or dates and I have no trouble in retrieving values from OUT parameters which are also of the primitive datatypes. Programmatically using .NET c# or vb is not a trouble at all, so retrieving the OUT parameter of type REF CURSOR isn't a trouble; but how can i accomplish this by using LabVIEW;

Somebody can point me out ..

Hope so ... thanx ...

[tbd]

Hi KoosKoets,

      I've written and interfaced with TSQL stored procedures, but doubt that qualifies me to help you with this Oracle interface, still...

Are you using any Automation (AX) tools, if so, which? (DAO, ADODB)

Cheers!

[KvZ]

Hi,

If you are using the LaVIEW Database toolkit see:

How Do I Set Input Parameters and Get Output Parameters from an SQL Stored Procedure with the Databa...

Hope this Helps

Thanks

Karsten

[KoosKoets]

I am using the Database connectivity toolkit from LabView and I knew about te website (thanx). This does work only when I use standard datatypes like: strings, long, integers and dates. But now the procedure returns a recordset, which is a type defined cursor of datatype REF CURSOR. With normal SQL code it is easy to retrieve and to proces the data; but the procedure has a lot of calculations and the return value is one of a recordset which is an object or a reference to an object. I could use the datatype Binary which is a datatype shown in in the labview toolkit. I am usign ADODB to connect with the database. The connection string I am passing is: Provider=msdaora;Data Source=MyOracleDB;User Id=myUsername;Password=myPassword;

If you would like to know more please let me know ...

Hope it helps to help me ..

Thanx in advance.

[Lorand]

Hi,

have you already found a solution?

I have the same problem. But in my case it doesn't have to be a type REF CURSOR, whereas I have not worked with cusor-variables yet.

Is there any other chance to recieve a complete recordset (TYPE...RECORD) as INOUT-parameter from a stored procedure?

I'm also the database-admin, so I can build the appropriate db-procedure.

Lorand

[KoosKoets]

hi lorand, i haven't got a solution yet; even the support from national instruments couldn't provide met with a satisfied answer. Their approach was to return a selection query of type string. Which of course is also a try; but i guess this will produce some overhead when you will be having a string with more than 1024 characters in it ...

so what i want to do is first creating a function which return a datatype of TABLE. So I can set up a view like: SELECT * FROM TABLE(FOO) which calls the function FOO and returns a collection of datatype TABLE.

labview then can select the view wich creates a cursor on the client side.

I don't know if it'll work with a procedure; i have noticed that the database connection toolset only can handle Binary and the Primitive datatypes like String and Long .. So i don't know what will happen when it get a TABLE as datatype.

Hope this helps if you got any other suggestions please reply ... as soon as I got some more answers I will let you know by posting in this forum.

Cheers.

[KvZ]

It's correct that the database toolkit maps all datatypes that it does not know to string. I'm no database expert but if the string approach does not work for you could you not setup the procedure to return the cursor record as a Binary data type and type cast it in LabVIEW? Or depending on the number of elements in the cursor record to individual outputs and in LabVIEW combine them into a Single Cluster?

Regards,

Karsten - NI

[Lorand]

I've got it.

I'm connected to the database via a UDL under Windows2000. To recieve Recordsets from stored procedures you must do the following:

1. open the UDL-mask (doubleclick)

2. go to the last tabpage (in german version "Alle")

3. select "Extended Properties" for edit

4. insert the value "plsqlrset=1"

OR insert in the UDL-file "Extended Properties="plsqlrset=1"" seperated by a semicolon.

The thing is:

You must enable the return of a rowset from PL/SQL stored procedures.

This can be done in two ways:

- as parameter in the connection-string (valid for the complete session) => as above

- by setting the ADODB Command property PLSQLRSet to TRUE. This seems not to be supported in LabView.

Use the "Oracle Provider for OLE DB Developer's Guide" for more information. There is an VB-example on page 2-13

Lorand

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

[Lorand]

sorry, something went wrong

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

[Lorand]

I've got it.

I'm connected to the database via a UDL under Windows2000. To recieve Recordsets from stored procedures you must do the following:

1. open the UDL-mask (doubleclick)

2. go to the last tabpage (in german version "Alle")

3. select "Extended Properties" for edit

4. insert the value "plsqlrset=1"

OR insert in the UDL-file "Extended Properties="plsqlrset=1"" seperated by a semicolon.

The thing is:

You must enable the return of a rowset from PL/SQL stored procedures.

This can be done in two ways:

- as parameter in the connection-string (valid for the complete session) => as above

  =>

- by setting the ADODB Command property PLSQLRSet to TRUE. This seems not to be supported in LabView.

Use the "Oracle Provider for OLE DB Developer's Guide" for more information. There is an VB-example on page 2-13