Sorry Tom if I sounded disrespectful in my above post. I realized I was not able to frame my question to you properly. As they say 'Engineers language is drawing' I guess Oracle technologists language is sql and pl/sql - so let me try to demo to you what I am asking using sql:


SQL> ed
Wrote file afiedt.buf

  1  create table order_line_items
  2  (order_id varchar2(20) not null,
  3  line_item_seq number(3) not null,
  4  item_id varchar2(20) not null,
  5* quantity number (9,2))
SQL> /

Table created.


-I want to use a procedure to insert records into the table. All inserts go through the procedure. The line_item_seq has to increment starting with 1 for each order_id. I want to use oracle sequences, but I figured out this probably is a better way:

SQL> create or replace
  2  procedure p_add_oline_item(p_order_id in varchar2, p_item_id in varchar2, p_quantity number)
  3  as
  4  begin
  5  insert into order_line_items (order_id, line_item_seq, item_id, quantity)
  6  values (p_order_id, (select max(line_item_seq)+1 from order_line_items where order_id=p_order_id), p_item_id, p_quantity);
  7  commit;
  8  end;
  9  /

Procedure created.

--This is what it would look like using a sequence (as I know of). It is psedo code - oracle does not have an easy way to reset a sequence. I think this is enough to give you the idea -

  1* create sequence oline_item_seq start with 1 increment by 1 maxvalue 999
SQL> /

Sequence created.

SQL> create or replace
  2  procedure p_add_oline_item2(p_order_id in varchar2, p_item_id in varchar2, p_quantity number)
  3  as
  4  begin
  5  if not exists (select null from order_line_items where order_id=p_oder_id) then
  6  alter sequence oline_item_seq.nextval reset;
  7  end if;
  8  insert into order_line_items (order_id, line_item_seq, item_id, quantity)
  9  values (p_order_id, (select max(line_item_seq)+1 from order_line_items where order_id=p_order_id), p_item_id, p_quantity);
 10  commit;
 11  end;
 12  /

Warning: Procedure created with compilation errors.


This is a very common design and we can easily do from java. I want to do it in the db using pl/sql. I was intending to use sequences until I started coding when I realized it is not as easy as I thought it was. I want to know if there is a better way to do this using oracle sequences - can you demo please, I am eager to learn. Else, do you think this is a special case where select max()+1 is better than a sequence?

Oops, the procedure using the sequence that I meant to post was:

SQL> create or replace procedure p_add_oline_item2(p_order_id in varchar2(20), p_item_id in varchar2(20), p_item_id number(9,2))
  2  )
  3  as
  4  l_oder_id varchar2(20);
  5  begin
  6  if not exists (select null from order_line_items where order_id=p_oder_id) then
  7  exception when no_data_found then
  8  alter sequence oline_item_seq.nextval reset;
  9  end if;
 10  insert into order_line_items (order_id, line_item_seq, item_id, quantity)
 11  values (p_order_id, oline_item_seq.nextval, p_item_id, p_quantity);
 12  commit;
 13  end;
 14  /

Warning: Procedure created with compilation errors.

I don't know you DBMS version, but if it's not 11g then what you're trying there will fail.

Try something like:

SQL> create or replace procedure p_add_oline_item2(p_order_id in varchar2(20), p_item_id in varchar2(20), p_item_id number(9,2))
  )
  as
  l_oder_id varchar2(20);
 begin
    insert into order_line_items (order_id, line_item_seq, item_id, quantity)
    select p_order_id, oline_item_seq.nextval, p_item_id, p_quantity from dual;
  exception 
    when dup_val_on_index
      then NULL;

END;
/