Re: RETURNING clause oddity

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 5 Aug 2001 08:33:42 -0700
Message-ID: <9kjp0m0gu0@drn.newsguy.com>

In article <997020191.19571.0.nnrp-13.9e984b29_at_news.demon.co.uk>, "Jonathan says...
>
>
>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.
>

I just ran:

ops$tkyte_at_ORA817.US.ORACLE.COM> drop table emp; Table dropped.

ops$tkyte_at_ORA817.US.ORACLE.COM> create table emp as select empno, ename from scott.emp where 1=0;
Table created.

ops$tkyte_at_ORA817.US.ORACLE.COM> drop sequence id_sequence; Sequence dropped.

ops$tkyte_at_ORA817.US.ORACLE.COM> create sequence id_sequence start with 100; Sequence created.

ops$tkyte_at_ORA817.US.ORACLE.COM> create or replace trigger trigger1   2 after insert on emp
  3 for each row
  4 DECLARE
  5 tempvar number;
  6 BEGIN
  7
  8 select id_sequence.nextval into tempvar from dual;   9
10 END;
11 /
Trigger created.

ops$tkyte_at_ORA817.US.ORACLE.COM> sELECT id_sequence.NEXTVAL FROM DUAL;

NEXTVAL

100

ops$tkyte_at_ORA817.US.ORACLE.COM> VARIABLE emp_id NUMBER
ops$tkyte_at_ORA817.US.ORACLE.COM> VARIABLE v_name   VARCHAR2(15)
ops$tkyte_at_ORA817.US.ORACLE.COM> INSERT INTO emp (empno, ename)

2 VALUES (id_sequence.NEXTVAL, 'TOMMY') 3 RETURNING empno, ename INTO :emp_id, :v_name;

1 row created.

ops$tkyte_at_ORA817.US.ORACLE.COM> SELECT * FROM EMP;

EMPNO ENAME
---------- ----------

101 TOMMY ops$tkyte_at_ORA817.US.ORACLE.COM> PRINT emp_id;

EMP_ID

101

ops$tkyte_at_ORA817.US.ORACLE.COM> SELECT id_sequence.CURRVAL FROM DUAL;

CURRVAL

102

ops$tkyte_at_ORA817.US.ORACLE.COM> select * from v$version;

BANNER

Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production PL/SQL Release 8.1.7.0.0 - Production
CORE 8.1.7.0.0 Production
TNS for Solaris: Version 8.1.7.0.0 - Production NLSRTL Version 3.4.1.0.0 - Production

I ran this in 803, 806, 815, 817, 901 and got the same results in all...

Can one of you guys post the *exact* script you ran (cut and paste like above is mot useful) and if questguy could provide the PLATFORM/VERSION used, that would be helpful as well.....

>
>--
>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;
>>/
>
>

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp

Received on Sun Aug 05 2001 - 10:33:42 CDT