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.
Applications Analyst Programmer
Administrative Information Services, UNC-CH
Phone: (919) 966-5878
Fax: (919) 962-5840
I can give some websites for such problems:
"Ray Porter" <ray_p...@unc.edu> schrieb im Newsbeitrag
"Stephan Schneider" <stephan....@web.de> wrote in message
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 Porter" <ray_p...@unc.edu> wrote in message