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

Passing a SYS_REFCURSOR to a function

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
Bitley
Asked:
Bitley
  • 2
2 Solutions
 
flow01Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
BitleyAuthor Commented:
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
 
flow01Commented:
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

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.

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