Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2005
    Location
    India
    Posts
    217

    Unanswered: Alternative to sequences....

    Some things in life, can not be changed. Like weather, volcano's, earth quakes, rain etc....
    Life is never fair and some things are just to live with.

    Having said that, i am in a strange situation where my sceniors don't believe in
    oracle sequences!! I am forced to implement a custom logic for the same!!!

    Now i know this is disastrous. And only to be done just before leaving the organization ;-)

    I know the following code will do some real harm .....but since i am helpless i just want to
    postpone the D day as much as possible.

    Following is code for generating custom sequence...
    Any suggestions will be greatly appreceiated.....
    Code:
    FUNCTION fn_GetNextSeq (prm_TableName  varchar2,prm_block number default 1) RETURN number
    AS
    o_seq_no number;
    lv_block number;
    PRAGMA AUTONOMOUS_TRANSACTION;
    BEGIN 
    
    IF  prm_block <  1 THEN
        lv_block  := 1;
        ELSE
        lv_block  := prm_block;
    END IF ;
    
    
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    
    UPDATE COM_SEQUENCE 
       SET SeqNo=SeqNo+lv_block
     WHERE upper(TableName)= upper(prm_TableName);
    
     BEGIN
    
      SELECT SeqNo-lv_block+1
      INTO o_seq_no
      FROM COM_SEQUENCE
     WHERE upper(TableName)= upper(prm_TableName);
    
    
        EXCEPTION
         WHEN OTHERS THEN 
                ROLLBACK;
    
     END;
     COMMIT;
    RETURN o_seq_no;
    
    END fn_GetNextSeq;

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Some thoughts (not about your seniors!):

    1) This:
    Code:
    UPDATE COM_SEQUENCE 
       SET SeqNo=SeqNo+lv_block
     WHERE upper(TableName)= upper(prm_TableName);
    
     BEGIN
    
      SELECT SeqNo-lv_block+1
      INTO o_seq_no
      FROM COM_SEQUENCE
     WHERE upper(TableName)= upper(prm_TableName);
    ... can be reduced to:
    Code:
    UPDATE COM_SEQUENCE 
       SET SeqNo=SeqNo+lv_block
     WHERE upper(TableName)= upper(prm_TableName);
      RETURNING SeqNo-lv_block+1
      INTO o_seq_no;
    2) Don't you want to RAISE if there is an exception?
    3) If caller passes in NULL as prm_block you will set the sequence value to NULL in the table.

  3. #3
    Join Date
    Feb 2005
    Location
    India
    Posts
    217
    Quote Originally Posted by andrewst
    Some thoughts (not about your seniors!):

    Code:
    UPDATE COM_SEQUENCE 
       SET SeqNo=SeqNo+lv_block
     WHERE upper(TableName)= upper(prm_TableName);
      RETURNING SeqNo-lv_block+1
      INTO o_seq_no;
    There is something wrong with the above code! It is givig me error when i tried to compile it.

    2) Don't you want to RAISE if there is an exception?
    Ok. I need to wrirte that code.
    3) If caller passes in NULL as prm_block you will set the sequence value to NULL in the table.
    Yeaaaaaaaaak!
    Hope my users will not pass NULL parameter.
    Any how i will change the code as below....

    Code:
    IF  nvl(prm_block,0) <  1 THEN
        lv_block  := 1;
        ELSE
        lv_block  := prm_block;
    END IF ;
    Thank you for the help.

  4. #4
    Join Date
    Feb 2005
    Location
    India
    Posts
    217
    Quote Originally Posted by ravilobo
    There is something wrong with the above code! ....
    The code worked after removing "; "
    Code:
    ..... WHERE upper(TableName)= upper(prm_TableName) ;......

  5. #5
    Join Date
    Feb 2005
    Location
    India
    Posts
    217
    andrewst ...
    Since you have combined 2 sql statements from my function into a single statement.....I don't think i need a transaction block. So can i remove the following line from my code?

    Code:
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    andrewst ...
    THANKS A LOT FOR YOUR HELP!
    Last edited by ravilobo; 10-12-05 at 12:00.

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Yes, you can.

  7. #7
    Join Date
    Feb 2005
    Location
    India
    Posts
    217
    With so many nigtmares during the past few months after implementing the
    above function.....waiting always for that one call from the user about
    application hanging....

    Now i am bit relieved...(and actually started beleving that this function
    may work and the day i was fearing may not come at all!!!
    )....after removing the transaction block and managing the
    requirement with a single statement.

    Mean while I am surprised that my senoirs have more confidence on my custom
    logic than oracle sequence! ( I hope that they will have the same confidence always;-))

    Well..thanks a lot Mr. Andrews!
    But before you take the full credit for solving the above requirement one final question
    for you.

    Do you think from your 14 years of vast experience (Thats right..i read your blog)...
    that the above function may really work in a production environment?
    Last edited by ravilobo; 10-12-05 at 12:35.

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by ravilobo
    Do you think from your 14 years of vast experience (Thats right..i read your blog)...
    that the above function may really work in a production environment?
    Well, I don't have direct experience of doing this (why would I? ), but I think it will work OK, yes. Just less efficiently than sequences.

  9. #9
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    Maybe you could speed things up a little by creating the COM_SEQUENCE table as "Index Organized"???

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •