Re: Returning Recordsets fro Stored Procedure

bubba wrote:
> 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.

http://www.psoug.org
click on Morgan's Library
click on Ref Cursors

-- 
Daniel A. Morgan
Relational theory is not something that is simply a nice-to-have.
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)