Result set to REF CURSOR

Hello All,

I have a stored procedure in which I construct a SQL statement on fly and execute it (dynamic SQL). Now, I want to take the result set returned by this dynamic SQL (Column list is always the same) into an output variable of the stored procedure.

Can someone tell me give a sample piece of code to achieve this? Your help is greatly appreciated.

Thanks,
Desi Rookie

http://govt.oracle.com/~tkyte/ResultSets/index.html

Here is my problem in detail. Any help will be very much appreciated.

Thanks,
Desi Rookie

CREATE OR REPLACE PROCEDURE Test_SP
(
pi_Emp_Name IN VARCHAR2
, po_Emp_Details OUT {How do I declare an output paramter to hold multiple rows & columns???} )
)

AS
v_SQL_String VARCHAR2(2000);
v_Dummy integer;

BEGIN
v_SQL_String := 'SELECT Employee_ID, Employee_Name, Emp_Address1, Emp_Address2, Emp_City, Emp_State, Emp_Zip FROM Employee WHERE UPPER (Employee_Name) LIKE ''%' || UPPER (pi_Emp_Name) || '%''';

-- Now I need to execute the above SQL string and take the output of it into po_Emp_Details (Please refer to the output parameter of the proc)
OPEN po_Emp_Details FOR
EXECUTE v_SQL_String;

END; -- Test_SP;

did you even read the article I suggested? It's all laid out right there...