Board index » delphi » Recordset from Oracle stored procedure

Recordset from Oracle stored procedure

I'm trying to get a recordeset from a oracle stored procedure
using the TADOStoredProc, but without any success.

The stored proc is:

return mytypes.RecordSet is
rs mytypes.RecordSet;
begin
  open rs for
  select *
  from Test;
  return rs;
end;

and mytypes.recordset is a type def of  REF cursor.

I can connect to the database and the procedure shows up in the object
inspector
but if i try to use ADOStoredProc1.Open i get an error.

Is there anyone out there that have experience in this?

Please give me some hints and references to how to solve this.

regards

Goran

 

Re:Recordset from Oracle stored procedure


On Thu, 11 Apr 2002 16:47:54 +0100, "Goran" <gor...@iconaphone.com>
wrote:
I answered this before

I had a similar situation
1. add all the params except the ref cursor
2. Use open instead of ExecProc
3. Use the recordset property to get recordcount a fieldnames
4. Hook datasource to the ADOStoredProc
5. use MDAC 2.5 or better I am running 2.6
6. USE ALL UPPERCASE FOR PACKAGE AND PROC NAME

example code:

    ADOStoredProc1.Close;
    ADOStoredProc1.Parameters.clear;
     dbmDBMemo1.DataField := '';
    ADOStoredProc1.ProcedureName :=
format('PACK_OUTPUT.%s',[UpperCase(cbComboBox1.text)]);
    ADOStoredProc1.Parameters.CreateParameter('Parm1', ftInteger,
pdInput, 10,1234);

    ADOStoredProc1.Parameters[0].value :=
ADOQuery1.fieldbyname('PRJ_PROJECT_ID').value;

    ADOStoredProc1.open;
    dbmDBMemo1.DataField := ADOStoredProc1.recordset.Fields[0].Name;
    lblLabel2.Caption :=  format( 'Recordcount =
%d',[ADOStoredProc1.recordset.recordcount]);

You can do something similar at design time

Quote
>I'm trying to get a recordeset from a oracle stored procedure
>using the TADOStoredProc, but without any success.

>The stored proc is:

>return mytypes.RecordSet is
>rs mytypes.RecordSet;
>begin
>  open rs for
>  select *
>  from Test;
>  return rs;
>end;

>and mytypes.recordset is a type def of  REF cursor.

>I can connect to the database and the procedure shows up in the object
>inspector
>but if i try to use ADOStoredProc1.Open i get an error.

>Is there anyone out there that have experience in this?

>Please give me some hints and references to how to solve this.

>regards

>Goran

Re:Recordset from Oracle stored procedure


You need to add the parameter PLSQLRSet=1 to your connection string.  This
tells the provider to allow recordsets to be passed back from the stored
proc.  However, you can only pass one recordset back per stored proc.
Quote
"Goran" <gor...@iconaphone.com> wrote in message news:3cb5b0f4$1_1@dnews...
> I'm trying to get a recordeset from a oracle stored procedure
> using the TADOStoredProc, but without any success.

> The stored proc is:

> return mytypes.RecordSet is
> rs mytypes.RecordSet;
> begin
>   open rs for
>   select *
>   from Test;
>   return rs;
> end;

> and mytypes.recordset is a type def of  REF cursor.

> I can connect to the database and the procedure shows up in the object
> inspector
> but if i try to use ADOStoredProc1.Open i get an error.

> Is there anyone out there that have experience in this?

> Please give me some hints and references to how to solve this.

> regards

> Goran

Other Threads