CODEWITHSUNDEEP
sqloracle-databasealter-tablenested-tablevarray
CDA the Programmer
CDA the Programmer

Reputation: 107

Add Column of Unbounded Array to SQL DB table

Is there a datatype described by an unbounded array (unlike VARRAY, where the original MAX size must be known) for SQL databases using Oracle? I'm trying to add a column to a DB table that has an array, but I don't know how big the array could become. Any help would be appreciated, thanks.

Basically, how do I finish this off?

ALTER TABLE my_table 
    ADD my_unbounded_array datatype

It's interesting too because I see that for postgresql, you could do:

ALTER TABLE my_table 
    ADD COLUMN my_unbounded_array text[]

for an unbounded array of strings, for example. But I haven't discovered anything similar for SQL.

Upvotes: 0

Views: 409

Answers (1)

MT0
MT0

Reputation: 167962

You can use a collection rather than a VARRAY:

CREATE TYPE string_array AS TABLE OF VARCHAR2(50);

Then can add it as a nested table:

CREATE TABLE table_name (
  ID NUMBER PRIMARY KEY
);

ALTER TABLE table_name ADD strings string_array
  NESTED TABLE strings STORE AS table_name__strings;

db<>fiddle here

Upvotes: 1

Related Questions