Oracle ORA-01001: 使用返回ref游标的函数时游标无效。

Hi Team,

I am getting these errors 
(1 - ORA-00932: inconsistent datatypes: expected - got -

 2 - ORA-01001: invalid cursor) 
 
when I am using function returning ref cursor.
Can anyone help me in this.

These are the efforts which I have taken to implement this.

1. I have created a function returning refcursor.


create or replace function "FUNC_ALLOCATION_DASHBOARD"(start_date in DATE, end_date in DATE) return SYS_REFCURSOR
IS
l_ret_rc SYS_REFCURSOR;
ss_interval number;
dayInterval number; 
ss_qry varchar2(20000); 
ss_qry_main varchar2(1000);
ss_qry_from varchar2(1000); 
ss_qry_where varchar2(1000);
ss_qry_groupBy varchar2(1000); 
ss_qry_build_alloc varchar2(20000); 
--ss_allocation_d varchar2(20000);
ss_dt date;
end_dt date;
v_startdate date;
total_days number;
cntr number;
BEGIN
 ss_qry_main := 'select ae.supervisorid,(select m.fname||'' ''||m.lname from resource m where m.srid = ae.supervisorid) "Manager",level,ae.srid,ae.fname||'' ''||ae.lname "Resource"';
 ss_qry_from := ' from detailstable ad, resource ae ';
 ss_qry_where := ' Where ae.ENDDATE IS NULL and ae.srid = ad.employeeid(+) start with ae.srid = (select aceemp.srid from resource aceemp where aceemp.fname||'' ''||aceemp.lname = ''Justin Todd'') connect by prior ae.srid = ae.supervisorid';
 ss_qry_groupBy := ' group by ae.supervisorid,level,ae.srid,ae.fname,ae.lname order by level';    
 cntr := 1;
 v_startdate := start_date;
 total_days := round((end_date - start_date)/7,0);
 for cntr in 1..total_days loop 
        --end_dt := ss_dt + 6;
        ss_qry_build_alloc := ss_qry_build_alloc ||', COALESCE(sum(case when ad.startdate <= '|| to_date(to_char(start_date, 'dd-mon-yyyy'),'dd-mon-yyyy') ||' and ad.enddate >= '|| to_date(to_char(end_date, 'dd-mon-yyyy'),'dd-mon-yyyy') ||'then round(ad.weeklyallocpercent*100,0) end),0) '||' "Wk-'|| to_char(to_date(v_startdate, 'MM/DD/YYYY'),'WW') ||'"';
        v_startdate := v_startdate + 6;
        end loop;
     ss_qry := ss_qry_main||ss_qry_build_alloc||ss_qry_from||ss_qry_where||ss_qry_groupBy;
    --return ss_qry;
    OPEN l_ret_rc FOR ss_qry; 
    return l_ret_rc;
   exception
       when others then
       dbms_output.put_line(sqlerrm);
       return null;
end FUNC_ALLOCATION_DASHBOARD;

2. Created table of object type.

CREATE OR REPLACE TYPE  "ALLOCATION_TYPE" as OBJECT
(
  supervisorid number
  , mgr varchar2(100)
  , hlevel number
  , srid number
  , employee varchar2(100)
  , week1 number
  , week2 number
  , week3 number
  , week4 number
  , week5 number
  , week6 number
  , week7 number
  , week8 number
  , week9 number
  , week10 number
  , week11 number
  , week12 number
  , week13 number
  , week14 number
  , week15 number
  , week16 number
  , week17 number
  , week18 number
  , week19 number
  , week20 number
  , week21 number
  , week22 number
  , week23 number
  , week24 number
  , week25 number
  , week26 number
  , week27 number
  , week28 number
  , week29 number
  , week30 number
  , week31 number
  , week32 number
  , week33 number
  , week34 number
  , week35 number
  , week36 number
  , week37 number
  , week38 number
  , week39 number
  , week40 number
  , week41 number
  , week42 number
  , week43 number
  , week44 number
  , week45 number
  , week46 number
  , week47 number
  , week48 number
  , week49 number
  , week50 number
  , week51 number
  , week52 number
  , week53 number
)
/

3. CREATE OR REPLACE TYPE  "ALLOCATION_TAB_TYPE" IS TABLE OF allocation_type
/

4. Created a pipelined function

create or replace FUNCTION allocation_dashboard_pipe_fn( i_cursor SYS_REFCURSOR)
    RETURN allocation_tab_type PIPELINED IS
    l_row allocation_type := allocation_type(NULL, NULL, NULL, NULL, NULL, NULL, NULL,null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null);
BEGIN
    LOOP
        FETCH i_cursor INTO l_row.supervisorid, l_row.mgr, l_row.hlevel, l_row.srid, l_row.employee, l_row.week1, l_row.week2, l_row.week3, l_row.week4, l_row.week5,l_row.week6, l_row.week7, l_row.week8, l_row.week9, l_row.week10, l_row.week11, l_row.week12, l_row.week13, l_row.week14, l_row.week15, l_row.week16, l_row.week17, l_row.week18, l_row.week19, l_row.week20, l_row.week21, l_row.week22, l_row.week23, l_row.week24, l_row.week25, l_row.week26, l_row.week27, l_row.week28, l_row.week29, l_row.week29, l_row.week30, l_row.week31, l_row.week32, l_row.week33, l_row.week34, l_row.week35, l_row.week36, l_row.week37, l_row.week38, l_row.week39, l_row.week40, l_row.week41, l_row.week42, l_row.week43,
l_row.week44, l_row.week45, l_row.week46, l_row.week47, l_row.week48, l_row.week49, l_row.week50, l_row.week51, l_row.week52, l_row.week53;
        EXIT WHEN i_cursor%NOTFOUND;
        PIPE ROW( l_row);
    END LOOP;
    CLOSE i_cursor;
    RETURN;
END;

5. Then call function in two ways 
  - SELECT * FROM TABLE(allocation_dashboard_pipe_fn(func_allocation_dashboard(to_date('01-jul-2017','dd-mon-yyyy'),to_date('31-jul-2017','dd-mon-yyyy'))));
      - Error no 2 Invalid cursor.

   
   Please help me I am not getting where I am doing wrong.

   exception
       when others then
       dbms_output.put_line(sqlerrm);