ORA 06533: Subscript beyond count

Question

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

Athwart

How many customers are there? – Investigation

Apparently you're selecting more customers than there's room in your varray, which has room for 6. More importantly, to answer this question properly, what are you trying to achieve? – Windmill

You need to extend within the loop. Right now you only ever allocate memory for a single item. That being said, this will potentially break if you ever have more than 6 customers as @Windmill mentioned. – Gallery

Also, doing it this way implies that your customers all have ids that are consecutive. That's not necessarily going to be the case, out in the real world! What is it you're trying to achieve here - is it learning to work with varrays? If not, you'd probably be far better off with a bulk collect forall – Monosyllable

Answer 1

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.

Gallery

Answer 2

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

Lindi

Answer 3

salary_array.extend just extend 1 index. In Your case You must use this syntax for extend all index of varray:

salary_array.extend(6);

for other case use size of varray insted of 6 .

Bitartrate

Answer 4

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

Due to this the array is not able to hold the data or to put in other words, An in-limit subscript was greater than the count of a varray.

Cerated

While this is still likely to become an issue, the error you are thinking of is ORA-06532:Subscript outside of limit. This error, ORA 06533:Subscript beyond count is due to trying to add a record without allocating memory with a call to extend. – Gallery

Answer 5

Try initializing with default values.

salary_array salaryvarray := salaryvarray(null, null, null, null, null, null);

Vogele