[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2009-05-04
10
Medium Priority
?
793 Views
Last Modified: 2013-12-07
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
Comment
Question by:thermalsun
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
10 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 24294888
see attached
ee.txt
0
 
LVL 1

Author Comment

by:thermalsun
ID: 24295251
Can I trap that I values is going below minimum value of the sequence then use another value of reset?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 24295409
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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
LVL 74

Expert Comment

by:sdstuber
ID: 24295413
if the sequences is owned by a different user you may need to use all_sequences or dba_sequences and specify the owner
0
 
LVL 1

Author Comment

by:thermalsun
ID: 24302917
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
 
LVL 1

Author Comment

by:thermalsun
ID: 24303172
getting status = 3 (Parameter error).

Which parameter is incorrect !!
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 2000 total points
ID: 24303551
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
 
LVL 1

Author Comment

by:thermalsun
ID: 24305197
cool.. that a lot sdstuber for your help.

I got it working :)
0
 
LVL 1

Author Closing Comment

by:thermalsun
ID: 31577489
Excellent and timely help  :)
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 24305287
Glad I could help!
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to take different types of Oracle backups using RMAN.

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question