result sets in Oracle stored procedures

Ray Porter

unread,

Mar 4, 2003, 7:43:07 AM3/4/03

to

I posted this question yesterday with no response. I would greatly
appreciate any information anyone can provide. The longer I go without an
answer the more critical this issue becomes.

I tried google for an answer to this and found lots of questions but no
answers. I'm using Delphi 5.02 Enterprise, Oracle 8.1.7, Oracle OLEDB
provider for Oracle 9i.

I've created a stored procedure in Oracle that has an in-out parameter of
type ref cursor. It works fine in sqlplus. In Delphi, if I add a
TADOStoredProc or TADODataSet set to stored procedure to my data module then
look at the parameters, the cursor parameter is identified as ftInterface.
Attempting to set the stored procedure to active results in an error that
says something about invalid or inconsistent parameters. Delphi will not
allow me to change the parameter type to ftCursor. I've also tried creating
the TADOStoredProc object at runtime with the same results.

Any assistance will be greatly appreciated.

--
================================
Ray Porter
Applications Analyst Programmer
Administrative Information Services, UNC-CH
Phone: (919) 966-5878
Fax: (919) 962-5840
Email: ray_p...@unc.edu
Web: http://www.unc.edu/~dragon

Stephan Schneider

unread,

Mar 4, 2003, 11:48:35 AM3/4/03

to

Hallo,

I can give some websites for such problems:

(1) http://www.vbip.com/books/1861003927/chapter_3927_15.asp
(2) http://support.microsoft.com/default.aspx?scid=kb;en-us;Q176086
(3) http://www.actionjackson.com/articles/20000127/example.htm

Regards
Stephan

"Ray Porter" <ray_p...@unc.edu> schrieb im Newsbeitrag
news:3e649f68$1...@newsgroups.borland.com...

Ray Porter

unread,

Mar 4, 2003, 2:01:55 PM3/4/03

to

Thanks, Stephan.

Ray

"Stephan Schneider" <stephan....@web.de> wrote in message
news:3e64d8dd$1...@newsgroups.borland.com...

Ray Porter

unread,

Mar 5, 2003, 8:02:58 AM3/5/03

to

I finally managed to find the solution in an article on the UK Oracle
Developers Journal web site and I thought I'd share it here since this is a
fairly common (and usually unanswered question). This only applies to the
Oracle OLEDB provider for Oracle 8i and up. The method for the MS provider
is documented on the MS site.

First, on the Oracle side, your stored procedure must be defined in a
package which defines a type for ref cursor. I also believe your cursor
parameter must be defined as in-out (pdInputOutput in Delphi). In Delphi,
add a TADOStoredProc or TADODataSet set to stored procedure to your project
(or set the appropriate properties if creating your objects at runtime).
Next, add Extended Properties="PLSQLRSet=1" to your connection string. The
default value is zero and you will only get the error indicating
inconsistent or conflicting parameters if you attempt to retrieve a result
set without resetting plsqlrset to 1. Next, in the parameter list for your
TADOStoredProc, delete the in-out parameter entry for your reference cursor.
Set the value for your input parameters (if any) and Open your
TADOStoredProc. From then on treat it just like any other dataset. My
tests imply that a cursor location of clUserServer and type of
ctOpenForwardOnly give the best performance.

The key and what's not mentioned in most discussions of this issue is
setting plsqlrset to 1 in the extended properties of the connection string.
Since this is a fairly frequently asked question, maybe TeamB or Borland
should consider putting a complete example up on the community site?

Ray

"Ray Porter" <ray_p...@unc.edu> wrote in message
news:3e649f68$1...@newsgroups.borland.com...