How to call a procedure that returns a "RecordSet"

[Andrew Luit]

There is a procedure runing in the server in Oracle. The procedure returns a PL/SQL table (like a 2 dimension array). My question is:

How do I call this procedure in the client by ADO.
Thanks in advance.

[Chris Eastwood]

You're not going to like this ...

I faced the same problem a couple of years ago - it turns out that Oracle can't (directly) return a RecordSet from a stored procedure (unless you do some low-level OCI stuff and build the recordset yourself using C/C++).

You can however use the Oracle Objects for OLE (OO4O) that comes with Oracle to achieve this - although it's a clunky ActiveX EXE program.

There's also a work-around on the MSDN (http://msdn.microsoft.com) somewhere, that shows how to 'fudge' a returned recordset by having your StoreProcedure return an 'array' (table data type in oracle? - can't remember) - it's a whole lot of code though.

However, while looking around on the Oracle website the other day, I found a beta release of an OLEDB driver so it might now be possible (hopefully! I had to code 100's and 100's of lines to build my own recordset from OO4O and the performance was dreadful). I haven't tried the OLEDB driver yet, but it comes with a huge manual (pdf format) and it looks quite good.




Chris Eastwood

CodeGuru - the website for developers
http://codeguru.developer.com/vb

[Andrew Luit]

Thanks, I will try to check out the document for OLEDB.