• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1241
  • Last Modified:

Oracle Simple Function return data.

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
wilflife
Asked:
wilflife
4 Solutions
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
 
wilflifeAuthor Commented:
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
 
Mark GeerlingsDatabase AdministratorCommented:
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
 
Daniel StanleyDatabase engineerCommented:
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
 
SujithData ArchitectCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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