Re: PL/SQL

Create a procedure with a REF CURSOR as in out parameter. First step in the procedure : execute the insert (I would advise you to replace the max construct by a sequence), in Oracle 8(i?) use the returning into clause of the insert statement to capture the custid.
In the second step open the ref cursor to reselect the data. For more info on ref cursors refer to http://osi.oracle.com/~tkyte, and surf to the resultsets topic.

Hth,

Sybrand Bakker, Oracle DBA

SP <spant1_at_aol.com> schreef in berichtnieuws 4dQ%4.819$Qf6.30750_at_nuq-read.news.verio.net...
> 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;
> 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
> 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 CDT