CODEWITHSUNDEEP
databaseoracle-databaseplsqluser-defined-types
Iustin Vlad
Iustin Vlad

Reputation: 73

How i can add one more value to my varray list in a column in pl/sql?

I'm trying to add one more value to my varray list of numbers called burse using multiset union all but I get this error. [Istoric is my column name and bure is the type (varray of numbers)1

But when I insert a single value its working. Example:enter image description here

What I am doing wrong ?

This is how I declare and insert into column enter image description here

Upvotes: 1

Views: 209

Answers (1)

APC
APC

Reputation: 146239

This should work...

update student 
set istoric = istoric multiset union all bure(42, 23) 
where id = 1

... except that you're now using a VARRAY (and not the nested table you had in your previous question). So you get an error message:

ORA-00932: inconsistent datatypes: expected UDT got BURE

The reason is, according to the documentation:

"While nested tables can also be changed in a piecewise fashions, varrays cannot....However, you cannot update or delete individual varray elements directly with SQL; you have to select the varray from the table, change it in PL/SQL, then update the table to include the new varray." (emphasis mine)

This is because VARRAYs are ordered sets while Nested Tables are not. Unless there is a firm requirement to maintain the order of elements it is better to use Nested Tables rather than Varrays: they're just more convenient.

So here is how you can update a Varray using PL/SQL:

declare
    lv bure;
    cnt pls_integer;
begin
    select istoric into lv
    from student
    where id = 1;

    cnt := lv.count();
    lv.extend();
    lv(cnt+1) := 23 ;
    lv.extend();
    lv(cnt+2) := 69 ;

    update student
    set istoric = lv
    where id = 1;
end;
/

Upvotes: 1

Related Questions