Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

what is identity equivalent key word in oracle

Posted on 2008-10-20
3
Medium Priority
?
1,691 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

The top UI technologies you need to be aware of

An important part of the job as a front-end developer is to stay up to date and in contact with new tools, trends and workflows. That’s why you cannot miss this upcoming webinar to explore the latest trends in UI technologies!

Question has a verified solution.

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

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
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.
Suggested Courses

688 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