0

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
CDA the Programmer
  • 87
  • 1
  • 6
  • 20
  • What exactly do you call "SQL databases"? Which database do you use? I know, you tagged the question with the Oracle tag, but "... anything similar for SQL" sounds as if you, actually, use MS SQL Server. – Littlefoot Aug 09 '21 at 20:12
  • @Littlefoot Oracle – CDA the Programmer Aug 09 '21 at 20:13
  • 4
    An unbounded array is a **TABLE**. Pretty much _every time_ you want to do this kind of thing, what you really need is another table, where the primary key from the first table is the first (or more, if it's a complex key) column in the new table. – Joel Coehoorn Aug 09 '21 at 20:16
  • After you resolve the problem of array storage, the next task will be to consume it in SQL or maybe check for element containment, intersection and other operations that are applied to sets. But they are not so easy and straightforward without native array datatype as general table and general SQL operations. So separate table is a good choice – astentx Aug 09 '21 at 21:46
  • 1
    The maximum size of a `varray` is over 2 billion elements. Surely you aren't planning to store arrays with more elements than that, as values in a column in a database table? That would be insane. So, I don't understand your question to begin with. –  Aug 09 '21 at 23:26
  • @mathguy Ok, this is good to know. I should've also included the question of what the upper limit is in my original post. But thanks for adding it to the conversation. I see that the specific number is 2147483647 elements https://stackoverflow.com/questions/33621047/pl-sql-maximum-size-of-varray. – CDA the Programmer Aug 10 '21 at 12:47

1 Answers1

1

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

MT0
  • 143,790
  • 11
  • 59
  • 117
  • Thanks, how do I actually view tables in db fiddle? – CDA the Programmer Aug 09 '21 at 20:26
  • @CDAtheProgrammer Same as most other database user interfaces; with a `SELECT` statement. [db<>fiddle](https://dbfiddle.uk/?rdbms=oracle_18&fiddle=abf93fc3548561684b614d8108cd5eeb) – MT0 Aug 09 '21 at 20:32
  • @MTO just to nitpick, [varrays are also collections](https://docs.oracle.com/en/database/oracle/oracle-database/21/lnpls/plsql-collections-and-records.html#GUID-7E9034D5-0D33-43A1-9012-918350FE148C). – William Robertson Aug 09 '21 at 20:56
  • @WilliamRobertson I don't particularly like calling it a "nested table" as the type isn't a nested table until you nest it in a table. I suppose I could call it a variable-length non-associative array (or collection). – MT0 Aug 09 '21 at 21:10
  • 1
    @MT0 - you are obscuring the difference between "common language" and "technical language". We may dislike Oracle's choice of words (I do too!) but technically something is a "nested table" if it satisfies the definition given in the language specification. A PL/SQL nested table is a nested table even if it is not nested into anything else, simply because that's what the PL/SQL language definition says it is. –  Aug 09 '21 at 23:30
  • It is correct to call it a collection and it is also correct to call it a nested table (as one is a sub-category of another); one may be more or less correct but the context of the answer is understandable with either. I hope we can also agree that it is [wrong to call it a suspension bridge](https://www.youtube.com/watch?v=F_1zoX5Ax9U). – MT0 Aug 09 '21 at 23:45
  • Well, a PL/SQL nested table variable may be neither nested nor a table, but a nested table column in a database table kind of is. – William Robertson Aug 10 '21 at 22:30