<ijj@o2.pl> wrote in message
news:1135073069.189985.277890@g44g2000cwa.googlegr oups.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