Adding a field in oracle with a unique identifier

[ashcarrot]

I know there must be something inbuilt with oracle or other dbs but i can't seem to find it

i have a db each with a primary key 1 -> n i would like to create a new entry with the next available number as key.

I could just SELECT max(id) and then plug it in though im worried about if someones doing the same thing at the same time it could cause problems.

Is there a good command for this? (i'd imagine there would be due to how often this would have to be done)

 

[ashcarrot]

Maybe i should be saying adding a *record* RATHER than a field

 

[sybaseguru]

ashcarrot,

If you are coming from MSSQL or Sybase background you can do this using an identity column. In Oracle you can create sequences. However, because of the way commit works in Oracle you may end up having gaps or a wrong order in sequence. If you want to have absolute control on this you can try the following:

Create a next rowid table to go and fetch value for the primary key of a given table. The table holding the next rowid value has two columns, nxr_name and nxr_next_rowid. These are the table name aand next rowid to be used respectively. Just use a similar function to one shown below to always fetch the correct next rowid for a given table:

create or replace function get_next_rowid
(
        i_table_name      IN      hts_next_rowid.nxr_name%TYPE DEFAULT NULL,
        i_nr              IN      NUMBER DEFAULT 1
)
RETURN NUMBER
AS
  e_table_name  EXCEPTION;
  v_table_name  hts_next_rowid.nxr_name%TYPE DEFAULT NULL;
  v_nr  NUMBER := i_nr;
  v_nxr_next_rowid hts_next_rowid.nxr_next_rowid%TYPE DEFAULT NULL;
  e_update EXCEPTION;
BEGIN
  BEGIN         -- Input parameter validation
    -- check for inputs
    IF i_table_name IS NULL
    THEN
      RAISE e_table_name;
    END IF;
    IF i_nr <= 1
    THEN
    --  DBMS_OUTPUT.PUT_LINE('get_next_rowid_sp: Warning i_nr = '||i_nr||', set to 1');
      v_nr := 1;
    END IF;
  EXCEPTION
    WHEN e_table_name
    THEN
      DBMS_OUTPUT.PUT_LINE('get_next_rowid_sp: Missing/empty parameter, must supply table name');
      RETURN -1;
  END;          -- End input parameter validation
  BEGIN         -- Check that row exists in next_rowid
    SELECT nxr_name
    INTO v_table_name
    FROM hts_next_rowid
    WHERE nxr_name = i_table_name;
  EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
      DBMS_OUTPUT.PUT_LINE('get_next_rowid_sp: No record found in hts_next_rowid table for '||i_table_name||'!');
      RETURN -101;
  END;         -- end check that row exists in next_rowid
--
  BEGIN        -- Begin update block
    UPDATE hts_next_rowid
    SET nxr_next_rowid = nxr_next_rowid + v_nr
    WHERE nxr_name = i_table_name
    RETURNING nxr_next_rowid - v_nr INTO v_nxr_next_rowid;
    IF SQL%ROWCOUNT = 0 OR SQLCODE != 0
    THEN
      RAISE e_update;
    END IF;
    COMMIT;
    RETURN v_nxr_next_rowid;
  EXCEPTION
    WHEN e_update
    THEN
      DBMS_OUTPUT.PUT_LINE('get_next_rowid_sp: Could not update nxr_next_rowid in hts_next_rowid table for '||i_table_name||'!');
      ROLLBACK;
      RETURN -100;
    WHEN OTHERS
    THEN
      RAISE_APPLICATION_ERROR(SQLCODE,SQLERRM,true);
      RETURN -100;
  END;          -- End update block
--
EXCEPTION
  WHEN OTHERS
  THEN
        DBMS_OUTPUT.PUT('Exception encountered! (');
        DBMS_OUTPUT.PUT_LINE(SQLCODE || '): ' || SQLERRM);
        RETURN -100;
END get_next_rowid;

Hope this helps

 

[dbalearner]

Guru,

This routine will come very handy especially if you are migrating from SQL server to Oracle. An application of ours will require such a function. Thanks

 

[sem]

I'm not quite satisfied with the code above so would propose some enhancements:

1. e_table_name and e_update are declared on procedure level, so I see no reason to catch them not in procedure EXCEPTION block thus creating extra exit points.

2. Additional select from hts_next_rowid is also probably redundant, because SQL%ROWCOUNT = 0 verifies that there's one corresponding record at least.

3. SQLCODE shouldn't be checked after update attempt because its non-zero value means that exception was raised.

I'd also recommend to use it in AUTONOMOUS TRANSACTION due to COMMIT issued within it.



Regards, Dima

 

[dsanchez2]

I would recommend that you use a sequence. If your only requirement is to obtain a unique key, the sequence is the best solution. Sequences do not create bottle-necks like the other proposed solutions would. Sequences may get gaps, but they are ordered.

If you use the proposed funtion as shown, it will also commit your transaction.

If you use AUTONOMOUS TRANSACTION, you can still get gaps in your sequence if the user does not commit their transaction.

 

[sem]

Ok, I agree and withdraw my suggestion about AUTONOMOUS TRANSACTION. Though commit within procedure is also very bad thing :-(

Regards, Dima

 

[dsanchez2]

I agree that a commit within a function is a bad thing.

I still think that the solution is to use a SEQUENCE. I don't understand why people worry so much about gaps. Even in a paper based system you get gaps. For example, a in paper base order system you may have order-books. Each book containg a set order-forms sequentially number. Now assume that each branch will have its own order book, so orders across the organization are not ordered. In addition, suppose that you spilled coffee on the order book, so you just spoiled a few order-forms, and just created a gap in the order-number sequence.

 

[sem]

IMO programmers don't wory about them at all, ures do. When some clerk sees document with number 1000 and the next one is 1002, he's sure that 1001 is missed and starts looking for it. Nobody can convince him (or rather her) that this's normal behaviour and system is 100% reliable.

Regards, Dima