Oracle Server - Returning Recordsets fro Stored Procedure

Author

Returning Recordsets fro Stored Procedure

bubba

2005-05-26, 8:23 pm

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_LEVELDECSRIPTIO
N,
NVL (sec_level2_list.ID, 0) lvl2_list_id,
sec_level2_list.levelname lvl2_levelname,
sec_level2_list.leveldecsription lvl2_leveldecsriptio
n,
NVL (sec_level3_list.ID, 0) lvl3_list_id,
sec_level3_list.levelname lvl3_levelname,
sec_level3_list.leveldecsription lvl3_leveldecsriptio
n,
NVL (sec_level4_list.ID, 0) lvl4_LIST_id,
sec_level4_list.levelname lvl4_levelname,
sec_level4_list.leveldecsription lvl4_leveldecsriptio
n
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_i
d = sec_level1_list.ID(+))
AND (sec_level2. fk_sec_level2_list_i
d = 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_i
d = sec_level3_list.ID(+))
AND (sec_level3.ID = sec_level4.fk_level3_id(+))
AND (sec_level4. fk_sec_level4_list_i
d = 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_LEVELDECSRIPTIO
N,
NVL (sec_level2_list.ID, 0) lvl2_list_id,
sec_level2_list.levelname lvl2_levelname,
sec_level2_list.leveldecsription lvl2_leveldecsriptio
n,
NVL (sec_level3_list.ID, 0) lvl3_list_id,
sec_level3_list.levelname lvl3_levelname,
sec_level3_list.leveldecsription lvl3_leveldecsriptio
n,
NVL (sec_level4_list.ID, 0) lvl4_LIST_id,
sec_level4_list.levelname lvl4_levelname,
sec_level4_list.leveldecsription lvl4_leveldecsriptio
n
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_i
d = sec_level1_list.ID(+))
AND (sec_level2. fk_sec_level2_list_i
d = 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_i
d = sec_level3_list.ID(+))
AND (sec_level3.ID = sec_level4.fk_level3_id(+))
AND (sec_level4. fk_sec_level4_list_i
d = 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.

bubba

2005-05-26, 8:23 pm

By the way, when I substiture constanmts for the variables and drop the two
parameters, this quesry works exactly as expected.

--

----------------------------------------------------
This mailbox protected from junk email by MailFrontier Desktop
from MailFrontier, Inc. http://info.mailfrontier.com

"bubba" <bubba@bubba.com> wrote in message
news: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_LEVELDECSRIPTIO
N,
> NVL (sec_level2_list.ID, 0) lvl2_list_id,
> sec_level2_list.levelname lvl2_levelname,
> sec_level2_list.leveldecsription lvl2_leveldecsriptio
n,
> NVL (sec_level3_list.ID, 0) lvl3_list_id,
> sec_level3_list.levelname lvl3_levelname,
> sec_level3_list.leveldecsription lvl3_leveldecsriptio
n,
> NVL (sec_level4_list.ID, 0) lvl4_LIST_id,
> sec_level4_list.levelname lvl4_levelname,
> sec_level4_list.leveldecsription lvl4_leveldecsriptio
n
> 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_i
d = sec_level1_list.ID(+))
> AND (sec_level2. fk_sec_level2_list_i
d = 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_i
d = sec_level3_list.ID(+))
> AND (sec_level3.ID = sec_level4.fk_level3_id(+))
> AND (sec_level4. fk_sec_level4_list_i
d = 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_LEVELDECSRIPTIO
N,
> NVL (sec_level2_list.ID, 0) lvl2_list_id,
> sec_level2_list.levelname lvl2_levelname,
> sec_level2_list.leveldecsription lvl2_leveldecsriptio
n,
> NVL (sec_level3_list.ID, 0) lvl3_list_id,
> sec_level3_list.levelname lvl3_levelname,
> sec_level3_list.leveldecsription lvl3_leveldecsriptio
n,
> NVL (sec_level4_list.ID, 0) lvl4_LIST_id,
> sec_level4_list.levelname lvl4_levelname,
> sec_level4_list.leveldecsription lvl4_leveldecsriptio
n
> 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_i
d = sec_level1_list.ID(+))
> AND (sec_level2. fk_sec_level2_list_i
d = 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_i
d = sec_level3_list.ID(+))
> AND (sec_level3.ID = sec_level4.fk_level3_id(+))
> AND (sec_level4. fk_sec_level4_list_i
d = 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.
>
>
>

DA Morgan

2005-05-26, 8:23 pm

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_LEVELDECSRIPTIO
N,
> NVL (sec_level2_list.ID, 0) lvl2_list_id,
> sec_level2_list.levelname lvl2_levelname,
> sec_level2_list.leveldecsription lvl2_leveldecsriptio
n,
> NVL (sec_level3_list.ID, 0) lvl3_list_id,
> sec_level3_list.levelname lvl3_levelname,
> sec_level3_list.leveldecsription lvl3_leveldecsriptio
n,
> NVL (sec_level4_list.ID, 0) lvl4_LIST_id,
> sec_level4_list.levelname lvl4_levelname,
> sec_level4_list.leveldecsription lvl4_leveldecsriptio
n
> 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_i
d = sec_level1_list.ID(+))
> AND (sec_level2. fk_sec_level2_list_i
d = 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_i
d = sec_level3_list.ID(+))
> AND (sec_level3.ID = sec_level4.fk_level3_id(+))
> AND (sec_level4. fk_sec_level4_list_i
d = 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_LEVELDECSRIPTIO
N,
> NVL (sec_level2_list.ID, 0) lvl2_list_id,
> sec_level2_list.levelname lvl2_levelname,
> sec_level2_list.leveldecsription lvl2_leveldecsriptio
n,
> NVL (sec_level3_list.ID, 0) lvl3_list_id,
> sec_level3_list.levelname lvl3_levelname,
> sec_level3_list.leveldecsription lvl3_leveldecsriptio
n,
> NVL (sec_level4_list.ID, 0) lvl4_LIST_id,
> sec_level4_list.levelname lvl4_levelname,
> sec_level4_list.leveldecsription lvl4_leveldecsriptio
n
> 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_i
d = sec_level1_list.ID(+))
> AND (sec_level2. fk_sec_level2_list_i
d = 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_i
d = sec_level3_list.ID(+))
> AND (sec_level3.ID = sec_level4.fk_level3_id(+))
> AND (sec_level4. fk_sec_level4_list_i
d = 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@x.washington.edu
(replace x with u to respond)

Daniel

2005-05-27, 3:23 am

To add a bit to what Daniel suggested, you should use a package, in
which a ref cursor could be defined as a package variable (i.e. not
part of a package procedure, for example). Look on asktom.oracle.com
for some examples.

HTH

Daniel

Sponsored Links