Re: 8i: Procedure Questions -

Currval always refers to the current session , but if you really just want to use the same sequence number across more than one stored procedure why not try the following:

select mySchema.my_tableSEQ.nextval into id_new from dual

INSERT INTO mySchema.myTable
> (id_myTable
> id_myTable2
> ,col_a)
> VALUES
> (id_new

      ,id_myTable2


>         ,'Test'   )

then change your procedure header to be:

CREATE PROCEDURE mySchmema.myTest_INSERT (id_myTable2 NUMBER(10), seq OUT NUMBER ) declare a variable in the calling procedure to receive the seq and then use it in the next procedure.....

HTH RHC alvie wrote:
>
>
> Hi - two questions:
>
> I need to write a procedure that 1.) INSERTS and returns the CURRVAL of
> a SEQUENCE and passes it to another procedure. Here's some SQL that
> I'm trying
>
> CREATE PROCEDURE mySchmema.myTest_INSERT (id_myTable2 NUMBER(10) )
>
> --local var
> id_new NUMBER(10)
>
> IS
> BEGIN
> INSERT INTO mySchema.myTable
> (id_myTable
> id_myTable2
> ,col_a)
> VALUES
> (mySchema.myTable_SEQ.NEXTVAL
> ,id_myTable2
> ,'Test' )
>
>
> --I'd like to assign CURRVAL to the variable id_new so I can
> --call another procedure and pass this value to it.
> SELECT OLIGOBUYER.myTable_SEQ.currval
> INTO id_new
> FROM DUAL
>
>
> --here's where I'd like to pass CURRVAL (id_new) to another procedure
> --that is another insert like above.
>
> END;
> Also, is CURRVAL safe to use, as far not getting someone else's
> CURRVAL. Will it always be for the current connction?
>
> thanks
> as
>
>
> Sent via Deja.com
> http://www.deja.com/

--
Posted via CNET Help.com
http://www.help.com/