No NO_DATA_FOUND

[CMerc]

This falls in the "There's gotta be an easier way" Category...

From within a PL/SQL block or proc I want to determine if a value exists in a table without putting the whole thing into a sub proc to handle the NO_DATA_FOUND exception.

Ideally I want to do the equivalent of the code below which fails because I can't execute a simple SELECT in a PL/SQL Block. If I make it an INTO I get the NO_DATA_FOUND error and it goes to the EXCEPTION or out all together.;

BEGIN

-- STUFF HERE ---

SELECT PropertyID
FROM BOLDED
WHERE PropertyID = 109;

IF SQL%NOTFOUND THEN
--Doing Stuff like INSERT INTO FEATURES...;
ELSE
--Doing Different Stuff like UPDATE FEATURES...;
END IF;

--MORE STUFF HERE and in the above IF statement --
END;

[Highlander]

Try this:

SELECT count(*)
into count_var
FROM BOLDED
WHERE PropertyID = 109;

IF count_var < 1 THEN
--Doing Stuff like INSERT INTO FEATURES...;
ELSE
--Doing Different Stuff like UPDATE FEATURES...;
END IF;

--MORE STUFF HERE and in the above IF statement --
END;

[mageri]

Or you can use a cursor like this:

DECLARE
CURSOR property_cur is
SELECT PropertyID
FROM BOLDED
WHERE PropertyID = 109;

cnt NUMBER := 0;

BEGIN

FOR property_row IN property_cur LOOP
EXIT WHEN property_cur%NOTFOUND;
cnt := cnt + 1;
END LOOP;

DBMS_OUTPUT.PUT_LINE('Hits: ' || TO_CHAR(cnt));

END;
/

[ales]

Or you can use nested PL/SQL block with exception handling:

declare

  propID BOLDED.PropertyID%TYPE:=0;

BEGIN 

-- STUFF HERE --- 

begin

  SELECT PropertyID  

  INTO PropID

  FROM BOLDED 

  WHERE PropertyID = 109; 

exception

  when no_data_found then null;

end;

IF PropID=0 THEN 

--Doing Stuff like INSERT INTO FEATURES...; 

ELSE 

--Doing Different Stuff like UPDATE FEATURES...; 

END IF; 

--MORE STUFF HERE and in the above IF statement -- 

END;