Please help me with this problem given to me by my boss.
''I need to create sequence/primary key which will pick the first 4 characters of a column called dept and count the number of records in that same dept to generate the p.k. the sequence must restart at every new dept. eg.
select dept,grade from staffrecord
insert into mytable
values (dept,grade,seq)
Thus the records in mytable should look like
Zoology Senior Clerk ZOOL001
Zoology Junior Clerk ZOOL002
Mathematics Lecturer MATH001
Mathematics T.A MATH002
............ ..... ......
in that order.
Will plsql rather be okay?
Thanks

select dept,grade,upper(substr(dept,1,4)) ||
to_char(rank () over(partition by dept order by grade ) ,'fm0000')
from mytable;

Hi Tom,

I think this relevant here. I would like to use a
sequence to in this way. I need to keep the current
value of the sequence in order to populate my
child table. So my question is how can I keep(preserve)
the current value so that I can use it for my child
table?

Hi Tom, 

I have a table:

SQL> desc BUDGETITEMAMOUNTS
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 BUDGETITEMID                              NOT NULL NUMBER(14)
 BUDGETITEMAMTSEQID                        NOT NULL NUMBER(1)
 BUDGETITEMAMTAMT                          NOT NULL NUMBER(20,4)

The requirement is to incremental budgetitemamtseqid field automatically for each new record of the same budgetitemid.

For example:
BUDGETITEMID           BUDGETITEMAMTSEQID     
1                      1
1                      2
2                      1
1                      3
2                      2
2                      3

I write a trigger like this one:

CREATE OR REPLACE TRIGGER budgetitemamounts_seq_trg
BEFORE INSERT ON budgetitemamounts
FOR EACH ROW
BEGIN
  IF (:new.BUDGETITEMAMTSEQID) IS NULL THEN
    SELECT nvl(max(BUDGETITEMAMTSEQID),0) + 1
    INTO :new.BUDGETITEMAMTSEQID
    FROM budgetitemamounts 
    WHERE BUDGETITEMID = :NEW.BUDGETITEMID;
  END IF;
END;
/


But I realized that it may have a concurrency problem that multiple users may try to insert the same seqid. Do you have a better idea on how to do it?

Thanks.
 

Hi Tom,
I have a requirement where in I need to "lock" a finite set of sequences. I am not satisfied with the requirement itself but before I say so to the developers, I wanted to know if there is a way out.

We have a c++ program that calls a pl/sql stored procedure.
The SP calls using a loop another SP to "reserve" a block of sequences. The 2nd SP looks like below:

CREATE OR REPLACE PROCEDURE LYGETBLOCKSEQUENCESP
(
po_resultcur OUT ETS_PKG.refcur,
pi_seq_name IN VARCHAR2,
pi_reserve_value IN NUMBER,
po_returnstatus OUT NUMBER
)
IS
PRAGMA AUTONOMOUS_TRANSACTION;

v_msg VARCHAR2(1000);
v_procid VARCHAR2(255) := ' [LYGETBLOCKSEQUENCESP]';
v_sqlstatement VARCHAR2(4000);
v_startval NUMBER;
v_endval NUMBER;

BEGIN

-- This inital selection of NEXTVAL is to initialize the sequence within a session.
EXECUTE IMMEDIATE ' SELECT ' || pi_seq_name || '.NEXTVAL ' ||
'FROM DUAL '
INTO v_startval;

EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || pi_seq_name || ' INCREMENT BY ' || TO_CHAR(pi_reserve_value+1);

EXECUTE IMMEDIATE ' SELECT ' || pi_seq_name || '.NEXTVAL ' ||
'FROM DUAL '
INTO v_endval;

EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || pi_seq_name || ' INCREMENT BY 1';


OPEN po_resultcur
FOR 'SELECT :b_startval AS "StartValue", '
||' :b_endval AS "EndValue" '
||'FROM DUAL '
USING v_endval - pi_reserve_value + 1,
v_endval;

po_returnstatus := 0;

EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = -20999 THEN
v_msg := SUBSTR(SQLERRM,12);
ELSE
v_msg := SUBSTR(SQLERRM,1,255) || v_procid;
END IF;

-- Return -1 to the calling program to indicate failure.
po_returnstatus := -1;
RAISE_APPLICATION_ERROR(-20999,v_msg);
END;


Let us say the pi_reserve_value is 5. Based on the v_startval and v_endval the app uses after a certain amount of time, the intermediate values for a key field (it can decide to rollback the whole transaction too). Multiple users can be executing the same application concurrently. The requirement is that for two different sets of values there should be no overlap. For example if user A gets (100,106), (117,123)... then user B should not get (104,110), (111,117) etc.(104,105 and 106 will be used by 2 users wrongly).

Is there any way to overcome the above problem using the Oracle Sequences? Or will this be better off taken care by C++ itself? Gaps in the sequences is not a problem.

Thanks a lot for all the help I get from your site.

Manjunath

Hi Tom,

THanks, this gave me the necessary info.

Manjunath

In the production system, I am (in some cases) using auto generate key take from
FUNCTION (PL/SQL). I know that this is not the best solution because it will not scale well.

The function I discussed here, implemented using AUTONOMOUS TRANSACTION, for expectation reducing overhead (locking/serialization) on the table that maintain the key generation.

What I do here, caused that the SEQUENCE cannot satisfy my purpose
(this conclusion base on my knowledge now).

This is the scenario:

The key that I alway generate have the format
two characters,two digits explaining current year, six digits counter,
so it may look like : AA06001234 (AA - application identifier, 06 - current year and 001234 - current counter for that account)

When the year part change (e.g : 07 - next year), the counter starting again from 000001.
For this purpose, I implemented key generation by using FUNCTION not SEQUENCE.
What I am asking here, is there any solution propose by using SEQUENCE ?
Please give me the light Tom.

So you think, I must breaking down the key into three different piece and
making them to be composite primary key?

To make that changes, I will rebuild application totally. I am not the
system architect that disign the system and I enter the project when the analysis phase was finished and start to coding. And now it is in production !!!

When I ask him (system architect), why we must encode three fields into one, not separating it (as your advice), so we can employ SEQUENCE on the generation of counter part.
He said that when the counter part is separated, it will grow to very huge number that we can not control it. Say the maximum counter for last year is 986432, and when we added from transactions this year, it will exploded (more than just six digits).
Human being could remember number aproximately below 10 digit (as the theory of software engineering he said), and when the number (counter part) becoming huge, human will difficult to remember it in temporary time.

Questions:
o Do you have best practice in genereting key automatically. Just as my requirement that I discussed in the previous question. Please elaborate your solutions.
o When I am stay to using the first approach (encoding it into one field), do you think AUTONOMOUS_TRANSACTION will help in the function generation ?

Thanks Tom.

I noticed the following behavior while I was using Oracle sequence in an analytic function such that it appears CURRVAL is returning a number incremented.

select l,
case when l<3 then '<3' else '>=3' end case_char,
case when l<3 then seq1.nextval else seq1.currval end case_seq
from ( select level l from dual connect by level <= 5)

L CAS CASE_SEQ
---------- --- ----------
1 <3 446
2 <3 447
3 >=3 448
4 >=3 449
5 >=3 450


Is there an explanation for this?

Thanks a lot, Tom, for your tremendous contribution to the Oracle community.