catch the sql -oracle feedback in an pl/sql procedure

[mokesql]

hi!

i need halp with my following problem. i want to write a procedure whitch makes updates on a table. i want to first try to make a update and then if this wasnt successful i wnat to make an insert. so i want to execute the update code and then catch the oracle scl massage like 0 rows updated in 0,177 seconds and if i then find out that no rows has been updated i know that i have to make a insert. i know that thats a coplicated view and way to solve this but i just want to know if that is possible. thanks a lot

bye

moke

 

[spenglerr]

That is not at all complicated!

In PL/SQL its quite easy:


PROCEDURE update_test (id number, a number, b varchar2)

BEGIN
UPDATE test
SET col_a = a
,col_b = b
WHERE primkey = id;
IF SQL%NOTFOUND THEN
INSERT INTO test (
primkey
,col_a
,col_b )
VALUES (
id
,a
,b );
END IF;
END;
/