skip to main | skip to sidebar

# Oracle WTF

Looping the loop

## Wednesday, September 02, 2009

### If at first you don't succeed...

...then try again. Then try again more 125 times. Then quit.

    PROCEDURE get_id
        ( p_id_out         OUT NUMBER
        , p_name_in        IN VARCHAR2
        , p_create_user_in IN VARCHAR2 )
    IS
        v_new_id      NUMBER := 0;
        v_max_tries   PLS_INTEGER := 127;
        v_default_id  NUMBER := 0;
    BEGIN
        v_new_id := lookup_id(p_name_in); -- will be 0 if not found

        WHILE v_new_id = 0 AND v_max_tries > 0
        LOOP
            BEGIN
                INSERT INTO entry
                ( entry_id
                , entry_name
                , create_date
                , create_user
                , create_app
                , mod_date
                , mod_user
                , mod_app)
                VALUES
                ( entry_seq.NEXTVAL
                , p_name_in
                , SYSDATE
                , p_create_user_in
                , 'get_id'
                , SYSDATE
                , p_create_user_in
                , 'get_id' )
                RETURNING entry_id INTO v_new_id;

            EXCEPTION
                WHEN OTHERS THEN NULL;
            END;

            v_max_tries := v_max_tries - 1;
        END LOOP;

        p_id_out := v_new_id;
    END get_id;

Thanks BB for sending this.

#### 7 comments:

oraclenerd said...

  at least it's pretty (ignoring the commas in front of course).

  September 02, 2009 5:04 PM
William Robertson said...

  Actually I formatted it a bit before posting. Thanks!
  Commas go in front, with one space. Everybody knows that.

  September 02, 2009 5:22 PM
Niall said...

  commas in front with a tab. sheesh does no-one go to sqlchurch these days...

  September 18, 2009 11:02 AM
Thai Rices said...

  This technique must be gaining popularity.
  I found similar code this morning, but in this case the perpetrator had managed to make it even less efficient by using an exception handler to recursively call the procedure (containing a MERGE) with an incrementing p_error_attempts parameter.

  September 18, 2009 2:25 PM
William Robertson said...

  Welcome back Thai. Does this mean you'll be posting the one about the missing users and the other one about the telephone interview?

  September 18, 2009 11:00 PM
William Robertson said...

  Ironically I formatted the code before posting so that irrelevant details wouldn't get in the way of the WTF. The original version looked like this.

  September 18, 2009 11:09 PM
silentd said...

  Who was it that said the definition of insanity is performing the same action repeatedly and expecting different results?

  September 21, 2009 10:14 PM

#### Links to this post

Older Post Home
Subscribe to: Post Comments (Atom)