Search code examples
sqloracle-databaseoracle12c

Oracle - enlarge array definition

I have defined custom type in Oracle db 12c.

create or replace type integer_varray as varray (4000) of int;

Now I realized that I need more than 4 000 records over there so I would like to extend this definition to 5 000 without loosing all data that are already in these arrays.

Is it possible? How?

EDIT: usage is like 

create table capacities
(
  id       int generated by default as identity (START WITH 1000000) not null
  ... other fields
  capacity integer_varray                                            not null
);
Solution

  • Yes, you can using ALTER TYPE .. MODIFY LIMIT with CASCADE option

    ALTER TYPE integer_varray  MODIFY LIMIT 5000 CASCADE;

    Demo