insert into person values(312,'john','doe',to_date("10.10.2000"),COLECTION_OF_CONTACTS('123123'),ADRESA('city','street','123','123'),'first_floor')

this is my insert on a table and oracle application express returns me this

table is defined as

CREATE TABLE  "UKLIZECKA" 
   (    "ID" NUMBER NOT NULL ENABLE, 
    "JMENO" VARCHAR2(32) NOT NULL ENABLE, 
    "PRIJMENI" VARCHAR2(32) NOT NULL ENABLE, 
    "DATUM_NAROZENI" DATE NOT NULL ENABLE, 
    "KONTAKT_UKLIZECKA"  "COLECTION_OF_CONTACTS" , 
    "ADRESA"  "ADRESA" , 
    "OBLAST_UKLIDU_ID" VARCHAR2(21) NOT NULL ENABLE, 
     CHECK ( adresa.mesto IS NOT NULL ) ENABLE, 
     CHECK ( adresa.ulice IS NOT NULL ) ENABLE, 
     CHECK ( adresa.popisne_cislo IS NOT NULL ) ENABLE, 
     CHECK ( adresa.psc IS NOT NULL ) ENABLE, 
     CONSTRAINT "UKLIZECKA_PK" PRIMARY KEY ("ID") ENABLE
   ) 
 NESTED TABLE "KONTAKT_UKLIZECKA" STORE AS "KONTAKT_UKLIZECKA"
 RETURN AS VALUE
/
ALTER TABLE  "UKLIZECKA" ADD CONSTRAINT "UKLIZECKA_OBLAST_UKLIDU_FK" FOREIGN KEY ("OBLAST_UKLIDU_ID")
      REFERENCES  "OBLAST_UKLIDU" ("ID") ENABLE
/
ALTER TABLE  "UKLIZECKA" ADD CONSTRAINT "UKLIZECKA_ZAMESTNANEC_FK" FOREIGN KEY ("ID")
      REFERENCES  "ZAMESTNANEC" ("ID") ENABLE
/

CREATE UNIQUE INDEX  "UKLIZECKA__IDX" ON  "UKLIZECKA" ("OBLAST_UKLIDU_ID")/

and the colection "COLECTION_OF_CONTACTS" is defined as

CREATE OR REPLACE TYPE  "COLECTION_OF_CONTACTS" IS 
    TABLE OF VARCHAR2(30); 
/

and structured data type"ADRESA" is defined as

CREATE OR REPLACE TYPE  "ADRESA" AS OBJECT ( 
    mesto         VARCHAR2(36), 
    ulice         VARCHAR2(36), 
    popisne_cislo VARCHAR2(36), 
    psc           VARCHAR2(36) 
) NOT FINAL; 
/

table, colection and structured type were generated sql from oracle data modeler

But what i need is to help with my insert into sql comand where i can't seem to find where is the issue

thank you for the sudgestions

CodePudding user response：

You have two issues:

• The table name is UKLIZECKA and not PERSON.
• "" is for identifiers and '' is for string literals so to_date("10.10.2000") is looking for a column identified by "10.10.2000" rather than passing a string literal. You want TO_DATE('10.10.2000', 'DD.MM.YYYY') (with an explicit, rather than implicit, format model) or a date literal DATE '2000-10-10'.
• It is also best practice to specify the columns you are inserting into.

Like this:

insert into uklizecka (
  id,
  jmeno,
  prijmeni,
  datum_narozeni,
  kontakt_uklizecka,
  adresa,
  oblast_uklidu_id
)values(
  312,
  'john',
  'doe',
  date '2000-10-10',
  COLECTION_OF_CONTACTS('123123'),
  ADRESA('city','street','123','123'),
  'first_floor'
)

db<>fiddle here