PostgreSQL array column in Oracle-CodePudding

In postgresql I can do following:

create table test(
    arrcolumn character varying(100)[]
)

insert into test(arrcolumn) values ('{bla,blubber}');

How can I create the same in oracle?

There is something called VARRAY but it seems not to work in sql tables only in PL/SQL procedures and functions?

CodePudding user response：

There is something called VARRAY but it seems not to work in sql tables only in PL/SQL procedures and functions?

That is incorrect; it does work in tables.

Define your data type:

CREATE TYPE string_array AS VARRAY(100) OF VARCHAR2(50);

Then use it in the table:

CREATE TABLE test (
  id        INT
            GENERATED ALWAYS AS IDENTITY
            PRIMARY KEY,
  arrcolumn string_array
);

Then you can insert values:

INSERT INTO test (
  arrcolumn
) VALUES (
  string_array('bla', 'blubbler')
);

db<>fiddle here

Do you know if this can also be unlimited?

Use a nested table:

CREATE TYPE string_array AS TABLE OF VARCHAR2(50);

CREATE TABLE test (
  id        INT
            GENERATED ALWAYS AS IDENTITY
            PRIMARY KEY,
  arrcolumn string_array
) NESTED TABLE arrcolumn STORE AS test__arrcolumn;

Or you can use two tables.

db<>fiddle here

CodePudding user response：

You can use an array data type maintained by the system itself, instead of defining your own. Something like this. Notice the syntactical differences: in Oracle you must use the type name for constructor (you can't just give the values, they must be given in a constructor).

The maximum number of elements in a sys.odci*list array is 32767 - no doubt because Oracle, for whatever reason, uses signed 16-bit integers to index into an array. (I can understand 16-bit, for compatibility with oldest versions, but why signed?)

create table t (arrcolumn sys.odcivarchar2list);
insert into t (arrcolumn) values (sys.odcivarchar2list('bla', 'blubber'));
select * from t;

ARRCOLUMN                             
--------------------------------------
ODCIVARCHAR2LIST('bla', 'blubber')

Then, if you need to access the elements in an array value (in the column), you can use the table operator, for example:

select column_value from t, table(arrcolumn);

COLUMN_VALUE
------------
bla
blubber