Home When inserting values into an object table in Oracle SQL, how do you declare nested objects?

Questions

When inserting values into an object table in Oracle SQL, how do you declare nested objects?

June 19, 2022

I’m experimenting with objects and nested tables to get an understanding of how they work.

I have created three objects types – Address, Property, and Sale. Address is nested in Property, and Property is nested in Sale. I have then created a Sold table – of type Sale.

CREATE OR REPLACE TYPE ADDRESS AS OBJECT(
    line_1 VARCHAR2 (10),
    town VARCHAR2 (10),
    postcode VARCHAR2(10)
);

CREATE OR REPLACE TYPE PROPERTY AS OBJECT(
    location ADDRESS,
    description VARCHAR2(10)
);

CREATE OR REPLACE TYPE SALE AS OBJECT(
    house PROPERTY,
    sale_price NUMBER
);


CREATE TABLE SOLD OF SALE;

However, when I try to insert values using any of the parenthesis groupings below, I get the error ‘missing right parenthesis or’ too many values’.

INSERT INTO SOLD VALUES ((('1 MY ROAD', 'MYTOWN', 'MY123'), 'DESCRIPTION'), 100000);

INSERT INTO SOLD VALUES (('1 MY ROAD', 'MYTOWN', 'MY123', 'DESCRIPTION'), 100000);

INSERT INTO SOLD VALUES ('1 MY ROAD', 'MYTOWN', 'MY123', 'DESCRIPTION', 100000);

In this context what is the correct way to group nested objects to avoid errors?

>Solution :

Use the object constructors:

INSERT INTO sold (house, sale_price)
VALUES (
  property(
    address('1 MY ROAD', 'MYTOWN', 'MY123'),
    'DESCRIPTION'
  ),
  100000
);

or:

INSERT INTO sold 
VALUES (
  sale(
    property(
      address('1 MY ROAD', 'MYTOWN', 'MY123'),
      'DESCRIPTION'
    ),
    100000
  )
);

Note: DESCRIPTION is 11 characters so it will not fit into a VARCHAR2(10) attribute. Either increase the size of the attribute or reduce the size of the string literal you are trying to put into the attribute.

db<>fiddle here