Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1236
  • 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
 
sujith80Commented:
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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