Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

ORA 06533: Subscript beyond count

Tags:

oracle

plsql

I created below simple block but getting

ORA 06533:Subscript beyond count

error.

Can someone please tell me what I am missing in below code.

declare
  type salaryvarray is varray(6) of customers.salary%type;
  salary_array salaryvarray:=salaryvarray();
  c_salary customers.salary%type;
  i integer(2);
  counter number(2);
begin
  salary_array.extend;
  select count(*) into counter from customers;
  for i in 1..counter loop
    select salary into c_salary from customers where id =i;
    salary_array(i):=c_salary;
  end loop;
end;
/
like image 302
Bhushan Avatar asked Mar 09 '15 15:03

Bhushan


2 Answers

The array_var.extend portion of the code needs to be inside the loop. Each time you add to it, you are allocating new memory. Skipping this step is asking the code to store something without giving it space.

declare
  type salaryvarray is varray(6) of customers.salary%type;
  salary_array salaryvarray:=salaryvarray();
  c_salary customers.salary%type;
  i integer(2);
  counter number(2);
begin
  select count(*) into counter from customers;
  for i in 1..counter loop
    salary_array.extend; -- Extend for each value.
    select salary into c_salary from customers where id =i;
    salary_array(i):=c_salary;
  end loop;
end;
/

You will very likely run into a similar error soon, however, ORA-06532: Subscript outside of limit. You limit your VARRAY to 6 elements, but customers could potential have more. Consider limiting the return, expanding the VARRAY or implementing a more dynamic collection type.

like image 172
Chris Hep Avatar answered Sep 29 '22 21:09

Chris Hep


Your select count(*) into counter from customers; has more than 6 results this way not able to save into varray(6) variable.

Instead of array, that has a fixed number of elements, you can use nested table or associative array.

Source: Declare dynamic array in PLSQL

like image 30
Fabio Farath Avatar answered Sep 29 '22 19:09

Fabio Farath