Error trying to print Collection variable by index

Question

Can anyone identify what is wrong with the following code. specifically the first dbms_output line. The second one prints fine. But first one gives this error:

Error at line 2 ORA-06550: line 15, column 53: PLS-00201: identifier 'MYCOLL' must be declared ORA-06550: line 15, column 1: PL/SQL: Statement ignored

DECLARE
   CURSOR c1
   IS
      SELECT sub_provider_address_id sub_id, phone, extension
        FROM sub_provider_address;

   TYPE coll_type IS TABLE OF c1%ROWTYPE;
   my_coll   coll_type;
BEGIN
   OPEN c1;

   FETCH c1
   BULK COLLECT INTO my_coll;

dbms_output.put_line(' my_coll first row id has '|| mycoll(1).phone );
dbms_output.put_line(' my_coll now has '|| my_coll.last );

END;

Answer 1

You're declaring the variable as my_coll:

my_coll   coll_type;

And on the line that's erroring you're referring to it as mycoll:

dbms_output.put_line(' my_coll first row id has '|| mycoll(1).phone );

So you're just missing an underscore, and it should be:

dbms_output.put_line(' my_coll first row id has '|| my_coll(1).phone );

Answer 2

Your coll_type is not an associated array type. you have to use something like this:

TYPE coll_type IS TABLE OF c1%ROWTYPE index by pls_integer;

Use this as an example:

declare
  cursor c1 is
    select 1 id from dual
  ;
  type coll_type is table of c1%ROWTYPE index by pls_integer;
  l_coll_type coll_type;
begin
  open c1;
  fetch c1 bulk collect into l_coll_type;
  close c1;
  dbms_output.put_line('Output:' || l_coll_type(1).id);
end;

Output:1