Oracle Stored Procedure, returning recordset

http://www.oradev.com/ref_cursor.jsp
http://www.databasedesign-resource.com/ref-cursor.html
http://www.oracle-base.com/articles/8i/UsingRefCursorsToReturnRecordsets.php

All the 3 links have good examples with code samples for your requirement. Especially, 3 one is good and cleaner to understand.

Thanks

Thanks for the links.  I'm having troubles understanding it though.  I'll keep going, but I might be back with (a lot) more questions :)

just the below code should be fine to understand which is from the 3rd link :

a) we are declaring ref cursor as a type in a package called types
CREATE OR REPLACE PACKAGE Types AS
  TYPE cursor_type IS REF CURSOR;
END Types;
/  

b) this procedure will return the ref cursor ( p_recordset variable ) which we can use
in our calling code.

CREATE OR REPLACE
PROCEDURE GetEmpRS (p_deptno    IN  emp.deptno%TYPE,
                    p_recordset OUT Types.cursor_type) AS
BEGIN
  OPEN p_recordset FOR
    SELECT ename,
           empno,
           deptno
    FROM   emp
    WHERE  deptno = p_deptno
    ORDER BY ename;
END GetEmpRS;
/

c) we use the recordset defined by procedure GetEmpRS in the below code and print
    the records from the record set.

SET SERVEROUTPUT ON SIZE 1000000
DECLARE
  v_cursor  Types.cursor_type;
  v_ename   emp.ename%TYPE;
  v_empno   emp.empno%TYPE;
  v_deptno  emp.deptno%TYPE;
BEGIN
  GetEmpRS (p_deptno    => 30,
            p_recordset => v_cursor);
           
  LOOP
    FETCH v_cursor
    INTO  v_ename, v_empno, v_deptno;
    EXIT WHEN v_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_ename || ' | ' || v_empno || ' | ' || v_deptno);
  END LOOP;
  CLOSE v_cursor;
END;
/

Anyways, if you need help post your code and questions. Thanks

Hi,

I don't think the 3rd example is going to work, as it is returning a recordset as a parameter.  I need it to return it as the result of the procedure.  When I type in :

exec procedurename(param1, param2)

I want the result to be a recordset.

Does that make sense?  

Cheers!

PAG

in pl/sql ref cursors are very much similar to record sets.

exec procedurename(param1, param2)   -- here param2 should be a OUT variable of type ref cursor

Once the procedure is complete, you need to fetch and display/process it.

Ok.. Looks like you want something else. Tell me what is  your requirement clearly with an example ( if a table has 5 records ) what should your procedure do and what should the code which calls this procedure do ?

Thanks

OK, my client has a stack of data which has been stuffed up for one reason or another.  They use a program called Database Manager which I wrote for them years ago that allows them to execute scripts, view results in grids etc.  Its platform independant as they use it for their Access, Oracle and MSSQL databases.

I have written a stored prcoedure that will correct the data for them, but they want to view the changes before they are done and export them to Excel.  So I want the stored procedure to dump a recordset.  

So, basically what I want to do is, once I've identified the records that are knackered, I want to put them into a recordset.  I DONT WANT TO USE DBMS_OUTPUT.PUT_LINE (just so that's clear).  UNLESS this can output the details to a recordset instead of to the console.

Sorry, I forgot to click submit :)  

PAG.

Thanks!  I'll take a look at that new code and get back to you soon.