Solved

Oracle Stored Procedure, returning recordset

Posted on 2007-11-21
8
5,427 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
  • 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
 

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to recover a database from a user managed backup

759 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now