Getting results from a stored procedure, or function

Question

I'm having trouble executing a stored procedure to get results back. Currently, I'd like to display results in the output window of toad, eclipse, or whatever else I'd be using. Later on, I want to access them from a program. Ignore the program part for now, unless it's essential.

I have this stored procedure code:

create or replace procedure pottyuserange (p_date_format in varchar2,
                                       p_start_date in varchar2,
                                       p_end_date in varchar2,
                                       p_ref_cursor out sys_refcursor)
as
begin
  open p_ref_cursor for 
    select   to_char(time_range, p_date_format) as current_date,
         lm_search,
         ao_search,
         ro_search,
         fl_search,
         total
from     (select   trunc(time_range) time_range,
                   sum(case when porta_potty = 'LM' then 1 else 0 end) as lm_search,
                   sum(case when porta_potty = 'AO' then 1 else 0 end) as ao_search,
                   sum(case when porta_potty = 'RO' then 1 else 0 end) as ro_search,
                   sum(case when porta_potty = 'FL' then 1 else 0 end) as fl_search,
                   sum(case when porta_potty in ('LM', 'AO', 'RO', 'FL') then 1 else 0 end) as total           
          from     core.date_test
          where    trunc(time_range) >= to_date(p_start_date, p_date_format)
          and      trunc(time_range) <= to_date(p_end_date, p_date_format)
          group by trunc(time_range))
    order by time_range asc;
  end pottyuserange;
/

And I'm trying to get results like this:

variable rc refcursor;

BEGIN
    pottyuserange('YYYY-MM-DD', '2008-10-1', '2010-10-12', :rc);
END;

print rc;

And this is the error I'm getting:

ORA-01722: invalid number
ORA-06512: at "core.pottyuserange", line 4
ORA-06512: at line 1

How would I correctly access this data, or turn it into a User-Defined Function? \

Here's my attempt at turning it into a function:

create or replace function pottyuserange (p_date_format in varchar2,
                                       p_start_date in varchar2,
                                       p_end_date in varchar2)
return result_set as rc sys_refcursor;

BEGIN

OPEN result_set FOR
    select   to_char(time_range, p_date_format) as current_date,
         lm_search,
         ao_search,
         ro_search,
         fl_search,
         total
from     (select   trunc(time_range) time_range,
                   sum(case when porta_potty = 'LM' then 1 else 0 end) as lm_search,
                   sum(case when porta_potty = 'AO' then 1 else 0 end) as ao_search,
                   sum(case when porta_potty = 'RO' then 1 else 0 end) as ro_search,
                   sum(case when porta_potty = 'FL' then 1 else 0 end) as fl_search,
                   sum(case when porta_potty in ('LM', 'AO', 'RO', 'FL') then 1 else 0 end) as total           
          from     core.date_test
          where    trunc(time_range) >= to_date(p_start_date, p_date_format)
          and      trunc(time_range) <= to_date(p_end_date, p_date_format)
          group by trunc(time_range))
    order by time_range asc;
    return result_set;
  end pottyuserange;
/

The error I'm getting with the above is: PLS-00201: identifier 'RESULT_SET' must be declared

EDIT: TIME_RANGE is a DATE. PORTA_POTTY is a varchar2.

Answer 1

(Method 1) Create own collection type(s) and change function to return object of this type:

 create or replace type tp_potty as object (
  current_date varchar2(20), lm_search number(6), ao_search number(6), 
  ro_search number(6), fl_search number(6), total number(6));

create or replace type tp_potty_tbl as table of tp_potty;

create or replace function pottyuserange (p_date_format in varchar2,
                                          p_start_date in varchar2, 
                                          p_end_date in varchar2)
                                   return tp_potty_tbl is
  potty_tbl tp_potty_tbl;
begin
  select cast(multiset(
      select to_char(time_range, p_date_format) as current_date,
             lm_search, ao_search, ro_search, fl_search, total
      from  (select    trunc(time_range) time_range,
                       sum(case when porta_potty = 'LM' then 1 else 0 end) lm_search,
                       sum(case when porta_potty = 'AO' then 1 else 0 end) ao_search,
                       sum(case when porta_potty = 'RO' then 1 else 0 end) ro_search,
                       sum(case when porta_potty = 'FL' then 1 else 0 end) fl_search,
                       sum(case when porta_potty in ('LM', 'AO', 'RO', 'FL') 
                                then 1 else 0 end) total
              from     date_test
              where    trunc(time_range) >= to_date(p_start_date, p_date_format)
              and      trunc(time_range) <= to_date(p_end_date, p_date_format)
              group by trunc(time_range))
        order by time_range asc) as tp_potty_tbl) into potty_tbl from dual;
    return potty_tbl;
end pottyuserange;

Now run:

select * from table(pottyuserange('yyyy-mm-dd', '2015-10-10', '2015-10-15'))

CURRENT_DATE         LM_SEARCH AO_SEARCH RO_SEARCH FL_SEARCH   TOTAL
-------------------- --------- --------- --------- --------- -------
2015-10-12                   1         0         0         0       1
2015-10-13                   1         0         0         0       1
2015-10-14                   0         0         0         0       0

(Method 2) List output of cursor returned from function in PL/SQL block like here:

create or replace function potty2 (p_date_format in varchar2,
  p_start_date in varchar2,p_end_date in varchar2) return sys_refcursor is

  result_set sys_refcursor;

BEGIN

OPEN result_set FOR
    select   to_char(time_range, p_date_format) as current_date,
         lm_search, ao_search, ro_search, fl_search, total
from     (select   trunc(time_range) time_range,
                   sum(case when porta_potty = 'LM' then 1 else 0 end) as lm_search,
                   sum(case when porta_potty = 'AO' then 1 else 0 end) as ao_search,
                   sum(case when porta_potty = 'RO' then 1 else 0 end) as ro_search,
                   sum(case when porta_potty = 'FL' then 1 else 0 end) as fl_search,
                   sum(case when porta_potty in ('LM', 'AO', 'RO', 'FL') 
                            then 1 else 0 end) as total           
          from     date_test
          where    trunc(time_range) >= to_date(p_start_date, p_date_format)
          and      trunc(time_range) <= to_date(p_end_date, p_date_format)
          group by trunc(time_range))
    order by time_range asc;
    return result_set;
end potty2;

PL/SQL block:

declare 
  cur sys_refcursor;
  v1 varchar2(20); v2 number(6); v3 number(6); v4 number(6); v5 number(6); v6 number(6);
begin
  cur := potty2('yyyy-mm-dd', '2015-10-10', '2015-10-15');
  loop
    fetch cur into v1, v2, v3, v4, v5, v6;
    exit when cur%notfound;
    dbms_output.put_line(v1||' '||v2||' '||v3||' '||v4||' '||v5||' '||v6);
  end loop;
  close cur; 
end;

My answer is based on this article: PL/SQL 101 : Understanding Ref Cursors