CODEWITHSUNDEEP
oracle-databasetuples
Arseni Mourzenko
Arseni Mourzenko

Reputation: 52321

Can I have a varray of tuples in Oracle?

In Oracle, I have a table users. Within this table, for every user, I want to be able to store an array of attributes of a user, that is, key-value pairs.

I could create an extra table with three columns—the foreign key pointing to the users table, the key and the value—but I would prefer keeping things simple and don't want to create an additional table for that.

I know that Oracle has varrays, created like this:

CREATE OR REPLACE TYPE example AS VARRAY(20) OF NVARCHAR2(500);

How do I do the same thing, but instead of NVARCHAR2, I would have a tuple (NVARCHAR2(50), NVARCHAR2(200))?

All the resources about Oracle tuples point to either a grouping of columns to be used in a WHERE ... IN clause, or Java-related documentation.

Upvotes: 0

Views: 532

Answers (1)

PKey
PKey

Reputation: 3841

What if you use record ... and not tuple?

DECLARE

TYPE t_myrecord IS RECORD ( field1 varchar2(50), field2 varchar2(200));
TYPE t_myarray IS VARRAY(20) OF t_myrecord;
a_myrecord t_myrecord;
a_myarray t_myarray := t_myarray();

BEGIN
a_myrecord.field1 := 'a';
a_myrecord.field2 := 'b';
a_myarray.extend(19);
a_myarray(1) := a_myrecord;
END;

Upvotes: 1

Related Questions