Re: Returning stored procedure results to query tool
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