Re: Returning stored procedure results to query tool

From: Andre N. Gritchouk <an_at_percombank.kiev.ua>
Date: Wed, 25 May 1994 07:12:54 GMT
Message-ID: <AAsfluja64_at_percombank.kiev.ua>


Steve Stansfield wrote:

>What I'd really like to do is get Oracle to return results of a stored
>procedure like Sybase does. Does anyone have any tricks or suggestions.

I'm using Oracle7 and Paradox 4.5(DOS) as frontend. I've written a package for getting results from Oracle stored procedure to Paradox.

The package is

/* package Paradox - Oracle intercommunication */

create or replace package POI as

 retstr# varchar2(200);

 procedure pdoxret(errcode number default 0,

                   m1  varchar2 default NULL) ;

 procedure ReadRet;

end POI;

/
/* package Paradox - Oracle intercommunication */

create or replace package body POI as
procedure pdoxret(errcode number default 0, m1 varchar2 default NULL ) as
 begin

    retstr#:='###'||errcode||'#'||m1||'###';  end pdoxret;

 procedure ReadRet
 as
 begin

    raise_application_error(-20222,retstr#);  end ReadRet;

end POI;

I finish all my stored function with calling POI.pdoxret procedure.

e.g.
 function f1(Name# in varchar2,Type# in char) return varchar2  is
   rt integer;
 begin

  rt:=1;

   ...
   ...
  poi.pdoxret(0,'#'||rt);

  return (0||'#'||rt);

 end f1;

 After calling function f1 from Paradox I call Readret procedure and  I can read results of f1 using Paradox function SQLErrorMessage().

 It is possible also:
  function f1(Name# in varchar2,Type# in char) return varchar2  is
   rt integer;
 begin

  rt:=1;
   ...
   ...
 raise_application_error(-20222,0||'#'||rt);  end f1;

 But in this case all changes made by f1 will be lost because  raise_application_error makes rollback.

 I'm sorry for my bad English.

 With best regards,

  • Andre Gritchouk
  • phone 7-(044)-291-8672
  • fax 7-(044)-291-8651
  • an%percombank.kiev.ua_at_relay.UA.NET
Received on Wed May 25 1994 - 09:12:54 CEST

Original text of this message