Solved

what is identity equivalent key word in oracle

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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Java functions are among the best things for programmers to work with as Java sites can be very easy to read and prepare. Java especially simplifies many processes in the coding industry as it helps integrate many forms of technology and different d…
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.
This tutorial explains how to use the VisualVM tool for the Java platform application. This video goes into detail on the Threads, Sampler, and Profiler tabs.
Suggested Courses

627 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