Solved

what is identity equivalent key word in oracle

Posted on 2008-10-20
3
1,685 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 150 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 350 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Java Flight Recorder and Java Mission Control together create a complete tool chain to continuously collect low level and detailed runtime information enabling after-the-fact incident analysis. Java Flight Recorder is a profiling and event collectio…
This video shows how to recover a database from a user managed backup
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

752 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