Subscribe to the RSS feed
Doing a lookup in a procedure and don't want it to die with a NO_DATA_FOUND error? You could trick it into returning a null by using an aggregation function, but that may add I/O's. You could write your own function to handle it, but isn't that a bit kludgey? Wrap the offending statement in its own block with an exception handler:
if(iLOGIN_UID = 0 or iLOGIN_UID is null) then
begin
select
LOGIN_UID
into
oReturn
from
LOGIN
where
GUID = iGUID;
exception when NO_DATA_FOUND then oReturn := null;
end;
else
oReturn := iLOGIN_UID;
end if;
Tags: plsql, oracle
2 comments:
I agree that generally you don't want to propagate NO_DATA_FOUND for single row lookups. However, I suggest that what you do is place the query inside its own function, and then design the function so that the caller of the function can decide whether or not NDF should be raised. Here is an example:
FUNCTION id_for_name (
department_name_in IN departments.department_name%TYPE
, raise_if_ndf_in IN BOOLEAN := FALSE
, ndf_value_in IN departments.department_id%TYPE := NULL
)
RETURN departments.department_id%TYPE
IS
l_return departments.department_id%TYPE;
BEGIN
SELECT department_id
INTO l_return
FROM departments
WHERE department_name = department_name_in;
RETURN l_return;
EXCEPTION
/*
User gets to interpret as "unfortunate" exception.
User can also specify a unique indicator of "row not found."
*/
WHEN NO_DATA_FOUND
THEN
IF raise_if_ndf_in
THEN
RAISE;
ELSE
RETURN ndf_value_in;
END IF;
WHEN TOO_MANY_ROWS
THEN
/* Quest Error Manager freeware availabled at www.ToadWorld.com - Downloads */
q$error_manager.raise_unanticipated
(text_in => 'Multiple rows found for department name'
, name1_in => 'DEPARTMENT_NAME'
, value1_in => department_name_in
);
END id_for_name;
/
Regards,
Steven Feuerstein
www.ToadWorld.com/SF
Well kids, there you have it from The Man himself.
Thanks for clarifying, Steven.
Post a Comment