[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