Problem with variable value being lost

From: 00steve <steven_macleod_at_hotmail.com>
Date: 14 Mar 2007 06:29:55 -0700
Message-ID: <1173878995.455018.93530@p15g2000hsd.googlegroups.com>

Im having an issue with the following code (snippet posted):

SELECT ewpm_project_id INTO pro_id FROM ewpm_work_package WHERE ewpm_work_package_id = wp_id;

--can return pro_id here

SELECT ewpm_work_package_prefix, LEVEL INTO parent_prefix, parent_level

FROM    ewpm_work_package
WHERE   ewpm_project_id = temp_pro_id
AND     ewpm_work_package_id = wp_id

START WITH ewpm_work_package_id = pack_id CONNECT BY PRIOR ewpm_work_package_id = ewpm_work_package_parent_id;

RETURN pro_id;

The pro_id value is correctly set during the first select statement, I can return the pro_id in the commented position. However, I can not return the value after the second select statement. All I am getting back is an empty string. If I remove the START WITH.. CONNECT BY clauses in the second query, the pro_id value is returned correcly. Has anyone experienced this before, or know what could be causing it?

thanks Received on Wed Mar 14 2007 - 08:29:55 CDT