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

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?

CodePudding user response：

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