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