CODEWITHSUNDEEP
oracle-databaseobjectoracle11g
Gaurav Soni
Gaurav Soni

Reputation: 6346

How to add more rows to object type column in oracle

CREATE OR REPLACE TYPE excep_type
AS
   OBJECT (overridden_attribute VARCHAR2 (30), exception_id NUMBER);

CREATE TABLE obj_test (id     NUMBER, obj_col excep_type);


INSERT INTO obj_test
  VALUES   (1, excep_type ('x', 1));

 SELECT   * FROM obj_test;

 ID   OBJ_COL
 --------------
 1     (X,1)

This is fine to me ,but suppose i want to add more records to this object type column obj_col ,then what need to be done .

Suppose there is one more record Y,2 need to be inserted into the column obj_col for ID 1 then what statement need to be executed . EXPECTED OUTPUT

ID   OBJ_COL
--------------
1     (X,1)
      (Y,2)

IF I want to update the existing one LIKE X,1 is been updated from Z,3 ,then what need to be done .Expected Output

ID   OBJ_COL
--------------
1     (Z,3)

Please help me with this

Upvotes: 4

Views: 2835

Answers (2)

Nick Krasnov
Nick Krasnov

Reputation: 27251

To achieve that you need to use nested table. Here is an example:

create or replace type excep_type as object 
(
  overridden_attribute varchar2 (30), 
  exception_id number
)
/

create or replace type t_excep_type as table of excep_type
/

create table nst_table
(
  id  number,
  exp t_excep_type
) nested table exp store as nst_exp -- name of the nested table
/


-- inserting of record in the base table

SQL> insert into nst_table(id, exp)
  2    values(1, t_excep_type(excep_type('X', 1)));

1 row inserted

SQL> commit;

Commit complete

 SQL> select * from nst_table t, table(t.exp);

    ID EXP OVERRIDDEN_ATTRIBUTE           EXCEPTION_ID
   --------------------------------------------- ------------
     1 <Ob X                               1   

 -- inserting of record in the nested table

SQL> insert into table(select exp from nst_table where id = 1)
  2    values (excep_type('Y', '2'))
  3  ;

1 row inserted

SQL> commit;

Commit complete

-- unnesting.

SQL> select * from nst_table t, table(t.exp);

        ID EXP OVERRIDDEN_ATTRIBUTE           EXCEPTION_ID
---------- --- ------------------------------ ------------
         1 <Ob X                              1
         1 <Ob Y                              2


 -- updating of data in the nested table

 SQL> update table(select exp from nst_table where id = 1)
   2     set overridden_attribute = 'Z', exception_id = 3
   3   where exception_id = 1 and overridden_attribute = 'X';

 1 row updated

 SQL> select * from nst_table t, table(t.exp);

    ID EXP OVERRIDDEN_ATTRIBUTE           EXCEPTION_ID
   ---------- --- -------------------------------------------
     1 <Ob Z                              3  
     1 <Ob Y                              2

But that approach of storing data to implement master-detail relationship is not the best one.

Upvotes: 2

A.B.Cade
A.B.Cade

Reputation: 16915

try:

CREATE OR REPLACE TYPE excep_type AS    OBJECT (overridden_attribute VARCHAR2 (30), exception_id NUMBER);  
CREATE OR REPLACE TYPE excep_type_List AS TABLE OF excep_type;
CREATE TABLE obj_test (id     NUMBER, obj_col excep_type_List)
NESTED TABLE obj_col store as obj_col_tab; 

insert into obj_test(id, obj_col) values(1, excep_type_List(
new excep_type('X',1),
new excep_type('Y',2)
));

Upvotes: 1

Related Questions