Click here to Skip to main content
15,846,346 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
like we use identity in SQL server

SQL
create table Employees
(
Id bigint identity(1,1) not null,
EmpId nvarchar(15) not null,
EmpName nvarchar(max) not null,
EmpAge numeric(3) not null,
Salary numeric(8),
ContactNo numeric(10)
)


how we can achieve identity column in oracle?

i am creating a table in oracle like as

create table employee_dtls
(
id NUMBER GENERATED always AS IDENTITY,
EmpName NVARCHAR2(30) not null,
Department NVARCHAR2(20) not null,
Contact_No number(10),
Salary decimal(8)
);


but its giving following error

Quote:
Error starting at line : 1 in command -
create table employee_dtls
(
id NUMBER GENERATED always AS IDENTITY,
EmpName NVARCHAR2(30) not null,
Department NVARCHAR2(20) not null,
Contact_No number(10),
Salary decimal(8)
)
Error at Command Line : 3 Column : 31
Error report -
SQL Error: ORA-02000: missing ( keyword
02000. 00000 - "missing %s keyword"


so my question is how we can achieve identity column in oracle database?
is Sequence and Trigger are the only way to implement
sequence in Oracle?


first of all i want to tell you i am new on oracle database.give me easy and best solution please.
thanks in advance.
Posted

1 solution

Identity definition is introduced in Oracle 12c. So if you're using a previous version, use a sequence.

Sequence is an object that is not bound to a specific table and can be used anywhere to get new unique numbers. Because of this you should create a trigger to set the value to your column.

Something like
SQL
CREATE SEQUENCE seq_employee_dtls
 START WITH     1
 INCREMENT BY   1
 NOCACHE
 NOCYCLE;

SQL
CREATE TRIGGER trg_employee_dtls
BEFORE INSERT OR UPDATE ON employee_dtls
FOR EACH ROW
BEGIN
   :NEW.id := seq_employee_dtls.NextVal;
END;
 
Share this answer
 
Comments
Maciej Los 9-Jan-16 5:04am    
I wouldn't be able to say it better. +5!
Member 10192073 9-Jan-16 5:19am    
only this is a way to identity a column in oracle?
someone had given the solution like


http://www.codeproject.com/Questions/707816/How-Can-I-Set-Auto-Increment-Property-In-Oracle

but its not working for me.

your solution is right but i want another solution if possible.
like given here also

https://oracle-base.com/articles/12c/identity-columns-in-oracle-12cr1
Wendelius 9-Jan-16 6:03am    
What version of Oracle server do you use?
Member 10192073 9-Jan-16 6:08am    
oracle 11G and using SQL Developer
Wendelius 9-Jan-16 6:14am    
That's the problem. As I said, identity was introduced in 12c.

Note that the client tools and their versions do not affect this since it's a question of database server functionality.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900