Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

what is identity equivalent key word in oracle

Posted on 2008-10-20
3
Medium Priority
?
1,694 Views
Last Modified: 2013-12-18
I have .sql scripts generated from sybase for creating table structure and trying to run on oracle  client toad after changing the datatypes from datetime to date etc.Toad is not recognizing identity (auto increment number column ).It is giving error when i create table saying right parenthesis missing as in the screenshot.
identity.jpg
0
Comment
Question by:gudii9
3 Comments
 
LVL 8

Assisted Solution

by:Wikkard
Wikkard earned 600 total points
ID: 22763595
I don't think oracle has the same notion of identity (autonumber) columns as SQL server or sybase.  

It uses sequences which you need to create as a seperate (sequence) database object and then create a trigger on insert for the table which needs the identity column to update the id.

This article explains it http://www.orafaq.com/faq/how_does_one_implement_autonumber_and_identity_columns_in_oracle
0
 
LVL 4

Accepted Solution

by:
hqassap earned 1400 total points
ID: 22764894
Hi,
In oracle things get weird:

CREATE TABLE comparison_axis (ca_id NUMBER,      -- Remove the (identity) word
ca_vers CHAR(1) NOT NULL,
...
...
...
insert_userid VARCHAR(25),
CONSTRAINT pk_comparison_axis PRIMARY KEY (ca_id)    -- Remove the two hyphens (this is the replacement of the identity
)

-- Then create a sequence to auto generate the identifiers:

CREATE SEQUENCE seq_ca_id
     MINVALUE 1
     MAXVALUE 999999999999999999
     START WITH 1
     INCREMENT BY 1
     NOCACHE;
      
-- And Finally create a triggere before insert on the record level to force the sequencing:

CREATE OR REPLACE TRIGGER trg_ca_insert
   BEFORE INSERT
   ON comparison_axis
   FOR EACH ROW
BEGIN
   IF :NEW.ca_id IS NULL
   THEN
      SELECT seq_ca_id.NEXTVAL
        INTO :NEW.ca_id
        FROM DUAL;
   END IF;
END;
/
0
 
LVL 7

Author Closing Comment

by:gudii9
ID: 31508104
thaank you very much
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.
Suggested Courses

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question