Oracle – Sync Sequence Nextval Using CONNECT and Side Effects

oraclesequence

DB

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit

Action

After a data migration – from an old table to a new table – we created a new sequence and executed this command to sync it's next value to be equal to table maximum id.

SQL

-- SYNC SEQUENCE WITH TABLE
SELECT level, {SEQUENCE_NAME}.NEXTVAL
FROM dual
CONNECT BY level <= (
 SELECT MAX(ID)
 FROM {TABLE}
);

Question

Is there any side effect on calling this SQL – since the keyword CONNECT is used to make connections to DB ?
Is there a more efficient way to do such task?

Useful Links

Oracle – CONNECT Keywork – Reference

Best Answer

CONNECT in this context has nothing to do with database connections.

hierarchical_query_clause ::=

You can change the increment of a sequence to set it to a required value. Example:

SQL> create sequence s1;

Sequence created.

SQL> select s1.nextval from dual;

   NEXTVAL
----------
         1

SQL> alter sequence s1 increment by 100;

Sequence altered.

SQL> select s1.nextval from dual;

   NEXTVAL
----------
       101

SQL> alter sequence s1 increment by 1;

Sequence altered.

SQL> select s1.nextval from dual;

   NEXTVAL
----------
       102

You can also use the undocumented RESTART clause, but that does not work in 11.2:

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Apr 30 12:44:26 2019
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> create sequence s1;

Sequence created.

SQL> select s1.nextval from dual;

   NEXTVAL
----------
         1

SQL> alter sequence s1 restart start with 101;

Sequence altered.

SQL> select s1.nextval from dual;

   NEXTVAL
----------
       101

SQL> select s1.nextval from dual;

   NEXTVAL
----------
       102

Related Solutions

Oracle 10g express database link to Oracle 8i

Metalink 207303.1 is the Client/ Server Interoperability Matrix which will be the definitive documentation. Since the express edition of 10g is based on Oracle 10.2, a database link to an 8.1.7 database should be supported. Database links to earlier versions of 8i (8.1.5 or 8.1.6) are not supported from a 10.2 database. For general sanity, I'd strongly suggest that the 8i side be running at least the 8.1.7.4 patchset but that isn't strictly required.

How could sequence.nextval be null in Oracle

I'm pretty certain this will end up being an artifact of your code, or the .net driver you are using. I've knocked up a quick demo for you using pure SQL - PL/SQL and never get a lost sequence value. Incidentally the ref cursor you are using is probably unnecessary and likely impacts performance and readability of the code - my demo includes an insert_record2 procedure that consistently performs over 10% faster -in about 26s on my laptop vs 36 for the ref cursor version. I at least also think is easier to understand. You could obviously run a modified version against your test database complete with audit trigger.

/* 
demo for dbse 
assumes a user with create table, create sequence, create procedure pivs and quota. 

*/

drop table dbse13142 purge;

create table dbse13142(
    the_id number not null
,   name   varchar2(20)
,   userid number)
;

drop sequence x_seq;
CREATE SEQUENCE  X_SEQ NOCACHE  NOORDER  NOCYCLE ;

create or replace PROCEDURE Insert_Record
                (p_name    IN  VARCHAR2,                
                 p_userid  IN  INTEGER,
                 cur_out   OUT sys_refcursor)
    IS
        v_id NUMBER := 0;
    BEGIN
        -- Get id value from sequence
        SELECT x_seq.nextval
          INTO v_id
          FROM dual;

        -- Line below is X_PKG line 40
        INSERT INTO dbse13142
            (the_id,            
             name,                        
             userid)
          VALUES
            (v_id,
             p_name,                        
             p_userid);

        -- Return new id
        OPEN cur_out FOR
            SELECT v_id the_id
              FROM dual;
    END;
/


create or replace PROCEDURE Insert_Record2
                (p_name    IN  VARCHAR2,                
                 p_userid  IN  INTEGER,
                 p_theid   OUT dbse13142.the_id%type)
    IS
    BEGIN
        -- Get id value from sequence
        SELECT x_seq.nextval
          INTO p_theid
          FROM dual;

        -- Line below is X_PKG line 40
        INSERT INTO dbse13142
            (the_id,            
             name,                        
             userid)
          VALUES
            (p_theid,
             p_name,                        
             p_userid);
    END;
/

set timing on

declare
   c sys_refcursor;
begin   
for i in 1..100000 loop
   insert_record('User '||i,i,c);
   close c;
end loop;
commit;
end;
/

select count(*) from dbse13142;
truncate table dbse13142;

declare
  x number;
begin   
for i in 1..100000 loop
   insert_record2('User '||i,i,x);
end loop;
commit;
end;
/

select count(*) from dbse13142;
truncate table dbse13142;

DB