• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 802
  • Last Modified:

How to make sure Oracle Sequence in SP is called only from 1 thread / user at a time

I have an SP which resets a Sequence to 1 and return the nextvalue to user.

How to handle race condition in here so following error is avoided.
exception ORA-08004 sequence
            COB_SEEQ_KITID.nextval goes below min value and cannot be instantiated
CREATE OR REPLACE PROCEDURE SP_GetNextKitID
(
        O_KitID             OUT NVARCHAR2
)
AS
BEGIN
 
        DECLARE
        v_Sequence         INTEGER;
        
        BEGIN
              
        SELECT COB_SEQ_KITID.NEXTVAL INTO v_Sequence FROM DUAL; 
        
        IF <some condition> THEN
            BEGIN
                EXECUTE IMMEDIATE ' ALTER SEQUENCE COB_SEQ_KITID INCREMENT BY '|| TO_CHAR (1 - v_Sequence);
                SELECT COB_SEQ_KITID.NEXTVAL INTO v_Sequence FROM DUAL;                
                EXECUTE IMMEDIATE 'ALTER SEQUENCE COB_SEQ_KITID INCREMENT BY 1';       
            END;
        END IF;     
 
        SELECT TO_CHAR(SYSDATE,'YY') ||TO_CHAR(SYSDATE,'DDD')|| LPAD(v_Sequence,5,'0') INTO O_KitID FROM DUAL;
    END;
END;

Open in new window

0
thermalsun
Asked:
thermalsun
  • 5
  • 5
2 Solutions
 
sdstuberCommented:
see attached
ee.txt
0
 
thermalsunAuthor Commented:
Can I trap that I values is going below minimum value of the sequence then use another value of reset?
0
 
sdstuberCommented:
yes, you can either define a pragma for that exception or check sqlcode in a when others clause of an exception block in your procedure

or you can avoid the error by checking the minimum value before trying to reset

select min_value from user_sequences where sequence_name = 'COB_SEQ_KITID';

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
sdstuberCommented:
if the sequences is owned by a different user you may need to use all_sequences or dba_sequences and specify the owner
0
 
thermalsunAuthor Commented:
I created a package and used Locks but the system is not able to acquire lock..

what could be going wrong... pls help.
CREATE OR REPLACE PACKAGE FLXUSER."COB_KIT_SEQUENCE" 
AS  
    PROCEDURE reset_kitid_seq (v_SequenceOUT OUT INTEGER);      
END;
 
 
CREATE OR REPLACE PACKAGE BODY  "COB_KIT_SEQUENCE" 
AS  
lock_handle NUMBER := 1073741800;    
PROCEDURE RESET_KITID_SEQ (v_SequenceOUT OUT INTEGER)
IS    
    lock_status INTEGER;    
    v_LastRefresh DATE;
    v_Error EXCEPTION;
      
        BEGIN           
           v_SequenceOUT:=0;         
            
           lock_status := dbms_lock.REQUEST(lockhandle => lock_handle, lockmode => dbms_lock.x_mode);
 
           IF lock_status = 0 THEN
            SELECT LastUpdateOn INTO v_LastRefresh 
            FROM SEQUENCE_ 
            WHERE UPPER(NAME) = 'KITID';        
                
            SELECT COB_SEQ_KITID.NEXTVAL INTO v_SequenceOUT FROM DUAL; 
                
                IF TO_CHAR(v_LastRefresh,'DD/MM/YYYY') <> TO_CHAR(SYSDATE,'DD/MM/YYYY') THEN
                    BEGIN
                        EXECUTE IMMEDIATE ' ALTER SEQUENCE COB_SEQ_KITID INCREMENT BY '|| TO_CHAR (1 - v_SequenceOUT);
                        SELECT COB_SEQ_KITID.NEXTVAL INTO v_SequenceOUT FROM DUAL;                
                        EXECUTE IMMEDIATE 'ALTER SEQUENCE COB_SEQ_KITID INCREMENT BY 1';
                        UPDATE SEQUENCE_ SET NEXT_ = 2, LASTUPDATEON = SYSDATE, LASTUPDATEDBY = 'GetNextKitID'
                        WHERE UPPER(NAME) = 'KITID';
                    END;
                END IF;
                           
           ELSE
          
               RAISE v_Error;           
           END IF;
        
           lock_status := dbms_lock.release(lockhandle => lock_handle);
            
            EXCEPTION
            WHEN v_Error THEN raise_application_error (-20660, 'Failed to process sequence COB_SEQ_KITID');  
        END;       
    END;

Open in new window

0
 
thermalsunAuthor Commented:
getting status = 3 (Parameter error).

Which parameter is incorrect !!
0
 
sdstuberCommented:
dbms_lock.release and request are overloaded

lockhandle should be a string that you have used "allocate_unique" to generate a number for.

if you just want to use the number, then use parameter ID instead.
0
 
thermalsunAuthor Commented:
cool.. that a lot sdstuber for your help.

I got it working :)
0
 
thermalsunAuthor Commented:
Excellent and timely help  :)
0
 
sdstuberCommented:
Glad I could help!
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now