Board index » delphi » result sets in Oracle stored procedures

result sets in Oracle stored procedures

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_por...@unc.edu
Web: http://www.unc.edu/~dragon

 

Re:result sets in Oracle stored procedures


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_por...@unc.edu> schrieb im Newsbeitrag
news:3e649f68$1@newsgroups.borland.com...

Quote
> 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_por...@unc.edu
> Web: http://www.unc.edu/~dragon

Re:result sets in Oracle stored procedures


Thanks, Stephan.

Ray

Quote
"Stephan Schneider" <stephan.schnei...@web.de> wrote in message

news:3e64d8dd$1@newsgroups.borland.com...
Quote
> 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_por...@unc.edu> schrieb im Newsbeitrag
> news:3e649f68$1@newsgroups.borland.com...
> > 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_por...@unc.edu
> > Web: http://www.unc.edu/~dragon

Re:result sets in Oracle stored procedures


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

Quote
"Ray Porter" <ray_por...@unc.edu> wrote in message

news:3e649f68$1@newsgroups.borland.com...
Quote
> 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_por...@unc.edu
> Web: http://www.unc.edu/~dragon

Other Threads