Oracle nested table append

Question

I have an oracle nested table as follows:

create table test_tab
(
Col1        VARCHAR,
Col2        VARCHAR,
Col3        VARCHAR,
Col4       Coltype,
PK(col1,col2,col3)
 );

Coltype definition:

create type Coltype as varray(10) of Coltuple;

Coltuple definition:

create type remarktuple as object
(
ColX varchar,
Coly varchar,
Colz varchar
);

and i have inserted a single row

insert into test_tab values('A','B','C',Coltype(Coltuple('X','Y','Z'));

now, if i want to inserted Coltype(Coltupele('P','Q','R')) to the same A,B,C row, how can i do it??... When i use a separate insert like insert into test_tab values('A','B','C',Coltype(Coltuple('P','Q','R')); , it is assuming it as a second insert and throwing error because of PK voilation.

Hope i have explained my requirement clearly. Thank you in advance.

RGO · Accepted Answer · 2013-12-19 11:18:08Z

1

With the above definition, Col4 is not a nested table; it's a varray. For Col4 to be a nested table, first, Coltype should be defined as:

CREATE TYPE Coltype AS TABLE of Coltuple;

Then, test_tab should be defined as something like the below:

CREATE TABLE test_tab
(
Col1        VARCHAR2(30),
Col2        VARCHAR2(30),
Col3        VARCHAR2(30),
Col4        Coltype,
PRIMARY KEY (col1,col2,col3)
)
NESTED TABLE Col4 STORE AS ColtypeStoreTab;

Now, with (Col1,Col2,Col3) being defined as the primary key of the parent table, it is not possible to insert another row with the values of ('A','B','C') while such a row already exists in the table unless the primary key constraint of the parent table is relaxed. Much probably, you wanted to insert ('P','Q','R') into the nested table of the parent table's row where (Col1,Col2,Col3) was('A','B','C'). If that's the case, here is how it could be achieved:

INSERT INTO TABLE(SELECT Col4 FROM test_tab 
                  WHERE Col1 = 'A' AND Col2 = 'B' AND  Col3 = 'C')
VALUES('P','Q','R');

answered Dec 19, 2013 at 11:18

RGO

4,7774 gold badges30 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Vinod Chelladurai Over a year ago

Thank you ver much sir. Your solution has been fantastic

Wernfried Domscheit · Accepted Answer · 2013-12-19 13:24:52Z

0

Another way to do it is this one:

UPDATE test_tab 
SET Col4 = Col4 MULTISET UNION Coltype(Coltuple('P','Q','R'))
WHERE Col1 = 'A' AND Col2 = 'B' AND  Col3 = 'C';

answered Dec 19, 2013 at 13:24

Wernfried Domscheit

60.4k10 gold badges93 silver badges132 bronze badges

Collectives™ on Stack Overflow

Oracle nested table append

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related