DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Insert statement based on row data....

  1. #1
    Join Date
    Aug 2000
    Location
    Toronto, Ontario Canada
    Posts
    52

    Question

    I'm trying to insert a record into a table if the data doesn't exist...pretty simple I think but the syntax eludes me...

    Here's what I have. When I execute the query, the procedure
    spins out of control.

    create or replace procedure insert_utest
    (p_username IN varchar2)
    IS
    v_username varchar2(5);

    CURSOR get_user IS
    SELECT username FROM utest
    WHERE username=p_username;

    BEGIN
    OPEN get_user;
    LOOP
    FETCH get_user into v_username;
    IF get_user%NOTFOUND THEN
    insert into utest values (v_username);
    commit;
    ELSE
    dbms_output.put_line('User ' ||v_username|| ' exists');
    exit;
    END IF;
    END LOOP;
    CLOSE get_user;
    END;

    ???

    Running the query does not return and generates a ton
    of redo. I can't see to find the problem????



  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    No wonder your procedure spins out of control, you've created an infinite loop.

    If you pass to the procedure the username that does not yet exist in your table UTEST, you doesn't have any exit from your loop! It will continue inserting the same name into the table untill it fills the tablespace or you kill the procedure.

    To avoid this you could do one of the following things (in the order from worse to better):

    1. Put EXIT after your commit in the first part of your if-else dtatement.
    2. Why have you tried to use a loop in the first pace? You don't need it at all! Simply remove LOOP and END LOOP as well the existing EXIT after DBMS_OUTPUT and you are done.
    3. Create an unique index on UTEST.UNAME (or create unique or primary key constraint), then change your code to:

    create or replace procedure insert_utest
    (p_username IN varchar2)
    IS
    BEGIN
    insert into utest values (p_username);
    commit;
    EXCEPTION
    WHEN_DUP_VAL_ON_INDEX THEN
    dbms_output.put_line('User ' ||p_username|| ' exists');
    END;

    This code is much simplier, readable and efficient.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Aug 2000
    Location
    Toronto, Ontario Canada
    Posts
    52
    Thanks.

    After some tinkering I realized why the proc. was looping but I like the clarity of your solution.


  4. #4
    Join Date
    Mar 2001
    Posts
    1

    Arrow

    hi
    you should first initialize your username variable ( a one local to the procedure to null)....and more important..

    when you are checking cursorname%notfound, its not enough as the value for the %notfound attribute for the first time is null so you write "when cursorname%notfound or cursorname%notfound is null then"

    hope this will solve problem

Posting Permissions

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


Click Here to Expand Forum to Full Width