Oracle PL/SQL/Object Oriented Database/Object Table Insert

Insert data to table containing object column

 

SQL>
SQL>
SQL> --User-defined types
SQL>
SQL> CREATE TYPE address_typ AS OBJECT
  2   (StreetNo      NUMBER(10),
  3    StreetName    VARCHAR2(100),
  4    AptNo         NUMBER(5),
  5    City          VARCHAR2(100),
  6    State         VARCHAR2(100),
  7    ZipCode       NUMBER(9),
  8    Country       VARCHAR2(100));
  9  /
Type created.
SQL>
SQL>  CREATE TABLE people
  2    (ID        NUMBER(5),
  3     FirstName VARCHAR2(100),
  4     LastName  VARCHAR2(100),
  5     Address   address_typ);
Table created.
SQL>
SQL>
SQL>  INSERT INTO people
  2   VALUES(10,
  3          "John",
  4          "Smith",
  5          address_typ(123,"Happy Lane", NULL,
  6          "Smalltown","Alaska", 12345,"USA") );
1 row created.
SQL>
SQL>
SQL>
SQL> SELECT * FROM people;
                      ID FIRSTNAME
------------------------ ----------------------------------------------------------------------------------------------------
LASTNAME
----------------------------------------------------------------------------------------------------
ADDRESS(STREETNO, STREETNAME, APTNO, CITY, STATE, ZIPCODE, COUNTRY)
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                   10.00 John
Smith
ADDRESS_TYP(123.00, "Happy Lane", NULL, "Smalltown", "Alaska", 12345.00, "USA")

SQL>
SQL> drop table people;
Table dropped.
SQL>
SQL>

Insert statement with nested type

  
SQL> create type addressType as object
  2  (Street   VARCHAR2(50),
  3  City      VARCHAR2(25),
  4  State     CHAR(2),
  5  Zip       NUMBER);
  6  /
SQL>
SQL> create type personType as object
  2  (Name     VARCHAR2(25),
  3   Address  addressType);
  4  /
SQL>
SQL>
SQL> create or replace type personType as object
  2  (Name     VARCHAR2(25),
  3   Address  addressType);
  4  /
SQL>
SQL>
SQL> create table myemp
  2  (cid    NUMBER,
  3   Person         personType);
SQL>
SQL>
SQL> insert into myemp values(1,personType("SomeName",addressType("StreetValue","CityValue","ST",11111)));
SQL>
SQL>
SQL>
SQL> select C.cid, C.Person.Name
  2    from myemp C;
                                                              
       CID  PERSON.NAME
----------  -------------------------
         1  SomeName
                   
SQL>
SQL>
SQL> drop table myemp;
SQL> drop type personType;
SQL> drop type addressType;
SQL>

Use user-defined type in insert statement

  
SQL> create type addressType as object
  2  (Street  VARCHAR2(50),
  3   City    VARCHAR2(25),
  4   State   CHAR(2),
  5   Zip     NUMBER);
  6  /
SQL> create type personType as object
  2  (Name     VARCHAR2(25),
  3   Address  addressType);
  4  /
SQL>
SQL>
SQL> create table CUSTOMER(
  2    cid  NUMBER,
  3    Person       personType
  4  );
SQL>
SQL> insert into CUSTOMER values(1,personType("SomeName",addressType("Street","City","ST",11111)));
SQL> insert into CUSTOMER values(1,personType("SomeName",addressType("Street2","City2","ST",11111)));
SQL>
SQL> drop type personType force;
SQL>
SQL> drop type addressType force;
SQL>
SQL> drop table CUSTOMER;