Re: Oracle equival to SQL Server @@IDENTITY

From: Al Reid <areidjr_at_reidhyphenhome.com>
Date: Wed, 1 Oct 2003 11:36:53 -0400
Message-ID: <vnlsr4rt14da4b@corp.supernews.com>

I learned something new today. I was not aware of the 'RETURNING' clause. Using it would change my previously posted Procedure definition to the following:

CREATE PROCEDURE INS_SG_MENUE (oReturn OUT VARCHAR2, inLevelName IN VARCHAR2, inErstelltAm IN VARCHAR2,

oIDENTITY OUT NUMBER)

   AS
   BEGIN
      oReturn := 0;

      INSERT INTO SG_MENUE
                              (ID, LevelName, ErstelltAm)
              VALUES (SEQ_SG_MENUE_ID.NEXTVAL, inLevelName, inErstelltAm)
      RETURNING ID INTO oIDENTITY;

   EXCEPTION
      WHEN OTHERS THEN
      oReturn := SQLERRM;

   END; Is this correct?

Al

"Sybrand Bakker" <gooiditweg_at_sybrandb.nospam.demon.nl> wrote in message news:2mjlnv8ckd9cce6pntee65huqu8pqe2nu0_at_4ax.com...
> On Wed, 1 Oct 2003 08:44:10 -0400, "Sean Chang" <sean_at_cnfei.com>
> wrote:
>
> >Your statement only accomplishes the INSERT part, the OP want the newest ID#
> >printed out also: select @@Identity, which is similar to select
> >sequence.nextval from dual;
>
> The OP should use the returning clause of the insert statement,
> provided he is on 8i
> >
> >Of course, Brian's ( next post) solution will work too, but I don't see
> >performance difference
> >between : select sequence.nextval from dual VS select sequence.curval from
> >dual, please
> >enlighten me why my solution slows down performace.
> >
>
> extra soft-parse, depending on context (whether called from PL/SQL or
> standalone) also an extra context-switch. When in SQL*plus also an
> extra sqlnet roundtrip
>
>
> >Quota from OP: "But i dont know how can i insert and select in one statement
> >!?!?!??!".
>
>
> By using the returning clause of the insert statement
>
>
> --
> Sybrand Bakker, Senior Oracle DBA
Received on Wed Oct 01 2003 - 10:36:53 CDT