Skip to main content

Oracle database use of sequences for incrementing values.

We always find a need to use incremental sequence for our table data columns like primary key or other unique keys. Examples of such fields could be id fields like employee id or order sequences for order processing in OLTP databases.

Oracle provides very efficient non blocking way of getting and generating these new sequences with a database object called sequence.

Sequence should be created and used as follows.

CREATE SEQUENCE employee_id_seq
 START WITH     10000   -- Start your sequence with 10000
 INCREMENT BY   1       -- Sequence would be incremented by 1 when nextvalue is fetched from sequence
  NOCYCLE;              -- Oracle sequence would not restart 
                        -- If you want sequence to start over after N then specify maxvalue N clause in the sequence

Full syntax of sequence statement

Once we are done creating sequence object in database next thing is pretty straightforward.
You can use your sequence immediately in insert statement.

INSERT INTO EMPLOYEES (EMPLOYEE_ID , FIRST_NAME,LAST_NAME ) 
VALUES
(employee_id_seq.NEXTVAL , 'PATRICK' , 'HARRIS');

If you are using Oracle Forms or some other tool that generates Inserts for you and if you do not have direct control then consider writing a BEFORE INSERT Trigger that would call the sequnce.nextvalue and provide the next sequence to your insert.
Ex.

CREATE OR REPLACE TRIGGER employees_pre_ins
BEFORE INSERT ON employees
FOR EACH ROW
begin
  SELECT employee_id_seq.NEXTVAL
  INTO   :new.employee_id
  FROM   dual;
END;
/

Comments

Popular posts from this blog

Use the Microsoft Visual Studio Code(VScode) with Java Maven project.

The VS Code is the go to editor now a days for the latest technologies and scripting options. VS Code as we all know works seamlessly with Python and JavaScripts, and offcourse the .NET family. But VS Code has many extensions which makes it big Java IDEs run for their money. Lets explore what we could do with VSCode if we are Java developer. Install VScode , no brainer. Jus type download Visual Studio code in google. Alrright once we have VSCode. Open it. Install Maven and Java extension. 2. Go to files and a small window will now appear on left side navigation bar. 3. Select New Maven project. 4. Select the archtype from dropdown. 5. As usual input the grpid, artifactid etc. 6. Done. Go through a video which has much detailed navigation flow. Till next time !!!

Oracle String quote escape tool to help you generate the escaped pl sql string

Oracle string quote escape tool : There will be a times when you are writing dynamic sql and that sql  has lot of quote constants in where clause or in your select list , escaping these quotes can be quite cumbersome if you don't have the right tool. If you have a large query or string which has quotes in it  and you do not want to bother checking the escapes and want to use the escaped query / string readily in your plsql. Below tool will help you generate escaped string from Oracle  plsql standpoint , just copy and paste it in your code It's not ours to take. Escape quotes

Cyclic blocking session removal script written by me for Oracle Database.

Plenty of times we have cyclic database blocking sessions. We have the script which runs in loop and kills them , only to see new blocking sessions have resurfaced. This is a classic scenario that happens many time due to poor application design. A was blocking B , B was blocking C. Unless you have some automated blocking session clearing script. You would run a script at point in time killing session of A. Giving the lock ownership to B.   B could realize it late that he has the lock and not commit his work. So DBA again goes in and sees block kills B's session. In meantime Frustrated A logs in and start his activity again queuing him up in wait for lock retrieval . This goes on and you end up running the script 5-10 times to kill these sessions to finally clear the blocks. Note this is not a deadlock which is normally apprehended and identified by oracle. In such case we had written below plsql to clear the sessions. This basically goes in and check for locks ever...