[Zope] Re: Calling a stored function with the Oracle DA
Nemeth Miklos
nemeth@iqsoft.hu
Fri, 14 Jan 2000 17:30:16 +0100
Jim Cain wrote:
> On Wed, 12 Jan 2000, Nemeth Miklos wrote:
>
> > > From: Jim Cain
> > >
> > >
> > > All,
> > >
> > > The "begin procedure (x, y, ..); end;" syntax works for procedures okay,
> > > but how do I call a function? The typical quick & dirty way in sqlplus is
> > >
> > > variable x number -- or whatever
> > > begin
> > > :x := function (a, b, ...);
> > > end;
> > >
> > > This function actually does updating, so I can't use the pragmas to make
> > > it acceptable in a select.
> > >
> > >
> > "begin :x := function(:a,:b); end;"
> >
> > where :x, :a and :b are SQL bind variables. This is OCI's syntax. I
> > suppose DCOracle accepts this syntax.
> > You may use literals instead of :a or :b if they are IN arguments.
>
> Yes I tried that:
>
> begin
> :new_id := contact_maint.insert_person
> (
> <dtml-sqlvar first_name type=string>,
> <dtml-sqlvar middle_name type=nb optional>,
> <dtml-sqlvar maiden_name type=nb optional>,
> <dtml-sqlvar last_name type=string>
> );
> end;
>
> But I get an OCI error: "ORA-01008: not all variables bound". How do I
> declare new_id?
I think Z SQL methods do not support OUT and IN/OUT arguments of Oracle stored
procedures.
A return value of a function is simply an OUT argument.
You should use
(1) external Python methods where you use DCOracle directly.
(2) some circumventing techniques; for example:
declare
new_id number;
begin
new_id := contact_maint.insert_person
(
<dtml-sqlvar first_name type=string>,
<dtml-sqlvar middle_name type=nb optional>,
<dtml-sqlvar maiden_name type=nb optional>,
<dtml-sqlvar last_name type=string>
);
insert into procresults(procName,callId,v1)
values('contact_maint.insert_person',<dtml-sqlvar CALLID type=nb>, new_id);
end;
where CALLID is an argument to the SQL method. CALLID must have a unique id (eg.
clockticks).
And in another SQL method:
select v1 from procresults where procName = 'contact_maint.insert_person' and
callId = <dtml-sqlvar CALLID type=nb>
This technique is surely very clumsy for obtaining the return value of a
function.
However it is often used technique to retrieve the result of a fleshy procedure,
storing data in (memory resident) temporary tables.
NM