-1

By below code is not working for cursor on PLSQL. I receive an error message. 

 declare 
      type abc is varray(10) of number;
      cursor x is select Empno from emp where rownum <10;
      a abc;
      counter  number :=1;
    begin
      a:=abc();
     for i in x
     loop
      a.extend();
      a(i):=counter.Empno;
      DBMS_output.put_line(a(i));
      counter:=  end loop;
    end;
Nigel
  • 47
  • 1
  • 2
  • 8

1 Answers1

3

You have used counter instead of for loop iterator i:

Try;

declare 
    type abc is varray(10) of number;
    cursor x is select Empno from emp where rownum <10;
    a abc;
    counter number := 1;
begin
    a:=abc();
    for i in x loop
        a.extend();
        a(counter) := i.Empno;
        DBMS_output.put_line(a(counter));
        counter := counter + 1;
    end loop;
end;

Or you can use Bulk collect to insert data into varray

declare 
    type abc is varray(10) of number;
    a abc := abc();        
begin  
    select Empno BULK COLLECT INTO a from emp where rownum <10;
    for i in 1 .. a.count loop 
        DBMS_output.put_line(a(i)); 
    end loop;
end;
Praveen
  • 8,945
  • 4
  • 31
  • 49
  • When there is no data in the table. I get a error during runtime. what would be the exception ? – Nigel Feb 13 '16 at 18:06
  • Error report - ORA-06502: PL/SQL: numeric or value error ORA-06512: at line 6 06502. 00000 - "PL/SQL: numeric or value error%s" *Cause: An arithmetic, numeric, string, conversion, or constraint error occurred. For example, this error occurs if an attempt is made to assign the value NULL to a variable declared NOT NULL, or if an attempt is made to assign an integer larger than 99 to a variable declared NUMBER(2). *Action: Change the data, how it is manipulated, or how it is declared so that values do not violate constraints. – Nigel Feb 13 '16 at 18:15
  • this is because of collection (varray) is empty then a.first and a.last will return null....... Please see the updated answer...... – Praveen Feb 13 '16 at 18:18