Sequences in Oracle SQL

ABOUT SEQUENCES in Oracle SQL Definitions:- A Sequence is a DB object created to generate sequence numbers(integers) automatically. It's primary use to create auto-increment values for Surrogate Keys or Pimary Key Columns without manual intervention. Syntax: CREATE SEQUENCE [START WITH ] [INCREMENT BY ] [MAXVALUE ] [MINVALUE ] [CYCLE/NOCYCLE] [CACHE ]; OR CREATE SEQUENCE [START WITH n] [INCREMENT BY n] [MAXVALUE n | NOMAXVALUE] [MINVALUE n | NOMINVALUE] [CYCLE | NOCYCLE] [CACHE n | NOCACHE] [ORDER | NOORDER]; Example 1 :- CREATE SEQUENCE S1 START WITH 1 INCREMENT BY 1 MAXVALUE 5; using sequence :- -------------------------- CREATE TABLE STUDENT ( SID NUMBER(2), SNAME VARCHAR2(10) ); INSERT INTO STUDENT VALUES(S1.NEXTVAL , 'A'); INSERT INTO STUDENT VALUES(S1.NEXTVAL , 'B'); INSERT INTO STUDENT VALUES(S1.NEXTVAL , 'C'); INSERT INTO STUDENT VALUES(S1.NEXTVAL , 'D'); INSERT INTO STUDENT VALUES(S1.NEXTVAL , 'E'); INSERT INTO STUDENT VALUES(S1.NEXTVAL , 'F'); => ERROR SELECT * FROM STUDENT; SID SNAME 1 A 2 B 3 C 4 D 5 E Example 2 :- CREATE SEQUENCE S2 START WITH 100 INCREMENT BY 1 MAXVALUE 999; Q:How to use above sequence to update empno? UPDATE EMP SET EMPNO = S2.NEXTVAL ; Example 3 :- Create a Vehicle table and use sequence to insert the registration numbers for different brands? VEHICLES VEHNO NAME AP08AB 0001 Activa AP08AB 0002 Suziki ------ CREATE TABLE VEHICLES ( VEHNO VARCHAR2(20) , NAME VARCHAR2(20) ); CREATE SEQUENCE S3 START WITH 1 INCREMENT BY 1 MAXVALUE 9999; Q:Generate vehno using above Sequence? INSERT INTO VEHICLES VALUES('AP08AB'||TO_CHAR(S3.NEXTVAL,'0000'),'SUZIKI'); INSERT INTO VEHICLES VALUES('AP07VN'||TO_CHAR(S3.NEXTVAL,'0000'),'Honda Activa'); Example 4 :- INVOICE INVNO INVDT APINV/0124/1 APINV/0124/2 'APINV/'||TO_CHAR(SYSDATE,'MMYY')||'/'||S4.NEXTVAL CYCLE/ NOCYCLE :- ---------------- By default sequences created with NOCYCLE clause. If we are not including NOCYCLE Clause then it begin from start with and generates upto maxvalue size and after reaching maxvalue then it stops. If we include CYCLE in Sequence then it begins from Start With value and generates upto maxvalue size and after reaching maxvalue,it reset to minvalue. Ex :- CREATE SEQUENCE S4 START WITH 1 INCREMENT BY 1 MAXVALUE 5 MINVALUE 1 CYCLE CACHE 4; CACHE size :- ------------ CREATE SEQUENCE S5 START WITH 1 INCREMENT BY 1 MAXVALUE 1000 MINVALUE 1 CYCLE CACHE 100; Note: =>Oracle pre-allocates 100 values in cache memory and whenever we call =>seq.nextval Oracle goes to Cache Memory and returns the value from cache memory. =>Accessing cache memory is faster than accessing DB so that, it improves performance. => Cache size should be less than one cycle(Maxvalue) => By default Cache size is 20 Question :- ---------------- CREATE SEQUENCE S6 START WITH 1 INCREMENT BY 1 MAXVALUE 10 MINVALUE 1 CYCLE; Does the above command fails when executed, what could be the reason? ANS :-Cache size (20) is not less than one cycle (10) USER_SEQUENCES :- ---------------- SELECT MIN_VALUE, MAX_VALUE, INCREMENT_BY, CYCLE_FLAG, CACHE_SIZE FROM USER_SEQUENCES WHERE SEQUENCE_NAME='sq1' ; CHANGING SEQUENCE PARAMETERS:- ------------------------------ ALTER SEQUENCE sq1 MAXVALUE 10; ALTER SEQUENCE sq1 CACHE 9; ALTER SEQUENCE sql CYCLE; DROPING SEQUENCE:- ------------ DROP SEQUENCE S1; Standard Fusion ERP tables and their sequence names ---------------------------------------------------- per_all_people_f: person_id(PK),per_all_people_s(Sequence Name) per_all_assignments_m: assignment_id+person_id(PK)-composite PK, per_all_assignments_s (Sequence Name) per_users: user_id(PK), per_users_s(Sequence Name) po_headers_all:-po_header_id(PK) po_headers_s (Sequence Name) po_lines_all:po_line_id(PK), po_lines_s(Sequence Name) poz_suppliers: vendor_id(PK), poz_suppliers_s (Sequence Name) poz_supplier_sites: vendor_site_id(PK), poz_supplier_sites_s (Sequence Name) PK-Primary Key -------------------- Custom Table and their sequence names ------------------------------------ XXCN_SALESREPS_STG XXCN-client name stg- stage table SALESREPS- specific data exisits in table. COLUMN_NAME SRP_STG_ID (Primary Key), it requires to use Sequence inorder to assign ID numbers automatically. -------------- What are standard and custom tables in Oracle Fusion ERP Cloud? Standard Tables: Oracle's pre-defined and cannot be structurally altered by users. Custom Table: Created by the user and it should be tested. Assignment questions: 1.Write the SQL statement to create a simple sequence named emp_seq that starts at 1 and increments by 1. 2. Given a table departments(dept_id NUMBER, dept_name VARCHAR2(50)), write an INSERT statement that uses the sequence dept_seq to automatically populate the dept_id column for a new department called 'Marketting'. 3. Explain the difference between NEXTVAL and CURRVAL. What happens if you call CURRVAL in a new session before calling NEXTVAL? 4. Create a sequence named cycle_seq that starts at 1, goes up to 5, and then restarts at 1 once it reaches the limit. 5. If a transaction that performs seq_name.NEXTVAL is rolled back, what happens to that sequence number? Does Oracle "reuse" it for the next transaction? 6. What is the purpose of the CACHE clause in a sequence definition? Mention one advantage and one potential disadvantage of using a high cache value. 7. In Oracle 12c and later, how can you associate a sequence with a table column so that it behaves like an "Identity" column automatically? 8. Which data dictionary view would you query to find the LAST_NUMBER (the next value to be cached) for all sequences owned by the current user? 9. What are the key pseudocolumns used with sequences? 10. How do you find all sequences in a database? 11. Why do gaps occur in sequence numbers? 12. What happens when a sequence reaches its MAXVALUE? 13. How can you reset a sequence without dropping it?