CODEWITHSUNDEEP
sqlplsql
AKSHATHA G.YAJAMAN
AKSHATHA G.YAJAMAN

Reputation: 3

How to add contents to the array dynamically in plsql

Unable to store values in to 'names' array.Please help me to resolve the error.

declare 
type name is varray(50) of varchar2(10);
abc varchar2(50);
names name:=name();
begin
    for i in 0..4
    loop
        select cname into abc from customer where cid=1;
        names(i):=abc;
        dbms_output.put_line(names(i));
    end loop;
end;

I am new to pl/sql and I am trying to add elements to my array but I am getting some error as follows.

ORA-06532: Subscript outside of limit

Upvotes: 0

Views: 8306

Answers (2)

Sudipta Mondal
Sudipta Mondal

Reputation: 2572

The loop in PL/SQL needs to start with 1.

Also, before saving value, you need to extend the record variable.

Also, your type is varray(50) of VARCHAR2(10), whereas abc is variable of size 50. 

declare 
    type name is varray(50) of varchar2(50);
    abc varchar2(50);
    names name:=name();
begin
    for i in 1..4
    loop
        select cname into abc from customer where cid=1;
        names.extend(1);
        names(i):=abc;
        dbms_output.put_line(names(i));
    end loop;
end;

Why do you need to EXTEND - Standard Oracle DOC

Upvotes: 3

Arkadiusz Łukasiewicz
Arkadiusz Łukasiewicz

Reputation: 6346

    declare 
    type name is varray(50) of varchar2(10);
    abc varchar2(50);
    names name:=name();
    begin
        for i in 1..4
        loop
            select cname into abc from customer where cid=1;
            names.extend;
            names(i):=abc;
            dbms_output.put_line(names(i));
        end loop;

end;

1) Arrays start from 1.

2) type name is varray(50) of varchar2(10); means that max length of array is 50 is not connected with initial length.

Upvotes: 0

Related Questions