Cursor

Hi

From performance point of view isn't Scenario - II a better option, coz we aren't using LEFT OUTER JOIN.

Scenario - I

Oracle - 9i ==> Join is done using Keywords

OPEN o_r85_profile_cur FOR
SELECT DECODE(pb.profile_exists_flg
, 1
, pb.profile_id
, NULL) l_profile_id
, r85.active_flg
, r85.inactive_dt
, r85.r85_profile_ver
FROM profile_base pb
LEFT OUTER JOIN
r85_profile r85
ON pb.profile_id = r85.profile_id
WHERE pb.entity_id = i_entity_id
AND pb.profile_type = ao_constants.c_r85_profile;

Oracle - 8i ==> Join is done using + operator

OPEN o_r85_profile_cur FOR
SELECT DECODE(pb.profile_exists_flg
, 1
, pb.profile_id
, NULL) l_profile_id
, r85.active_flg
, r85.inactive_dt
, r85.r85_profile_ver
FROM profile_base pb
, r85_profile r85
WHERE pb.entity_id = i_entity_id
AND pb.profile_type = ao_constants.c_r85_profile
AND pb.profile_id = r85.profile_id(+);

Scenario - II

BEGIN

SELECT profile_id
INTO l_profile_id
FROM profile_base
WHERE entity_id = i_entity_id
AND profile_type = ao_constants.c_r85_profile
AND profile_exists_flg = ao_constants.c_profile_exists_yes;

EXCEPTION
WHEN NO_DATA_FOUND THEN
l_profile_id := 0;
END;

l_section := 'Open cur o_r85_profile_cur';

OPEN o_r85_profile_cur FOR
SELECT profile_id
, active_flg
, inactive_dt
, r85_profile_ver
FROM r85_profile
WHERE profile_id = l_profile_id;