Solved

Oracle Simple Function return data.

Posted on 2006-11-02
5
1,181 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
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 34

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

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
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…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

760 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

21 Experts available now in Live!

Get 1:1 Help Now