Re: RETURNING clause oddity

it lookslike a bug.
You should get back the EMPNO of
the row you have just returned, and
clearly you don't.

I've just re-run your test on 8.1.7.0,
and it reproduces. Call it in to Oracle support - it's a simple test case.

--
Jonathan Lewis

Host to The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases
See http://www.jlcomp.demon.co.uk/book_rev.html

Seminars on getting the best out of Oracle
See http://www.jlcomp.demon.co.uk/seminar.html

Screensaver or Lifesaver: http://www.ud.com
Use spare CPU to assist in cancer research.




B R wrote in message ...


>I noticed an oddity with the RETURNING clause. Can anyone explain this

>to me?

>

>I used the RETURNING clause with an insert to get the value of the

>empno column of the new row. However, RETURNING is not providing the

>value of empno in the new row, but instead providing the current value

>of the sequence id_sequence.

>

>This seems to be caused by a trigger incrementing the value of the

>sequence (I created the trigger only to see if incrementing the

>sequence had an effect, and it did!). However, since the RETURNING

>clause is supposed to return what is in empno, I don't understand why

>it is influenced by the trigger incrementing the sequence.

>

>Here are the commands I issued. As you can see, empno in the new row

>is 101, but RETURNING puts 102 (the current value of the sequence) in

>the variable emp_id:

>

>SQL> SELECT id_sequence.NEXTVAL FROM DUAL;

>

>  NEXTVAL

>---------

>      100

>

>VARIABLE emp_id NUMBER

>VARIABLE v_name   VARCHAR2(15)

>INSERT INTO emp (empno, ename)

>VALUES (id_sequence.NEXTVAL, 'TOMMY')

>RETURNING empno, ename INTO :emp_id, :v_name

>

>SQL> SELECT * FROM EMP;

>

>    EMPNO ENAME

>--------- ---------------

>      101 TOMMY

>

>SQL> PRINT emp_id;

>

>   EMP_ID

>---------

>      102

>

>SQL> SELECT id_sequence.CURRVAL FROM DUAL;

>

>  CURRVAL

>---------

>      102

>

>

>This is the useless trigger I put on the table... it seems to be

>affecting what the RETURNING clause returns for empno:

>

>create or replace trigger trigger1

>after insert on emp

>for each row

>DECLARE

>   tempvar number;

>BEGIN

>

>   select id_sequence.nextval into tempvar from dual;

>

>END;

>/