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

<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

ijj@o2.pl wrote:
> 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

Mark has answered your question with respect to sequence generated
numbers. But what why not deal with the root cause: A bad design.
Why do you need a surrogate key at all? And if you do ... it seems
one that is non-numeric would make a lot more sense.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)