obtain primary keys

Hello all!

First, I'm not a dba- I am a sysadmin. company is "between" dba's so I have to take care of an oracle database used for developping a webapp.

The application uses stored procedures where I see a lot of:

 lock table t in exclusive mode nowait;
 select max(ix) + 1 into pix from t;
 insert into t values (pix, etc...);
 commit;

To me this locking seems a bit drastic.

One way of doing it would be using sequences from triggers, like:

 create or replace trigger tinsert
  before insert on t for each row
  begin
    if :new.ix is not null then
      raise_application_error(-20000, 'ix must be null');     else
      select seq_t_ix.nextval into :new.ix from dual;     end if;
  end;

What I like with this is that there is no way the programmers can fail :-) but there are a slight drawback when it comes to concatenated primary keys.

There are parts of concat keys that needs to be changed from number(6) to number(13) if to use sequences. This might be a stupid question, but it is somehow possible to estimate, calculate or test how such change will effect performance and disk usage?

Any thoughts on this? Is there an "Oracle-way" or "best practice" on how to get primery key values?

Them programmers are used to sql-server and claims that there is a special datatype for this there but this does not seem to be the case for Oracle- or am I wrong?

It's on 10.1.0.3 if that matters.

Thanks for your time,
ph Received on Thu Feb 10 2005 - 16:00:27 CST