Board index » delphi » Returning a result set from an Oracle stored procedure

Returning a result set from an Oracle stored procedure

Hi

I am unable to get Oracle 7.3.3 to return a result set to a stored procedure
call from the Delphi 3, stored procedure component.  When calling a SP that
does a delete or update in Oracle, it works fine, but have had no joy trying
to return the result set.

Would be appreciative of any advice in solving this very frustrating problem

Thanks a lot

 

Re:Returning a result set from an Oracle stored procedure


The following works fine with 7.2-7.3 and possibly 8x
(finds numbers not used in TABLE_A in a series defined by min X and max Y):

CREATE package PKG as
  type VALTYPE is ref cursor return TABLE_B%rowtype;
end PKG;
/
CREATE procedure FREE_VALS (X number, Y number, RES in out PKG.VALTYPE)
is
   v number;
begin
   delete from TABLE_B;
   v:= X;
   while v < Y loop
       insert into TABLE_B values(v);
       v:= v + 1;
   end loop;
   open RES for select * from TABLE_B where VAL not in (select VAL from TABLE_A)
order by VAL;
end FREE_VALS;
/

Just remember to set the StoredProc's paramtype to CURSOR for RES and do
    StoredProc1.Open;

Good luck!!

Quote
Terence Gilbert wrote:
> Hi

> I am unable to get Oracle 7.3.3 to return a result set to a stored procedure
> call from the Delphi 3, stored procedure component.  When calling a SP that
> does a delete or update in Oracle, it works fine, but have had no joy trying
> to return the result set.

> Would be appreciative of any advice in solving this very frustrating problem

> Thanks a lot

Other Threads