oracle - pipeline function return table "inconsistent datatypes: expected %s got %s" -

i'm trying create pipeline function return table , can't seem syntax correct. i've followed examples , getting inconsistent datatypes , don't understand why. assume has in someway row getting returned in way mismatches row definition. appreciated. in advance.

drop type odsmaxvrsn_tbl; drop type odsmaxvrsn_row;   create or replace type odsmaxvrsn_row object (   audit_sls_trans_key     number ,sls_tran_key                 number ,sls_4_part_key           varchar2(50), sls_audit_rvsn_nbr      number, sls_tran_key_unaud      number ) /  create or replace type odsmaxvrsn_tbl table of odsmaxvrsn_row /  create or replace function get_odsmaxvrsn (in_dtfmt varchar2,in_start_date varchar2,in_end_date varchar2)          return odsmaxvrsn_tbl pipelined   l_row odsmaxvrsn_row; l_cursor  sys_refcursor;  l_sql    varchar2(2000);  begin      l_sql := 'with  sales ( select s.sls_trans_key,s.sls_4_part_key, nvl(s.sls_audit_rvsn_nbr,0) sls_audit_rvsn_nbr, a.audit_sls_trans_key  sls_trans s left outer join audit_sls_trans          on s.sls_4_part_key = a.sls_4_part_key , a.sls_audit_rvsn_nbr = 1     s.rec_crt_ts between to_date('''|| in_start_date ||''','''||in_dtfmt ||''') , to_date('''||in_end_date ||''','''||in_dtfmt||''')' ||'      ,  a.audit_sls_trans_key > 0         or s.sls_audit_rvsn_nbr  > 0 ) , maxrvsn ( select trn.sls_4_part_key     , to_number(max(sls_audit_rvsn_nbr)) sls_audit_rvsn_nbr     sales trn      group trn.sls_4_part_key ) , unaudited ( select  t.sls_trans_key, t.sls_4_part_key, t.sls_audit_rvsn_nbr sls_trans  t t.sls_audit_rvsn_nbr null ) select t.audit_sls_trans_key ,t.sls_trans_key ,t.sls_4_part_key ,t.sls_audit_rvsn_nbr ,u.sls_trans_key  sales t inner join maxrvsn m on m.sls_4_part_key = t.sls_4_part_key , m.sls_audit_rvsn_nbr = t.sls_audit_rvsn_nbr left outer join unaudited u on t.sls_4_part_key = u.sls_4_part_key'; sys.dbms_output.put_line( l_sql);     open l_cursor l_sql;     loop         fetch l_cursor l_row;         exit when l_cursor%notfound;          pipe row (l_row);      end loop;     close l_cursor;     return; end get_odsmaxvrsn;  select * table(get_odsmaxvrsn('yyyy-mm-dd','2017-07-25','2017-07-31')) 

maybe try one:

pipe row ( odsmaxvrsn_row(    l_row.audit_sls_trans_key,    l_row.sls_trans_key,    l_row.sls_4_part_key,    l_row.sls_audit_rvsn_nbr,    l_row.sls_trans_key)        ); 

for debugging insert

sys.dbms_output.put_line(     l_row.audit_sls_trans_key ||','||    l_row.sls_trans_key||','||    l_row.sls_4_part_key||','||    l_row.sls_audit_rvsn_nbr||','||    l_row.sls_trans_key); 

in order see whether values have correct data type.

better set input values date datatype, not varchar2, i.e.

create or replace function get_odsmaxvrsn (     in_start_date date,in_end_date date)     return odsmaxvrsn_tbl pipelined   ...      s.rec_crt_ts between :in_start_date , :in_end_date   ...  open l_cursor l_sql using in_start_date, in_end_date; 

or in case have insist on varchar2, try

create or replace function get_odsmaxvrsn (     in_dtfmt varchar2,in_start_date varchar2,in_end_date varchar2)     return odsmaxvrsn_tbl pipelined   ...      s.rec_crt_ts between :in_start_date , :in_end_date   ...  open l_cursor l_sql using      to_date(in_start_date, in_dtfmt),      to_date(in_end_date, in_dtfmt);