Returning Recordsets fro Stored Procedure

From: bubba <bubba_at_bubba.com>
Date: Thu, 26 May 2005 16:20:08 -0500
Message-ID: <119cfcarl7smg6e@corp.supernews.com>

I am new to Oracle although I have done SQL Server for a number of years. I feel silly for asking such a basic question, but I have looked through anumber of books and cannot find a straighforward example opf what I need to do.

I need to call a procedure from another procedure and return the recordset from the second procedure to the first. The first procedure takes some parameters, decides which subsequent procedure to call, and calls the second procedure with some parameters. The resulting record set needs to pass up from the second procedure to the first which will be used to populate some drop down boxes.

The second procedure is as follows:

CREATE OR REPLACE PROCEDURE TESTPROC_LVL2 (
CV_RESULT IN OUT SYS_REFCURSOR,
USERID INTEGER,
PARENT_LEVEL_KEY INTEGER
)

AS
BEGIN
DECLARE
v_row_count integer := 0;
V_ROW_TOT INTEGER := 0;
--Check to see if there are any keys for this group other than zero CURSOR CHKROWS IS
SELECT COUNT(*) AS ROWTOT FROM
(SELECT sec_users.ID user_id, sec_users.username, NVL (sec_level1_list.ID, 0) lvl1_list_id, sec_level1_list.levelname LVL1_LEVELNAME,
sec_level1_list.leveldecsription LVL1_LEVELDECSRIPTION, NVL (sec_level2_list.ID, 0) lvl2_list_id, sec_level2_list.levelname lvl2_levelname, sec_level2_list.leveldecsription lvl2_leveldecsription, NVL (sec_level3_list.ID, 0) lvl3_list_id, sec_level3_list.levelname lvl3_levelname, sec_level3_list.leveldecsription lvl3_leveldecsription, NVL (sec_level4_list.ID, 0) lvl4_LIST_id, sec_level4_list.levelname lvl4_levelname, sec_level4_list.leveldecsription lvl4_leveldecsription FROM sec_users,

sec_level1,
sec_level1_list,
sec_level2,
sec_level2_list,
sec_level3,
sec_level3_list,
sec_level4,
sec_level4_list

WHERE ( (sec_users.ID = sec_level1.fk_user_id)

AND (sec_level1.fk_sec_level1_list_id = sec_level1_list.ID(+))
AND (sec_level2.fk_sec_level2_list_id = sec_level2_list.ID(+))
AND (sec_level1.ID = sec_level2.fk_level1_id(+))
AND (sec_level2.ID = sec_level3.fk_level2_id(+))
AND (sec_level3.fk_sec_level3_list_id = sec_level3_list.ID(+))
AND (sec_level3.ID = sec_level4.fk_level3_id(+))
AND (sec_level4.fk_sec_level4_list_id = sec_level4_list.ID(+))

AND SEC_USERS.ID=USERID
AND SEC_LEVEL1_LIST.ID=PARENT_LEVEL_KEY
)

ORDER BY sec_users.ID ASC,

sec_level1_list.ID ASC,
sec_level2_list.ID ASC,
sec_level3_list.ID ASC,
sec_level4_list.ID ASC)

WHERE lvl2_list_id <> 0;
BEGIN
OPEN CHKROWS;
FETCH CHKROWS INTO V_ROW_TOT;
v_row_count := V_ROW_TOT;
CLOSE CHKROWS;
IF V_ROW_TOT > 0
-- This indicates that the user has some restirctions on this group, so only provide a list
-- of valid items from the security table THEN
OPEN CV_RESULT FOR
SELECT sec_users.ID user_id, sec_users.username, NVL (sec_level1_list.ID, 0) lvl1_list_id, sec_level1_list.levelname LVL1_LEVELNAME,
sec_level1_list.leveldecsription LVL1_LEVELDECSRIPTION, NVL (sec_level2_list.ID, 0) lvl2_list_id, sec_level2_list.levelname lvl2_levelname, sec_level2_list.leveldecsription lvl2_leveldecsription, NVL (sec_level3_list.ID, 0) lvl3_list_id, sec_level3_list.levelname lvl3_levelname, sec_level3_list.leveldecsription lvl3_leveldecsription, NVL (sec_level4_list.ID, 0) lvl4_LIST_id, sec_level4_list.levelname lvl4_levelname, sec_level4_list.leveldecsription lvl4_leveldecsription FROM sec_users,

sec_level1,
sec_level1_list,
sec_level2,
sec_level2_list,
sec_level3,
sec_level3_list,
sec_level4,
sec_level4_list

WHERE ( (sec_users.ID = sec_level1.fk_user_id)

AND (sec_level1.fk_sec_level1_list_id = sec_level1_list.ID(+))
AND (sec_level2.fk_sec_level2_list_id = sec_level2_list.ID(+))
AND (sec_level1.ID = sec_level2.fk_level1_id(+))
AND (sec_level2.ID = sec_level3.fk_level2_id(+))
AND (sec_level3.fk_sec_level3_list_id = sec_level3_list.ID(+))
AND (sec_level3.ID = sec_level4.fk_level3_id(+))
AND (sec_level4.fk_sec_level4_list_id = sec_level4_list.ID(+))
AND SEC_USERS.ID=USERID

AND sec_level2_list.ID <> 0
AND sec_level1_list.ID=PARENT_LEVEL_KEY
)

ORDER BY sec_users.ID ASC,

sec_level1_list.ID ASC,
sec_level2_list.ID ASC,
sec_level3_list.ID ASC,
sec_level4_list.ID ASC;

END IF;
END;
END;
/

I have not been able to create a calling procedure that can successfully call this proc. I tried using Crystal Reports to call it and then list the return values, but I get a message about the wrong number/type of parameters. Crystal will prompt me correctly for the values.

I really don't care so much about the Crystal connection, but I cannot seem to figure out how to call this from another procedure either, I get a similar error. Received on Thu May 26 2005 - 16:20:08 CDT