Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Oracle Simple Function return data.

Posted on 2006-11-02
5
Medium Priority
?
1,232 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 200 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 200 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 800 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 800 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
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…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

618 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