Re: PLS-00386: type mismatch found at '...' between FETCH cursor and INTO, but why ?

From: ddf <oratune_at_msn.com>
Date: Tue, 12 Feb 2013 13:58:53 -0800 (PST)
Message-ID: <e28408d8-3b88-498e-9898-dede211f96c7_at_googlegroups.com>

On Monday, February 11, 2013 7:56:56 AM UTC-7, Spendius wrote:
> Hello,
>
>
>
> Running this (on 10g R2):
>
> > create or replace type dual_typ as object (ddat date);
>
> > /
>
> >
>
> > create or replace type dual_tab as table of dual_typ;
>
> > /
>
> >
>
> > declare
>
> > cursor selDual_cur is select sysdate ddate from dual;
>
> > selDual dual_tab;
>
> >
>
> > begin
>
> >
>
> > open selDual_cur;
>
> > loop fetch selDual_cur bulk collect into selDual;
>
> > exit when selDual.count = 0;
>
> > dbms_output.put_line('ici');
>
> >
>
> > end loop;
>
> >
>
> > end;
>
> > /
>
> >
>
> I always get error
>
> PLS-00386: type mismatch found at 'SELDUAL' between FETCH cursor and
>
> INTO
>
> on the "loop fetch selDual..." line... If I replace dual_typ as object
>
> (ddat date);
>
> with dual_typ as object (ff varchar2(2));
>
> and the cursor selDual with select 'xx' from dual there's no
>
> difference.
>
>
>
> Someone has an idea perhaps ?
>
> Thanks a lot.
>
> Spendius

The issue is the object type you're trying to base the table on -- it's unnecessary:

SQL> create or replace type dual_tab as table of date;   2 /

Type created.

SQL>
SQL> declare
  2 cursor selDual_cur is select sysdate ddate from dual;   3 selDual dual_tab;
  4
  5 begin
  6
  7 open selDual_cur;
  8 loop

  9          fetch selDual_cur bulk collect into selDual;
 10          exit when selDual.count = 0;
 11          dbms_output.put_line('ici');

 12 end loop;
 13
 14 end;
 15 /
ici

PL/SQL procedure successfully completed.

SQL> David Fitzjarrell Received on Tue Feb 12 2013 - 22:58:53 CET