CODEWITHSUNDEEP
sqloracle-database
OddCommand24
OddCommand24

Reputation: 441

How to insert an additional varray record in Oracle?

create type recordObj as object(
rID varchar(5),
rName varchar(5));

create type recordsArr as varray(2) of recordObj;

create table RecordsTable(
ID varchar(3),
records recordsArr);

insert into RecordsTable values ('RE1', recordsArr(recordObj('RE-12','CONF')));

After successfully inserting this row, I want to add an additional record into the 'recordsArr' varray where RecordsTable ID is also 'RE1', since varray is of size 2, but I don't want to do it in the query I have just written. If I run this query again, but with two 'recordObj' inserts, it just duplicates the data and create a new row. I want add a new varray record but in the existing record where ID is 'RE1'. Any help?

Upvotes: 0

Views: 224

Answers (1)

MT0
MT0

Reputation: 167981

You can do it via PL/SQL:

DECLARE
  p_records recordsArr;
BEGIN
  SELECT records
  INTO   p_records
  FROM   recordsTable
  WHERE  id = 'RE1'
  FOR UPDATE;
  
  p_records.EXTEND;
  p_records(2) := recordObj( 'RE-13', 'ABC' );

  UPDATE RecordsTable
  SET records = p_records
  WHERE id = 'RE1';
END;
/

Then:

SELECT id,
       r.*
FROM   recordsTable rt
       CROSS JOIN TABLE( rt.records ) r;

Outputs:

ID RID RNAME
RE1 RE-12 CONF
RE1 RE-13 ABC

db<>fiddle here

If, instead, you created the data-type as a collection using:

create type recordObj as object(
  rID varchar(5),
  rName varchar(5)
);

create type recordsArr as table of recordObj;           -- TABLE not VARRAY(2)

create table RecordsTable(
  ID varchar(3),
  records recordsArr
) NESTED TABLE records STORE AS recordstable__records;  -- Add storage for the collection

insert into RecordsTable values ('RE1', recordsArr(recordObj('RE-12','CONF')));

Then you could use:

INSERT INTO TABLE( SELECT records FROM RecordsTable WHERE id = 'RE1' )
VALUES ( 'RE-14', 'DEF' );

But, that will not work for a VARRAY.

db<>fiddle here

Upvotes: 1

Related Questions