Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: PL/SQL 8.1.6 : RETURNING CLAUSE problems

Re: PL/SQL 8.1.6 : RETURNING CLAUSE problems

From: Lachlan Pitts <Lachlan_Pitts_at_softworks.com.au>
Date: 2000/07/20
Message-ID: <39768865@grissom>#1/1

Good point, thanks Oleg.....

I suppose the question is - is this atomic?

Is it possible that another user could have inserted a record into the table between the end of the insert and the complete evaluation of the select?

I assumed that this was the motivation behind the RETURNING clause in the first place.....

Anyone know?

Lachlan Pitts

"Oleg Kuzmin" <kuzmin_at_hotmail.com> wrote in message news:sncm4rc36tt51_at_corp.supernews.com...
> Greetings!
>
> One workaround would be the following:
> FUNCTION domainWriteQuery
> (
> pnValue IN NUMBER,
> pnNewKey OUT NUMBER )
> IS
> BEGIN
> INSERT INTO T_TABLE( K_KEY, V_VALUE )
> VALUES ( SEQ_TABLE.NEXTVAL, pnValue );
>
> SELECT MAX(K_KEY) INTO pnNewKey FROM T_TABLE;
> END domainWriteQuery;
>
> MAX conditions may differ for your system, but the logic is the same.
>
> Oleg
>
> Lachlan Pitts <Lachlan_Pitts_at_softworks.com.au> wrote in message
> news:3976492d_at_grissom...
> > G'day people,
> >
> > New problem for a new day....
> >
> > Any ideas what is wrong with this picture?
> > It compiles but at runtime complains of "SQL Statement not ended";
> >
> > FUNCTION domainWriteQuery
> > (
> > pnValue IN NUMBER,
> > pnNewKey OUT NUMBER )
> > IS
> > BEGIN
> > INSERT INTO T_TABLE( K_KEY, V_VALUE )
> > VALUES ( SEQ_TABLE.NEXTVAL, pnValue )
> > RETURNING K_KEY INTO pnNewKey;
> > END domainWriteQuery;
> >
> > Thanks in advance,
> >
> > Lachlan Pitts
> >
> >
> >
>
>
Received on Thu Jul 20 2000 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US