Insert statement based on row data....

[steviep9]

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????

[jmodic]

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.

[steviep9]

Thanks.

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

[cholkar]

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