Stored Procedure question

[jambu]

I want to create a stored procedure from a very slow view. I am more comfortable doing this in sql server but I am using oracle 9 for this project.

My problem is that I am getting errors that seem to imply that I can not do what I want.

The first error implied that I cannot use select in a stored procedure without declaring variables and using 'select into' to populate them.

Question 1, can I use a select statement in an Oracle stored procedure and retrieve multiple rows?

The next error I got was that the multiple rows caused a problem and I have to use a cursor to get this multiple data.

Question 2, If I use a cursor will it allow the stored proc to return multiple rows?

Question 3, If the answers to the above are no, what alternatives do I have, basically I want something that behaves like a sql server stored procedure where you just have to type in a select statement.

Any help appreciated, simple example code would be nice, thanks.

 

[SantaMufasa]

Jambu,

A1. Yes, you can use a SELECT statement in an Oracle stored procedure to retrieve multiple rows. Among your choices to achieve this result are CURSORS.

A2. Yes, A CUSOR allows you to process multiple rows. What you do with those rows is a function of your code.

A3. Not applicable.

We would be happy to provide sample code, but it would be more meaningful if we tailored the example specifically to your needs. Is your preliminary code (or pseudo-code) such that you can post it here?

Mufasa
(aka Dave of Sandy, Utah, USA @ 19:46 (16Sep04) UTC (aka "GMT" and "Zulu"), 12:46 (16Sep04) Mountain Time)

 

[yujwal]

Can I have two open cursors in a stored procedure in oracle 9i.

Thanks,
yujwal.

 

[SantaMufasa]

Yujwal,

You may have as many open cursors as you want/need, provided the total number of cursors for your entire instance does not exceed the value of your instance's parameter setting, "open_cursors".

Mufasa
(aka Dave of Sandy, Utah, USA @ 22:56 (16Sep04) UTC (aka "GMT" and "Zulu"), 15:56 (16Sep04) Mountain Time)

 

[jambu]

Thanks for the replies.

The sql is very long winded and wouldn't help very much.

Could someone post a very simple example of how to do this?

Thanks.

 

[cappmgr]

simple sample of returning two cursors

CREATE OR REPLACE PACKAGE Pkg_Cappmgr
AS
TYPE ref_cursor_type IS REF CURSOR;   
PROCEDURE GetProgram2Curs(p_i_ProgramCode   IN MARTY_TESTTABLE.PROGRAM_CODE%TYPE
			,p_i_ProgramName       IN MARTY_TESTTABLE.PROGRAM_NAME%TYPE					   
			,c_o_RefProgramByCode  OUT  ref_cursor_type
			,c_o_RefProgramByName  OUT  ref_cursor_type);								 

						 
END Pkg_Cappmgr;
/
CREATE OR REPLACE PACKAGE BODY Pkg_Cappmgr
AS
PROCEDURE GetProgram2Curs(p_i_ProgramCode   IN MARTY_TESTTABLE.PROGRAM_CODE%TYPE
			,p_i_ProgramName       IN MARTY_TESTTABLE.PROGRAM_NAME%TYPE					   
			,c_o_RefProgramByCode  OUT  ref_cursor_type
			,c_o_RefProgramByName  OUT  ref_cursor_type)								 
AS
BEGIN
OPEN c_o_RefProgramByCode
FOR							   			
SELECT  PROGRAM_CODE REFPROGRAMCODE
       ,NVL(PROGRAM_NAME, ' ') REFPROGRAMNAME 		
FROM MARTY_TESTTABLE 
WHERE PROGRAM_CODE = p_i_ProgramCode
ORDER BY PROGRAM_NAME ASC;
OPEN c_o_RefProgramByName
FOR							   			
SELECT  PROGRAM_CODE REFPROGRAMCODE
	   ,NVL(PROGRAM_NAME, ' ') REFPROGRAMNAME 		
FROM MARTY_TESTTABLE 
WHERE PROGRAM_NAME = p_i_ProgramName
ORDER BY PROGRAM_NAME ASC;  	 				 	 		   					
END GetProgram2Curs;
END Pkg_Cappmgr;
/
[code]
to test
[code]
SQL> var c1 refcursor;
SQL> var c2 refcursor;
SQL> exec pkg_cappmgr.GetProgram2Curs'2100', 'Clinic', :c1, :c2);
SQL> print c1;
SQL> print c2;

hth,
Marty