PL/SQL

From: SP <spant1_at_aol.com>
Date: 2000/06/08
Message-ID: <4dQ%4.819$Qf6.30750_at_nuq-read.news.verio.net>#1/1


[Quoted] I'm trying to do the following function call: select cusid, cn, ref_ from shopper where custid=(select myfunction(custid) from shopper);

The function is:

create function myfunction(cn in shopper.cn%type, ref_ in shopper.ref_%type) return shopper%rowtype
as
begin
newcustID shopper.custid%custid;
[Quoted] select max(custid)+1 into newcustid from shopper; insert into shopper(custid,cn,ref_)
values (newcustid,cn,ref_);
return newcustid;
end myfunction;

The function compiles but the execution of the select statement gives the following message:
cannot use DML statement in a query.

Shopper table has other rows also but I just want to select the three columns. Is there a way to rewrite it. It doesn't matter whether it is a procedure(I can do the exec statement) or a function, I just want to do the [Quoted] insert in the table and return the inserted fields to a EXEC statement or through a function call. The format of the returned row should be of the form:

custid      cn      ref
------      ---     ---
123        John     Tom

Thanks,
SP Received on Thu Jun 08 2000 - 00:00:00 CEST

Original text of this message