[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5460
  • Last Modified:

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.
0
PAG_Promax
Asked:
PAG_Promax
  • 4
  • 3
1 Solution
 
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
 
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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
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
 
sujith80Commented:
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
 
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now