Solved

Oracle Stored Procedure, returning recordset

Posted on 2007-11-21
8
5,440 Views
Last Modified: 2013-12-19
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
Comment
Question by:PAG_Promax
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 20332636
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
 

Author Comment

by:PAG_Promax
ID: 20332689
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
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 20332700
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:PAG_Promax
ID: 20332704
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
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 20332715
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
 
LVL 27

Accepted Solution

by:
sujith80 earned 500 total points
ID: 20332876
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
 

Author Comment

by:PAG_Promax
ID: 20332885
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
 

Author Comment

by:PAG_Promax
ID: 20332888
Thanks!  I'll take a look at that new code and get back to you soon.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Oracle SQL - Query help 7 78
form builder not starting 3 72
Creation date for a PDB 5 63
Pivoting oracle table 9 73
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to take different types of Oracle backups using RMAN.

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question