Changes between Initial Version and Version 1 of Ticket #36189, comment 5


Ignore:
Timestamp:
Feb 18, 2025, 10:29:58 AM (8 months ago)
Author:
Yeongbae Jeon

Legend:

Unmodified
Added
Removed
Modified

  • Ticket #36189, comment 5

    initial v1  
    1414
    1515For example, if the currval of the sequence is 1, but the user manually assigns 10 as the primary key when creating the object, then use_returning_into: False will execute the last_insert_id function, which will return the incorrect value of 1.
     16
     17
     18---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
     19
     20(Edit 1, 02/19/2025)
     21
     22I just tested creating a table, sequence, and trigger in SQL*Plus and then performed inserts in two separate sessions. It seems that the sequence is tied to each transaction. Below is the example I executed. and the oracle version is 23.5.0.24.07
     23
     24**Code to Run Before Insert** 
     25
     26{{{
     27CREATE SEQUENCE my_sequence
     28START WITH 1
     29INCREMENT BY 1
     30NOCACHE
     31NOCYCLE;
     32
     33CREATE TABLE my_table (
     34    id NUMBER PRIMARY KEY,
     35    name VARCHAR2(100)
     36);
     37
     38CREATE OR REPLACE TRIGGER my_table_trigger
     39BEFORE INSERT ON my_table
     40FOR EACH ROW
     41WHEN (NEW.id IS NULL)
     42BEGIN
     43    :NEW.id := my_sequence.NEXTVAL;
     44END;
     45/
     46}}}
     47
     48
     49
     50**Test Steps** 
     51
     521. **Open the first terminal tab running SQL*Plus** 
     53{{{
     54   INSERT INTO my_table (name) VALUES ('Alice');
     55}}}
     56
     572. **Switch to the second terminal tab running SQL*Plus** 
     58{{{
     59   INSERT INTO my_table (name) VALUES ('Alice');
     60}}}
     61
     623. **Back in the first tab**
     63{{{
     64   SQL> INSERT INTO my_table (name) VALUES ('Bob');
     65
     66   1 row created.
     67
     68   SQL> SELECT my_sequence.CURRVAL FROM DUAL;
     69
     70      CURRVAL
     71   ----------
     72         1
     73}}}
     74
     754. **Then, in the second tab** 
     76{{{
     77   SQL> INSERT INTO my_table (name) VALUES ('Bob');
     78
     79   1 row created.
     80
     81   SQL> SELECT my_sequence.CURRVAL FROM DUAL;
     82
     83      CURRVAL
     84   ----------
     85         2
     86}}}
     87
     88
     89Another case that shows sequence is tied to transactions:
     90
     91
     92**Code to Run Before Insert** 
     93
     94{{{
     95CREATE SEQUENCE my_sequence
     96START WITH 1
     97INCREMENT BY 1
     98NOCACHE
     99NOCYCLE;
     100
     101CREATE TABLE my_table (
     102    id NUMBER PRIMARY KEY,
     103    name VARCHAR2(100)
     104);
     105
     106CREATE OR REPLACE TRIGGER my_table_trigger
     107BEFORE INSERT ON my_table
     108FOR EACH ROW
     109WHEN (NEW.id IS NULL)
     110BEGIN
     111    :NEW.id := my_sequence.NEXTVAL;
     112END;
     113/
     114}}}
     115
     116
     117
     118**Test Steps** 
     119
     1201. **Open the first terminal tab running SQL*Plus** 
     121{{{
     122   INSERT INTO my_table (name) VALUES ('Alice');
     123}}}
     124
     1252. **Switch to the second terminal tab running SQL*Plus** 
     126{{{
     127   INSERT INTO my_table (name) VALUES ('Alice');
     128}}}
     129
     1303. **Back in the first tab**
     131{{{
     132   SQL> INSERT INTO my_table (name) VALUES ('Bob');
     133
     134   1 row created.
     135
     136   SQL> SELECT my_sequence.CURRVAL FROM DUAL;
     137
     138      CURRVAL
     139   ----------
     140         1
     141}}}
     142
     1434. **Then, in the second tab, but this time do not insert**
     144{{{
     145  SQL> select my_sequence.currval;
     146  select my_sequence.currval
     147         *
     148  ERROR at line 1:
     149  ORA-08002: Sequence MY_SEQUENCE.CURRVAL is not yet defined in this session.
     150  Help: https://docs.oracle.com/error-help/db/ora-08002/
     151}}}
Back to Top