Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2006
    Posts
    12

    Unanswered: migratin script from sql server 2 oracle

    whats the equivalent of the below sql server2005 script in Oracle PL/SQL

    CREATE TABLE [omx].[UserAuthorization](
    [MemberAuthorizationRecordIdentifier] [bigint] IDENTITY(1,1) NOT NULL,

    CONSTRAINT [PK_UserAuthorization] PRIMARY KEY CLUSTERED
    (
    [MemberAuthorizationRecordIdentifier] ASC
    )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    To get the answer this question, you'll have to wait for a Forum member who knows both SQL Server and Oracle. However, if you are in a hurry, you could explain what this script does. OK, it is obvious that creates a table, but - what is "identity(1, 1)", what does "ignore_dup_key" do, what is "on [primary]", etc.

    If the answer is (not necessarily, but could be) "Google for it and you'll know", well, you could also Google for Oracle's CREATE TABLE script

    Now seriously, just as a first aid: BIGINT is, probably, a datatype. I have no idea what IDENTITY means, so I'll leave it alone. Constraint is OK, while ASC might mean that this column autoincrements in ascending order and you don't want to allow duplicates to appear.

    If so, here it is: as Oracle doesn't support autoincrement columns, we'll have to simulate it using a sequence and a database trigger:
    Code:
    SQL> CREATE TABLE user_authorization
      2  (member_id NUMBER PRIMARY KEY,
      3   other_columns NUMBER
      4  );
    
    Table created.
    
    SQL> CREATE SEQUENCE seq_auth;
    
    Sequence created.
    
    SQL> CREATE OR REPLACE TRIGGER trg_auth
      2    BEFORE INSERT ON user_authorization
      3    FOR EACH ROW
      4  BEGIN
      5    SELECT seq_auth.NEXTVAL INTO :NEW.member_id FROM dual;
      6  END;
      7  /
    
    Trigger created.
    As an illustration, let's add a few records and see how it works:
    Code:
    SQL> INSERT INTO user_authorization (other_columns) VALUES (123);
    
    1 row created.
    
    SQL> INSERT INTO user_authorization (other_columns) VALUES (556);
    
    1 row created.
    
    SQL> SELECT * FROM user_authorization;
    
     MEMBER_ID OTHER_COLUMNS
    ---------- -------------
             1           123
             2           556
    
    SQL>
    If that's not it, say so and we'll try to correct it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •