Solved

Passing a SYS_REFCURSOR to a function

Posted on 2013-12-27
4
641 Views
Last Modified: 2014-01-02
Hi all,

I haven't been able to find quite what I'm trying to achieve in the archives here.  This is my test code:

create or replace package pkg_test
as
  function sf_test1
  return varchar2;

  function sf_test2
  return varchar2
end pkg_test;


create or replace package body pkg_test
as
  function sf_test 
  (
    p_cursor in out sys_refcursor
  )
  return varchar2
  is
    l_val   varchar2(10);
    l_string varchar2(1000);
  begin
    loop
      fetch p_cursor into l_val;
      exit when p_cursor%notfound;
      
      l_string := l_string || ' ' || l_val;
    end loop;
    close p_cursor;
    
    return l_string;
  end;
  
  function sf_test1
  return varchar2
  is
    l_result  varchar2(1000);
  begin
    l_result := sf_test(cursor(select 'A' from dual));
    return l_result;
  end sf_test1;

  function sf_test2
  return varchar2
  is
    l_result  varchar2(1000);
  begin
    l_result := sf_test(cursor(select 'B' from dual));
    return l_result;
  end sf_test2;
end pkg_test;

Open in new window


So I should be able to call
select pkg_test.sf_test1 from dual;

Open in new window

and get 'A', or
select pkg_test.sf_test2 from dual;

Open in new window

and get 'B'.  But the code isn't compiling - it says "subquery not allowed in this context" on the lines where sf_test1 and sf_test2 are calling sf_test.

What am I doing wrong?
0
Comment
Question by:Bitley
[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
  • 2
4 Comments
 
LVL 20

Expert Comment

by:flow01
ID: 39743344
Yes , but i think de CURSOR construction does not work  in this place.

You can create youre own  function :


create or replace package body pkg_test
as
  function sf_test
  (
    p_cursor in sys_refcursor  -- you don't need the out : the cursor is closed when the function is finished
  )
  return varchar2
  is
    l_val   varchar2(10);
    l_string varchar2(1000);
  begin
    loop
      fetch p_cursor into l_val;
      exit when p_cursor%notfound;
     
      l_string := l_string || ' ' || l_val;
    end loop;
    close p_cursor;
   
    return l_string;
  end;
 
  function open_ref_cursor(v_statement varchar2) return sys_refcursor
  is
   v_cursor sys_refcursor;
  begin
    open v_cursor for v_statement;
    return v_cursor;
  end;  
 
  function sf_test1
  return varchar2
  is
    l_result  varchar2(1000);
  begin
    l_result := sf_test(open_ref_cursor(q'{select 'A' from dual}'));
    return l_result;
  end sf_test1;

  function sf_test2
  return varchar2
  is
    l_result  varchar2(1000);
  begin
    l_result := sf_test(open_ref_cursor(q'{select 'B' from dual}'));
    return l_result;
  end sf_test2;
end pkg_test;

(the q-quote mechanisme makes it possible to avoid escaping ' with double '' in de statement)
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 250 total points
ID: 39743977
The above code looks like it will work but I didn't test it.

What you have will work with one tweak mentioned above (remove the OUT on the parameter) and change the result into a SELECT instead of a direct assignment.  I also had to add sf_test as a public function.

create or replace package pkg_test
as
  function sf_test( p_cursor in sys_refcursor) return varchar2;

  function sf_test1
  return varchar2;

  function sf_test2
  return varchar2;
end pkg_test;
/

show errors


create or replace package body pkg_test
as
  function sf_test 
  (
    p_cursor in sys_refcursor
  )
  return varchar2
  is
    l_val   varchar2(10);
    l_string varchar2(1000);
  begin
    loop
      fetch p_cursor into l_val;
      exit when p_cursor%notfound;
      
      l_string := l_string || ' ' || l_val;
    end loop;
    close p_cursor;
    
    return l_string;
  end;
  
  function sf_test1
  return varchar2
  is
    l_result  varchar2(1000);
  begin
    select pkg_test.sf_test(cursor(select 'A' from dual)) into l_result from dual;
    return l_result;
  end sf_test1;

  function sf_test2
  return varchar2
  is
    l_result  varchar2(1000);
  begin
    select pkg_test.sf_test(cursor(select 'B' from dual)) into l_result from dual;
    return l_result;
  end sf_test2;
end pkg_test;
/

show errors

select pkg_test.sf_test1 from dual;
                                  

Open in new window

0
 

Author Comment

by:Bitley
ID: 39746226
Thanks flow01!

You answered the question I asked, but I'm wondering if my question was stated in the way something like this is usually done - it seems simpler to open the cursor in the shared function where the processing occurs.  I reworked your example as follows, which compiles and runs great.  If you or anybody has a comment please let me know, otherwise I'll close out this issue in a couple of days.

Thanks for the q-quote hint!

create or replace package pkg_test
as
  function sf_test1
  return varchar2;
  
  function sf_test2
  return varchar2;
end pkg_test;
/

create or replace package body pkg_test
as
  function sf_test
  (
    p_stmt varchar2
  )
  return varchar2
  is
    l_cursor  sys_refcursor;
    l_val     varchar2(10);
    l_string  varchar2(1000);
  begin
    open l_cursor for p_stmt;
    loop
      fetch l_cursor into l_val;
      exit when l_cursor%notfound;
      l_string := l_string || ' ' || l_val;
    end loop;
    close l_cursor;
    return l_string;
  end sf_test;
  
  
  function sf_test1
  return varchar2
  is
    l_result varchar2(1000);
  begin
    l_result := sf_test(q'{select 'A' from dual}');
    return l_result;
  end sf_test1;
  
  
  function sf_test2
  return varchar2
  is
    l_result varchar2(1000);
  begin
    l_result := sf_test(q'{select 'B' from dual}');
    return l_result;
  end sf_test2;
end pkg_test;
/

select pkg_test.sf_test1 from dual;
select pkg_test.sf_test2 from dual;

Open in new window

0
 
LVL 20

Accepted Solution

by:
flow01 earned 250 total points
ID: 39746533
When I saw your code I was thinking you where in the proces of discovering possibilities of ref_cursor constructions.
So I didn't comment on practical use (there a more easy ways to get 'A'  and 'B' as result).
Your last solution works with less code.
Unless you need additional debugging different for function sf_test1 and sf_test2, you can limit the code of both functions to passing the query:

  function sf_test1
  return varchar2
  is
  begin
    return sf_test(q'{select 'A' from dual}');
  end sf_test1;
 
 
  function sf_test2
  return varchar2
  is
  begin
    return sf_test(q'{select 'B' from dual}');
  end sf_test2;
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to Comment Out Lines of Code in a Pass Through Query In MS Access 2016 19 96
Read XML values 8 66
return value in based on value passed 6 48
Oracle Date add 9 28
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to recover a database from a user managed backup
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

738 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