Oracle Stored Procedure, returning recordset

Hi folks,

I'm trying to write a stored procedure in Oracle PL/SQL that'll return a recordset.  I need to be able to view the results in a grid within my application.

Can anyone help me with this?

Cheers!

PAG.
PAG_PromaxAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
sujith80Connect With a Mentor Commented:
You may use a function that returns a refcursor;
See the example below.

create or replace function test_func
return sys_refcursor
as
 l_cur sys_refcursor;
begin
 open l_cur for select * from emp;
 return l_cur;
end;
/

Use this code to test it from sqlplus.

variable x refcursor;
exec :x := test_func;
print x;
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
0
 
PAG_PromaxAuthor Commented:
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 :)
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
0
 
PAG_PromaxAuthor Commented:
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
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
0
 
PAG_PromaxAuthor Commented:
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.
0
 
PAG_PromaxAuthor Commented:
Thanks!  I'll take a look at that new code and get back to you soon.
0
All Courses

From novice to tech pro — start learning today.