Oracle PL/SQL/Object Oriented Database/Insert

Insert data into an object table

 
SQL> 
  2
SQL> create or replace
  2  type address as object(
  3   id number,
  4   street varchar2(100),
  5   state varchar2(2),
  6   zipcode varchar(11)
  7  )
  8  /
Type created.
SQL>
SQL> create table address_table of address
  2  /
Table created.
SQL>
SQL>
SQL>
SQL> insert into address_table values ( address( 2, "123 Main Street", "NJ", "07728" ) )
  2  /
1 row created.
SQL>
SQL> select * from address_table;

        ID
----------
STREET
--------------------------------------------------------------------------------
ST ZIPCODE
-- -----------
         2
123 Main Street
NJ 07728

SQL> drop table address_table;
Table dropped.
SQL>

Insert inherited object

 
SQL>
SQL> create or replace
  2  type person as object (
  3   first_name varchar2(100),
  4   last_name varchar2(100),
  5   dob date,
  6   phone varchar2(100),
  7   member function get_last_name return varchar2,
  8   member function get_phone_number return varchar2 )
  9  not final
 10  /
Type created.
SQL>
SQL>
SQL> create or replace
  2  type body person as
  3    member function get_last_name return varchar2 is
  4    begin
  5      return self.last_name;
  6    end;
  7    member function get_phone_number return varchar2 is
  8    begin
  9      return self.phone;
 10    end;
 11  end;
 12  /
Type body created.
SQL>
SQL> create table person_table( p person );
Table created.
SQL>
SQL>
SQL>
SQL> create or replace
  2  type new_employee under person (
  3    empno number,
  4    hiredate date,
  5    work_phone varchar2(100),
  6    overriding member function get_phone_number return varchar2,
  7    member function get_home_phone_number return varchar2 )
  8  not final
  9  /
Type created.
SQL>
SQL> create or replace
  2  type body new_employee as
  3    overriding member function get_phone_number return varchar2 is
  4    begin
  5      return self.work_phone;
  6    end;
  7    member function get_home_phone_number return varchar2 is
  8    begin
  9      return self.phone;
 10    end;
 11  end;
 12  /
Type body created.
SQL>
SQL>
SQL> select treat( x.p as new_employee ).empno empno,
  2         x.p.last_name last_name
  3     from person_table x
  4    where p is of ( new_employee )
  5  /
no rows selected
SQL>
SQL> select * from person_table;
no rows selected
SQL>
SQL> drop table person_table;
Table dropped.
SQL>
SQL> drop type new_employee;
Type dropped.
SQL>
SQL> drop type person;
Type dropped.
SQL>

INSERT Values into a Table that Contains Row Objects (TCRO)

SQL> -- INSERT Values into a Table that Contains Row Objects (TCRO)
SQL>
SQL>
SQL> CREATE OR REPLACE TYPE addressType as OBJECT(
  2        street VARCHAR2(20),
  3        city VARCHAR2(20),
  4        state CHAR(2),
  5        zip CHAR(5));
  6  /
Type created.
SQL>
SQL> CREATE TABLE address_table OF addressType;
Table created.
SQL>
SQL> CREATE TABLE client (name VARCHAR2(20),
  2    address REF addressType scope is address_table);
Table created.
SQL>
SQL>
SQL>
SQL> INSERT INTO client VALUES ("Jones",null);
1 row created.
SQL>
SQL>
SQL> SELECT * FROM client;
NAME      ADDRESS
--------- --------------------------------------------------
Jones
SQL>
SQL>
SQL>
SQL> drop table client;
Table dropped.
SQL>
SQL> drop table address_table;
Table dropped.
SQL>
SQL>

INSERT Values into a Table with the Column Type in It

SQL> CREATE OR REPLACE TYPE addressType as OBJECT(
  2        street VARCHAR2(20),
  3        city VARCHAR2(20),
  4        state CHAR(2),
  5        zip CHAR(5));
  6  /
Type created.
SQL> CREATE TABLE emp (empno   NUMBER(3),
  2                    name    VARCHAR2(20),
  3                    address addressType);
Table created.
SQL>
SQL>
SQL> INSERT INTO emp VALUES (101, "Adam", addressType("1 A St.","Mobile","AL","36608"));
1 row created.
SQL>
SQL> SELECT * FROM emp;
     EMPNO NAME
---------- --------------------
ADDRESS(STREET, CITY, STATE, ZIP)
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
       101 Adam
addressType("1 A St.", "Mobile", "AL", "36608")

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

Insert value to an objec table as normal table

 
SQL>
SQL> create or replace
  2  type address as object(
  3   id number,
  4   street varchar2(100),
  5   state varchar2(2),
  6   zipcode varchar(11)
  7  )
  8  /
Type created.
SQL>
SQL> create table address_table of address
  2  /
Table created.
SQL>
SQL>
SQL> insert into address_table values ( 1, "1910 Oracle Way", "VA", "21090" )
  2  /
1 row created.
SQL>
SQL> select * from address_table;

        ID
----------
STREET
--------------------------------------------------------------------------------
ST ZIPCODE
-- -----------
         1
1910 Oracle Way
VA 21090

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

Insert value to a specific column for an object table with insert...select statement

 
SQL>
SQL> create or replace type address_type
  2    as object
  3    ( city    varchar2(30),
  4      street  varchar2(30),
  5      state   varchar2(2),
  6      zip     number
  7    )
  8  /
Type created.
SQL> create or replace type person_type
  2    as object
  3    ( name             varchar2(30),
  4      dob              date,
  5      home_address     address_type,
  6      work_address     address_type
  7    )
  8  /
Type created.
SQL>
SQL>
SQL> create table people1 of person_type
  2  /
Table created.
SQL>
SQL>
SQL> insert into people1(name)
  2   select rownum from all_objects;
12213 rows created.
SQL>
SQL> drop table people1;
Table dropped.
SQL>
SQL> drop type person_type;
Type dropped.
SQL> drop type address_type;
Type dropped.
SQL> --

Insert value to one column in an object table

 
SQL>
SQL> create or replace type address_type
  2    as object
  3    ( city    varchar2(30),
  4      street  varchar2(30),
  5      state   varchar2(2),
  6      zip     number
  7    )
  8  /
Type created.
SQL> create or replace type person_type
  2    as object
  3    ( name             varchar2(30),
  4      dob              date,
  5      home_address     address_type,
  6      work_address     address_type
  7    )
  8  /
Type created.
SQL>
SQL>
SQL> create table people1 of person_type
  2  /
Table created.
SQL>
SQL>
SQL> insert into people1 (name)
  2   values ( "Hello World!" );
1 row created.
SQL>
SQL> select * from people1;
NAME                 DOB
-------------------- --------------------
HOME_ADDRESS(CITY, STREET, STATE, ZIP)
------------------------------------------------------------------------------------------------------------------------------------------------------
WORK_ADDRESS(CITY, STREET, STATE, ZIP)
------------------------------------------------------------------------------------------------------------------------------------------------------
Hello World!


1 row selected.
SQL>
SQL> drop table people1;
Table dropped.
SQL>
SQL> drop type person_type;
Type dropped.
SQL> drop type address_type;
Type dropped.
SQL> --

Insert value with type variable

 
SQL>
SQL>
SQL> create or replace
  2  type person as object(
  3   first_name varchar2(100),
  4   last_name varchar2(100) )
  5  /
Type created.
SQL>
SQL>
SQL> create table person_table( p person );
Table created.
SQL>
SQL>
SQL> declare
  2   l_person person;
  3  begin
  4   l_person := person( "C", "B" );
  5   insert into person_table
  6   values ( l_person );
  7  end;
  8  /
PL/SQL procedure successfully completed.
SQL>
SQL> select * from person_table;

P(FIRST_NAME, LAST_NAME)
--------------------------------------------------------------------------------
PERSON("C", "B")
SQL>
SQL> drop table person_table;
Table dropped.
SQL>
SQL>

Object table insert

 
SQL>
SQL> create or replace type address_type
  2    as object
  3    ( city    varchar2(30),
  4      street  varchar2(30),
  5      state   varchar2(2),
  6      zip     number
  7    )
  8  /
Type created.
SQL> create or replace type person_type
  2    as object
  3    ( name             varchar2(30),
  4      dob              date,
  5      home_address     address_type,
  6      work_address     address_type
  7    )
  8  /
Type created.
SQL>
SQL>
SQL> create table people1 of person_type
  2  /
Table created.
SQL>
SQL>
SQL> desc people
ERROR:
ORA-24372: invalid object for describe

SQL>
SQL>
SQL> insert into people1 values ( "Tom", "15-mar-1965",
  2    address_type( "Reston", "123 Main Street", "Va", "45678" ),
  3    address_type( "Redwood", "1 Oracle Way", "Ca", "23456" ) );
1 row created.
SQL> /
1 row created.
SQL>
SQL>
SQL> select * from people1;
NAME                 DOB
-------------------- --------------------
HOME_ADDRESS(CITY, STREET, STATE, ZIP)
------------------------------------------------------------------------------------------------------------------------------------------------------
WORK_ADDRESS(CITY, STREET, STATE, ZIP)
------------------------------------------------------------------------------------------------------------------------------------------------------
Tom                  15-MAR-1965 00:00:00
ADDRESS_TYPE("Reston", "123 Main Street", "Va", 45678)
ADDRESS_TYPE("Redwood", "1 Oracle Way", "Ca", 23456)
Tom                  15-MAR-1965 00:00:00
ADDRESS_TYPE("Reston", "123 Main Street", "Va", 45678)
ADDRESS_TYPE("Redwood", "1 Oracle Way", "Ca", 23456)

2 rows selected.
SQL>
SQL>
SQL> drop table people1;
Table dropped.
SQL> drop type person_type;
Type dropped.
SQL> drop type address_type;
Type dropped.
SQL>
SQL> --

One-step INSERTs into an object table

SQL>
SQL>
SQL> CREATE OR REPLACE TYPE addressType as OBJECT(
  2        street VARCHAR2(20),
  3        city VARCHAR2(20),
  4        state CHAR(2),
  5        zip CHAR(5));
  6  /
Type created.
SQL>
SQL> CREATE TABLE address_table OF addressType;
Table created.
SQL>
SQL> CREATE TABLE client (name VARCHAR2(20),
  2    address REF addressType scope is address_table);
Table created.
SQL>
SQL>
SQL> INSERT INTO client SELECT "Walsh", REF(aa) FROM address_table aa;
0 rows created.
SQL>
SQL> SELECT name, DEREF(address) FROM client;
no rows selected
SQL>
SQL>
SQL> drop table client;
Table dropped.
SQL>
SQL> drop table address_table;
Table dropped.
SQL>
SQL>

Use table function in insert statement for a user-defined type column

 
SQL>
SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
  2                    ENAME VARCHAR2(10),
  3                    JOB VARCHAR2(9),
  4                    MGR NUMBER(4),
  5                    HIREDATE DATE,
  6                    SAL NUMBER(7, 2),
  7                    COMM NUMBER(7, 2),
  8                    DEPTNO NUMBER(2));
Table created.
SQL>
SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK",    7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7521, "WARD",  "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER",  7839, TO_DATE("2-APR-1981",  "DD-MON-YYYY"), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10);
1 row created.
SQL>
SQL> CREATE TABLE DEPT (DEPTNO NUMBER(2),DNAME VARCHAR2(14),LOC VARCHAR2(13) );
Table created.
SQL>
SQL> INSERT INTO DEPT VALUES (10, "ACCOUNTING", "NEW YORK");
1 row created.
SQL> INSERT INTO DEPT VALUES (20, "RESEARCH", "DALLAS");
1 row created.
SQL> INSERT INTO DEPT VALUES (30, "SALES", "CHICAGO");
1 row created.
SQL> INSERT INTO DEPT VALUES (40, "OPERATIONS", "BOSTON");
1 row created.
SQL>
SQL> create or replace type emp_type
  2    as object
  3    (empno       number(4),
  4     ename       varchar2(10),
  5     job         varchar2(9),
  6     mgr         number(4),
  7     hiredate    date,
  8     sal         number(7, 2),
  9     comm        number(7, 2)
 10    );
 11  /
Type created.
SQL>
SQL> create or replace type emp_tab_type as table of emp_type;
  2  /
Type created.
SQL> create table dept_and_emp
  2    (deptno number(2) primary key,
  3     dname     varchar2(14),
  4     loc       varchar2(13),
  5     emps      emp_tab_type
  6    )
  7    nested table emps store as emps_nt;
Table created.
SQL>
SQL>
SQL>
SQL> insert into dept_and_emp
  2    select dept.*,
  3       CAST( multiset( select empno, ename, job, mgr, hiredate, sal, comm from emp ) AS emp_tab_type )
  4      from dept
  5  /
4 rows created.
SQL> insert into table
  2    ( select emps from dept_and_emp where deptno = 10 )
  3    values
  4    ( 1234, "NewEmp", "CLERK", 7782, sysdate, 1200, null );
1 row created.
SQL>
SQL>
SQL> drop table emp cascade constraints;
Table dropped.
SQL> drop table dept cascade constraints;
Table dropped.
SQL> drop table dept_and_emp;
Table dropped.
SQL> drop type emp_tab_type;
Type dropped.
SQL> drop type emp_type;
Type dropped.
SQL>
SQL> --