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.

11 comments:

oraclenerd said...

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

William Robertson said...

Actually I formatted it a bit before posting. Thanks!

Commas go in front, with one space. Everybody knows that.

Niall said...

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

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.

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?

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.

silentd said...

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

fluff said...

Did this code come from forms? I have myself done things like that where they system can easily go into infinite loops and forms is one of those. I'm not going into details but there are some interpreted systems where it is exceedingly easy for the system to start looping. Typically some kind of reset or exception can trigger it. That causes people to put in loop stoppers like this. The underlying problem is the interpreter doesn't detect and stop loops. I wouldn't complain about it unless you like programs where the only way to halt a loop is to kill the whole thing and lose all your data.

Adrian Christie said...

Fluff: put down the drink, and step away from the bar.

manni said...

lololololol

Pól Anaithnid said...

The only reason I can see for coding
like this is if one is programming
against a blocking server like
SQLite - where only one user is allowed
access the database (1 file in this
case) at any given instant - so you
try 127 times - but since PL/SQL doesn't
work against SQLite (or is there something
I should be told?