ORA 06533: Subscript Out of Account

Question

I created below a simple block, but getting

ORA 06533: subscript for the count

error.

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

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; / 

Answer 1

Part of the array_var.extend code must be inside the loop. Each time you add to it, you allocate a new memory. Skipping this step requires the code to store something without specifying it.

 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; / 

Soon you are likely to encounter a similar error, ORA-06532: Subscript outside of limit . You limit VARRAY to 6 elements, but there may be more potential customers. Consider a return restriction, a VARRAY extension, or the introduction of a more dynamic collection type.

Answer 2

In your select count(*) into counter from customers; There are more than 6 results that cannot be stored in the varray(6) variable.

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

Source: Declare a dynamic array in PLSQL

Answer 3

Your salary_array can store a maximum of 6 customer salaries, but your select count(*) into counter from customers returns more than 6 records.

Because of this, the array cannot hold data or put other words, the in-limit index is larger than the counter.