Re: Oracle sequence - primary key in cycle

From: Mark C. Stock <mcstockX_at_Xenquery>
Date: Tue, 20 Dec 2005 05:27:54 -0500
Message-ID: <lu-dnTbAXbcwQTreRVn-iQ@comcast.com>

<ijj_at_o2.pl> wrote in message
news:1135073069.189985.277890_at_g44g2000cwa.googlegroups.com...
> Hi,
>
> I have a table in a data warehouse which has reached its limit for
> autogenerated primary key - a signed 32 bit integer which can be a
> decimal number from -2147483648 to 2147483647 (I use Informatica
> PowerCenter, ETL tool, which creates it like that).
>
> However, many rows are deleted from this table and because of that some
> values of primary key are currently not used, so it looks like:
> 1,2,5,10, 24, 15, ..... and so on.
>
> I have a question how Oracle sequence should look like when I would
> like to generate primary keys in cycle, checking if the next value of
> the PK is already used in the table (no insert/update) or not (insert).
>
> I would be very grateful for answer.
>
> Iza
>

the sequence can't handle that in and of itself -- you'll need to code a check against the PK or trap the exception.

something like (just an example, not saying this is the best way to handle it):

create or replace function get_next_deptno_pk return number
is

   n_retval number;
   n_exists number := 0;
begin

   loop

       select detno_seq.nextval into n_retval from dual;
       select count(*) into n_exists from dept where deptno = n_retval;
       exit when n_exists = 0;

   end loop;
   return n_retval;
end get_next_deptno_pk;

once you hit the sequence's maxvalue, this code will raise ORA-08004 and you'll be back in the same boat

++ mcs Received on Tue Dec 20 2005 - 04:27:54 CST