How to insert multiple object rows into a table in oracle sql?

Question

I need to implement the table below with sql (oracle):

First i created the types:

CREATE TYPE address AS OBJECT(num INT, street VARCHAR(20), city VARCHAR(20), zip INT);
CREATE TYPE phoneNum AS VARRAY(2) OF VARCHAR(13);
CREATE TYPE kids AS OBJECT(firstK VARCHAR(20), lastK VARCHAR(20), age INT);

Then i created the table "person":

CREATE TABLE person (id VARCHAR(10), firstName VARCHAR(20), lastName VARCHAR(20), adr address,pn phoneNum, ks kids);

I use this to insert into the table:

INSERT INTO person VALUES("1", "Justin", "Trudeau", address(10, "Main street", "Ottawa", 1000), phoneNum("+123456789012","+103647901456"), kids("Michelangelo", "Trudeau", 14));

Which works fine. But how do i insert the second row of "kids" (Leanardo, Trudeau, 10).

`... values ('a', 'b', 'c'), ('1', '2', '3')` ? this seems to be a duplicate of [this](https://stackoverflow.com/q/452859/8279585) -- please check the answers in that link — samkart, Nov 30 '21 at 13:40
Does this answer your question? [Inserting multiple rows in a single SQL query?](https://stackoverflow.com/questions/452859/inserting-multiple-rows-in-a-single-sql-query) — samkart, Nov 30 '21 at 13:41
Double quotes are for identifiers, use single quotes for string literals. E.g. `'Ottawa'`. And numeric literals need no quoting at all, simply `1`. — jarlh, Nov 30 '21 at 13:45

MT0 · Accepted Answer · 2021-11-30T14:02:22.400

how do i insert the second row of "kids" (Leanardo, Trudeau, 10).

You cannot. The kids type is defined as:

CREATE TYPE kids AS OBJECT(firstK VARCHAR(20), lastK VARCHAR(20), age INT);

and then you define the table as:

CREATE TABLE person (
  id VARCHAR(10),
  firstName VARCHAR(20),
  lastName VARCHAR(20),
  adr address,
  pn phoneNum,
  ks kids
);

kids (despite the plural in its name) is a singular object so you can only put a single kids value into it.

If you want to put multiple kids then you either need to:

change the table to store a collection of kids;
have a separate table for children; or
reverse the relationship and store two (or more) foreign key relationships for parents.

For example, you could (since children are people too) store the relationship as:

CREATE TYPE address AS OBJECT(
  num    INT,
  street VARCHAR2(20), 
  city   VARCHAR2(20),
  zip INT
);
CREATE TYPE phoneNum AS VARRAY(2) OF VARCHAR2(13);

CREATE TABLE person (
  id            INTEGER
                CONSTRAINT person__id__pk PRIMARY KEY,
  firstName     VARCHAR2(20),
  lastName      VARCHAR2(20),
  date_of_birth DATE,
  adr           address,
  pn            phoneNum
);

CREATE TABLE children(
  id        INTEGER
            CONSTRAINT children__id__pk PRIMARY KEY,
  parent_id CONSTRAINT children__parent_id__fk REFERENCES person (id),
  child_id  CONSTRAINT children__child_id__fk  REFERENCES person (id),
  CONSTRAINT children__pid_cid__u UNIQUE (parent_id, child_id)
);

db<>fiddle here

How to insert multiple object rows into a table in oracle sql?

1 Answers1