javapandit.net
  • Home
  • Quick Java
    • Exception Handling
    • Collections Framework
  • Java Best Practices
  • Web Services
    • Web Service Basics
    • Ten Basic webservice concepts
    • XML
    • Apache Axis
    • Restful Web Services
  • JMS Concepts
    • JMS- MySQL
  • Hadoop
    • NoSQL DATABASEs
    • Apache Sqoop
    • Hadoop Interview Questions
  • Java 5
  • Java 8
    • Java 8 : Lambda Expressions
  • JDBC
  • Java Architect
    • Enterprise application re-platforming strategies
    • Java Memory Management
  • Java Programs
  • Technical Tips
    • How to set JAVA_HOME environment variable
    • How to create an auto increment field in Oracle?
    • Linux Commands
  • Best Java Interview Questions
    • Java Interview Questions- YouTube
  • Interview Questions
    • Java Tech interview Questions
    • Core Java Interview Questions >
      • core tech questions1
      • Java Collection interview questions
      • Java Concurrency
    • Servlets Interview Questions
    • JSP Interview Questions
    • Java Web Services Interview Questions
    • EJB Interview Questions
    • XML Interview Questions
    • JMS Interview Questions
  • Struts Interview Questions
    • Struts 2 Interview Questions
  • Java EE Architects Interview Questions
    • Java Architect Interview Questions
    • Top 10 reasons for Java Enterprise Application Performance Problems
    • Web Application Scalability Questions for IT Architect
  • JavaPandit's Blog
  • Web Services Interview Questions
  • Servlets And JSP
  • Oracle SOA Interview Questions
    • Open ESB /JBI
    • BPEL Language
  • Log4J
  • Ant
  • Maven
  • JMeter
  • JUnit
  • Apache POI Framework
  • ORCALE SERVICE BUS (OSB) Interview Questions
  • J2EE Patterns
    • Model-View-Controller (MVC)
    • Front Controller
    • DAO
    • Business Delegate
    • Session Facade
    • Service Locator
    • Transfer Object
    • Design Patterns >
      • Creational Patterns >
        • Singleton
      • Behavioural Patterns
      • Structural Patterns
    • Intercepting Filter
  • SQL Interview Questions/Lab
  • Best Wall Papers
    • Devotional Songs
  • Java Community
  • HIBERNATE
  • ORACLE CC&B
    • Oracle CC&B Interview Questions
  • Docker
  • Little Princess
    • Sai Tanvi Naming Ceremony Celebrations
    • Rice Feeding Ceremony
    • Sai Tanvi Gallery
  • APPSC Career Guidance
    • AP History
    • Indian Polity
    • Indian Economy
    • Science & Technology
    • Mental Ability and Reasoning
    • Disaster Management
    • Current Affairs and Events
    • General Sciences >
      • Biology
      • Physics
      • Chemistry
    • Previous Question Papers
  • About Us
  • Contact US

How to create an auto increment field in Oracle?

2/7/2013

0 Comments

 
Problem: Unfortunately oracle does not support auto_increment like mysql does. You need to put a little extra effort to get that.

Solution : In Oracle, you can create an auto increment field using ‘sequence’ database object that can be assigned as primary keys. Using Oracle ‘sequence’ object, you can generate new values for a column. An Oracle sequence is an object like a table or a stored procedure. 


Let’s walk through an example:

STEP: 1 First, Let's create an emp table with an auto increment value on emp_id  colum field and  also as a table primary key and no null value constraints.


SQL> Create table emp ( 
emp_id  number not  null,
Name   varchar2(25),
Designation varchar2(30),
);
-- Create/Recreate primary, unique and foreign key constraints
alter table emp
add primary key (emp_id)
using index;

> Table created.

STEP: 2  Now we want  emp_id  to be an auto increment field. First we need a sequence to grab values from.

SQL> CREATE SEQUENCE emp_sequence
START WITH 1
INCREMENT BY 1;

>Sequence created.


Notes: 
Now we have created a sequence object named emp_sequence with starting value as 1 and incrementing by 1.


STEP: 3  Now we want  emp_id  to be an auto increment field. First we need a sequence to grab values from.
Now we can use that sequence in an BEFORE INSERT trigger on the table.

SQL> CREATE OR REPLACE TRIGGER emp_trigger
BEFORE INSERT
ON emp
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
SELECT emp_sequence.nextval INTO :NEW.emp_id FROM dual;
END;

> Trigger created.


Notes: 
This trigger will automatically grab the next value from the sequence we just created and substitute it into the emp_id column before the insert is completed. 

STEP: 4   Now Let's do some inserts:

SQL> INSERT INTO emp (name,designation) VALUES ('Java Pandit' ,'CEO' );

1 row created.

SQL> INSERT INTO emp (name,designation) VALUES (' Ramu Gayapaka' ,'Solution Architect' );

1 row created.

SQL> INSERT INTO emp (name,designation) VALUES ('Janaki Sontiyala', 'Project Lead' );

1 row created.

SQL> SELECT * FROM emp;

EMP_ID      NAME                 Designation
———- —————————————- —————--
   1            Java pandit             CEO
   2            Ramu Gayapaka     Solution Architect
   3            Janaki Sontiyala     Project Lead

———- —————————————- —————--


Because the sequence is updated independent of the rows being committed there will be no conflict if multiple users are inserting into the same table simultaneously.

Hope this article is helpful for you to fix auto increment field creation in ORACLE Database.



                             ***************** Good Luck ******************




0 Comments



Leave a Reply.

    Author
    Ramu Gayapaka is the Founder of Javapandit  Technologies online community.

     

    Archives

    November 2013
    February 2013
    September 2012

    Categories

    All
    Architect's Page
    Management

    RSS Feed

Powered by Create your own unique website with customizable templates.