Solved

Oracle Simple Function return data.

Posted on 2006-11-02
5
1,203 Views
Last Modified: 2008-01-09
Hi,

I am looking for a simple function with a ref_cursor that returns some data, and then how to call it. Perhaps against the scott schema. This is to help me learn how to create them and then use them.

Thanks.
0
Comment
Question by:wilflife
[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
5 Comments
 
LVL 28

Assisted Solution

by:Naveen Kumar
Naveen Kumar earned 50 total points
ID: 17860139
you can see the below :
http://www.experts-exchange.com/Databases/Oracle/Q_20791913.html
http://www.experts-exchange.com/Databases/Oracle/Q_20872937.html

just read thru the url : search for ref cursor in that.  This link has an example also on how to use it :
http://www.oracle-base.com/articles/10g/StringAggregationTechniques.php 

Refer to online oracle documentation for more information.

Thanks
0
 

Author Comment

by:wilflife
ID: 17860317
I would like to be able to just call the function...

Do i have to call the function as part of another query?

For instance assuming this sample function how do i then call it?

CREATE OR REPLACE FUNCTION testDep RETURN SYS_REFCURSOR

IS

rtnval SYS_REFCURSOR;

BEGIN

OPEN v_rtn_val FOR SELECT E.JOB

FROM SCOTT.EMP E;

RETURN rtnval;

END testDep ;

 
0
 
LVL 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 50 total points
ID: 17860409
How do you call an Oracle function that returns a ref cursor?  That will depend on which tool or programming language you are using to interact with Oracle.  The Oracle client tools (Oracle Forms, Oracle Reports, etc.) don't normally use this capability of PL\SQL, since they build their own queries and results sets internally.  Where do you want to use an Oracle function that returns a ref cursor?
0
 
LVL 7

Accepted Solution

by:
Daniel Stanley earned 200 total points
ID: 17864031
here is solid example.

create table dummy(
f1 number,
f2 char(10));

drop type tab1;
drop type rec1;

create or replace type rec1 as object(
f1 number,
f2 char(10));
/



create or replace type tab1 as table of rec1;
/

create or replace function objTest
return tab1 PIPELINED
as
cursor c1 is
select * from dummy;
r1 c1%rowtype;
begin
    open c1;
    loop
    fetch c1 into r1;
    exit when c1%notfound;
            pipe row (rec1(r1.f1, r1.f2));
    end loop;
    close c1;
return;
end objTest;
/
show err

create or replace package test_pkg
is
      
type gRefCur is ref cursor return dummy%rowtype;

procedure prcTest(rv OUT test_pkg.gRefCur);

end test_pkg;
/
show err

create or replace package body test_pkg
is

procedure prcTest(rv OUT test_pkg.gRefCur)
is
begin
delete from dummy;
commit;

insert into dummy values(
1, 'Test1');
insert into dummy values(
2, 'Test2');
commit;

open rv for
select * from table(cast(objTest as tab1));

exception
when others then
raise;

end prcTest;

end test_pkg;
/
show err

--execute
SQL> var a refcursor
SQL> exec test_pkg.prctest(:a);

PL/SQL procedure successfully completed.

SQL> print a

        F1 F2
---------- ----------
         1 Test1
         2 Test2

have fun,
daniels










0
 
LVL 27

Assisted Solution

by:sujith80
sujith80 earned 200 total points
ID: 17864849
Here is a simple example that shows how to get the function results and manipulate it:

create or replace function test_func
return sys_refcursor
is
 l_cur sys_refcursor;
begin
 open l_cur for select phno from tab1;
 return l_cur;
end;
/

declare
 l_cur sys_refcursor;
 l_ph varchar2(30);
begin
 l_cur := test_func;
 loop
  fetch l_cur into l_ph;
  exit when l_cur%notfound;
  dbms_output.put_line(l_ph);
 end loop;
 close l_cur;
end;
/
0

Featured Post

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

733 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