identity column in oracle

Question

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 8-Jan-16 22:37pm

Member 10192073

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Wendelius · Accepted Answer · 2016-01-08T22:59:00

Solution 1

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;

Posted 8-Jan-16 22:59pm

Wendelius

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.

Member 10192073 9-Jan-16 21:54pm

ok sir thanks,so it means either i have to go for 12C oracle version or by create sequences as you mentioned above.

Wendelius 10-Jan-16 0:33am

Exactly :)

Member 10192073 10-Jan-16 4:29am

ok give me solution for
check constraint on Date of birth..........
means (today-Date of birth)>= 18 years.

Wendelius 10-Jan-16 4:39am

Since this is completely different question, please post a new question in Q&A.

Member 10192073 10-Jan-16 4:40am

ok i am goint to put

Wendelius 9-Jan-16 6:04am

Thanks :)