Oracle PL/SQL Tutorial/Object Oriented/Object Table

Accessing the object table by reference

The built-in function ref returns a pointer to the object.

The variable you need to declare is not of type employeeType, but ref employeeType.

SQL>
SQL>
SQL> create type employeeType is object (
  2      empNo    NUMBER,
  3      eName    VARCHAR2(10),
  4      job      VARCHAR2(9),
  5      mgr      NUMBER,
  6      hireDate DATE,
  7      sal      NUMBER,
  8      comm     NUMBER,
  9      deptNo   NUMBER,
 10      member procedure p_changeName (i_newName_tx VARCHAR2),
 11      member function  f_getIncome_nr  return VARCHAR2
 12  )
 13  /
SP2-0816: Type created with compilation warnings
SQL>
SQL> create or replace type body employeeType as
  2      member function f_getIncome_nr return VARCHAR2 is
  3      begin
  4          return sal+comm;
  5      end f_getIncome_nr;
  6      member procedure p_changeName(i_newName_tx VARCHAR2) is
  7      begin
  8          eName:=i_newName_tx;
  9      end p_changeName;
 10  end;
 11  /
SP2-0818: Type Body created with compilation warnings
SQL>
SQL> create table t_emp of employeeType;
Table created.
SQL>
SQL>
SQL>
SQL> declare
  2      v_emp_oref ref employeeType;
  3      v_employeeType employeeType;
  4
  5  begin
  6      v_employeeType:=employeeType(100,"TestEmp",null,null,sysdate,1000,500,10);
  7      insert into t_emp values v_employeeType;
  8
  9
 10      select ref(t) into v_emp_oref from t_emp t where empNo=100;
 11
 12      update t_emp t set sal=sal+100 where ref(t)=v_emp_oref;
 13  end;
 14  /
PL/SQL procedure successfully completed.
SQL>
SQL> select * from t_emp;
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
       100 TestEmp                         09-JUN-07       1100        500         10
SQL>
SQL> drop table t_emp;
Table dropped.
SQL> drop type employeeType;
Type dropped.
SQL>

Alter table to add a user-defined type column

SQL>
SQL> create or replace type Address_Type
  2  as object
  3  (  street_addr1   varchar2(25),
  4     street_addr2   varchar2(25),
  5     city           varchar2(30),
  6     state          varchar2(2),
  7     zip_code       number
  8  )
  9  /
Type created.
SQL>
SQL> alter type Address_Type
  2  REPLACE
  3  as object
  4  (  street_addr1   varchar2(25),
  5     street_addr2   varchar2(25),
  6     city           varchar2(30),
  7     state          varchar2(2),
  8     zip_code       number,
  9     member function toString return varchar2,
 10     map member function mapping_function return varchar2
 11  )
 12  /
Type altered.
SQL>
SQL> create or replace type body Address_Type
  2  as
  3      member function toString return varchar2
  4      is
  5      begin
  6          if ( street_addr2 is not NULL )
  7          then
  8              return street_addr1 || " " ||
  9                     street_addr2 || " " ||
 10                     city || ", " || state || " " || zip_code;
 11          else
 12              return street_addr1 || " " ||
 13                     city || ", " || state || " " || zip_code;
 14          end if;
 15      end;
 16
 17      map member function mapping_function return varchar2
 18      is
 19      begin
 20          return to_char( nvl(zip_code,0), "fm00000" ) ||
 21                 lpad( nvl(city," "), 30 ) ||
 22                 lpad( nvl(street_addr1," "), 25 ) ||
 23                 lpad( nvl(street_addr2," "), 25 );
 24      end;
 25  end;
 26  /
Type body created.
SQL>
SQL>
SQL> create table people
  2  ( name           varchar2(10),
  3    home_address   address_type,
  4    work_address   address_type
  5  )
  6  /
Table created.
SQL>
SQL>
SQL> create or replace type Address_Array_Type
  2  as varray(50) of Address_Type
  3  /
Type created.
SQL>
SQL> alter table people add previous_addresses Address_Array_Type
  2  /
Table altered.
SQL> select name, prev.city, prev.state, prev.zip_code
  2    from people p, table( p.previous_addresses ) prev
  3   where prev.state = "PA";
no rows selected
SQL> /
no rows selected
SQL>
SQL> drop table people;
Table dropped.
SQL> drop type Address_Array_Type;
Type dropped.
SQL> drop type address_type;
Type dropped.

Create a table based on an Object with methods

SQL>
SQL> CREATE OR REPLACE TYPE address AS OBJECT
  2              (line1 VARCHAR2(20),
  3               line2 VARCHAR2(20),
  4               city VARCHAR2(20),
  5               state_code VARCHAR2(2),
  6               zip VARCHAR2(13),
  7    MEMBER FUNCTION get_address RETURN VARCHAR2,
  8    MEMBER PROCEDURE set_address
  9              (addressLine1 VARCHAR2,
 10               addressLine2 VARCHAR2,
 11               address_city VARCHAR2,
 12               address_state VARCHAR2,
 13               address_zip VARCHAR2)
 14  );
 15  /
Type created.
SQL> CREATE OR REPLACE TYPE BODY address AS
  2    MEMBER FUNCTION get_address RETURN VARCHAR2
  3    IS
  4    BEGIN
  5      RETURN (SELF.line1||" "||SELF.line2||" "||SELF.city||", "||SELF.state_code||" "||SELF.zip);
  6    END get_address;
  7    MEMBER PROCEDURE set_address (addressLine1 VARCHAR2,
  8                  addressLine2 VARCHAR2,
  9                  address_city VARCHAR2,
 10                  address_state VARCHAR2,
 11                  address_zip VARCHAR2)
 12    IS
 13    BEGIN
 14      line1 :=addressLine1;
 15
 16      line2 :=addressLine2;
 17
 18      city :=address_city;
 19
 20      state_code :=address_state;
 21
 22      zip :=address_zip;
 23
 24    END set_address;
 25  END;
 26  /
Type body created.
SQL>
SQL> CREATE TABLE address_master OF address;
Table created.
SQL>
SQL> INSERT INTO address_master VALUES (address("19 J","R Rd","Vancouver","NJ","00000"));
1 row created.
SQL>
SQL> select * from address_master;
LINE1                LINE2                CITY                 ST
-------------------- -------------------- -------------------- --
ZIP
-------------
19 J                 R Rd                 Vancouver            NJ
00000

1 row selected.
SQL>
SQL> drop table address_master;
Table dropped.
SQL>

Insert data just as one would ordinarily do with a common SQL table:

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 emp (empno NUMBER(3),
  2  name VARCHAR2(20),
  3  address addressType)
  4  /
Table created.
SQL>
SQL>
SQL> INSERT INTO emp VALUES (101, "Adam",
  2  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> CREATE TABLE address_table OF addressType;
Table created.
SQL>
SQL>
SQL> INSERT INTO address_table VALUES ("4 D St.", "Gulf","FL","32563");
1 row created.
SQL>
SQL>
SQL> SELECT city
  2  FROM address_table;
CITY
--------------------
Gulf
SQL>
SQL>
SQL> drop table address_table;
Table dropped.
SQL> drop table emp;
Table dropped.
SQL>
SQL> drop type addresstype;
Type dropped.
SQL>

Inserting a row into an object table

SQL>
SQL>
SQL> CREATE Or Replace TYPE ProductType AS OBJECT (
  2    id          NUMBER,
  3    name        VARCHAR2(15),
  4    description VARCHAR2(22),
  5    price       NUMBER(5, 2),
  6    days_valid  NUMBER
  7  )
  8  /
Type created.
SQL>
SQL> CREATE TABLE object_products OF ProductType
  2  /
Table created.
SQL>
SQL>
SQL> INSERT INTO object_products VALUES (
  2    ProductType(1, "AAA", "BBB", 3.95, 10)
  3  );
1 row created.
SQL>
SQL> select * from object_products;
 ID NAME            DESCRIPTION                 PRICE DAYS_VALID
--- --------------- ---------------------- ---------- ----------
  1 AAA             BBB                          3.95         10
SQL>
SQL> drop table object_products;
Table dropped.
SQL>
SQL>

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

One way to begin is to insert into the client table and null the addressType:

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
  2  /
Table created.
SQL>
SQL> CREATE TABLE client (name VARCHAR2(20),
  2    address REF addressType scope is address_table)
  3  /
Table created.
SQL>
SQL> DESC client;
 Name          Null?    Type
 ----------
 NAME                   VARCHAR2(20)
 ADDRESS                REF OF ADDRESSTYPE
SQL>
SQL> INSERT INTO client VALUES ("Jones",null);
1 row created.
SQL>
SQL>
SQL> SELECT *
  2  FROM client;
NAME                 ADDRESS
-------------------- --------------------------------------------------
Jones
SQL>
SQL>
SQL> drop table address_table;
Table dropped.
SQL> drop table client;
Table dropped.
SQL>
SQL> drop type addresstype;
Type dropped.
SQL>

One-step INSERTs into a Table that Contains Row Objects

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
  2  /
Table created.
SQL>
SQL> CREATE TABLE client (name VARCHAR2(20),
  2    address REF addressType scope is address_table);
Table created.
SQL>
SQL>
SQL> DESC client;
 Name       Null?    Type
 ----------
 NAME                VARCHAR2(20)
 ADDRESS             REF OF ADDRESSTYPE
SQL>
SQL> INSERT INTO client
  2  SELECT "Walsh", REF(aa)
  3  FROM address_table aa
  4  WHERE zip = "32563";
0 rows created.
SQL>
SQL> SELECT name, DEREF(address)
  2  FROM client;
no rows selected
SQL>
SQL>
SQL> select * from client;
no rows selected
SQL>
SQL> --DEREF (Dereference) the Row Addresses
SQL> SELECT name, DEREF(address)
  2  FROM client;
no rows selected
SQL>
SQL>
SQL> drop table address_table;
Table dropped.
SQL> drop table client;
Table dropped.
SQL>
SQL> drop type addressType;
Type dropped.

SELECT from the object-oriented table

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
  2  /
Table created.
SQL>
SQL> CREATE TABLE client (name VARCHAR2(20),
  2    address REF addressType scope is address_table);
Table created.
SQL>
SQL> DESC client;
 Name      Null?    Type
 ----------    
 NAME               VARCHAR2(20)
 ADDRESS            REF OF ADDRESSTYPE
SQL>
SQL> INSERT INTO client VALUES ("Jones",null);
1 row created.
SQL>
SQL>
SQL> SELECT *
  2  FROM client;
NAME                 ADDRESS
-------------------- --------------------------------------------------
Jones
SQL>
SQL>
SQL> UPDATE client SET address =
  2  (SELECT REF(aa)
  3  FROM address_table aa
  4  WHERE aa.city LIKE "Mob%")
  5  WHERE name = "Jones"
  6
SQL> select * from client;
NAME                 ADDRESS
-------------------- --------------------------------------------------
Jones
SQL>
SQL> drop table address_table;
Table dropped.
SQL> drop table client;
Table dropped.
SQL> drop type addresstype;
Type dropped.
SQL>
SQL>

SELECTing Individual Columns in Table that Contains Row Objects

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
  2  /
Table created.
SQL>
SQL> CREATE TABLE client (name VARCHAR2(20),
  2    address REF addressType scope is address_table);
Table created.
SQL>
SQL>
SQL> DESC client;
 Name      Null?    Type
 ---------
 NAME               VARCHAR2(20)
 ADDRESS            REF OF ADDRESSTYPE
SQL>
SQL> INSERT INTO client
  2  SELECT "Walsh", REF(aa)
  3  FROM address_table aa
  4  WHERE zip = "32563";
0 rows created.
SQL>
SQL> SELECT c.name, c.address.city
  2  FROM client c;
no rows selected
SQL>
SQL>
SQL> drop table address_table;
Table dropped.
SQL> drop table client;
Table dropped.
SQL>
SQL> drop type addressType;
Type dropped.
SQL>

Selecting Rows from the object Table

SQL>
SQL>
SQL> CREATE or Replace TYPE ProductType AS OBJECT (
  2    id          NUMBER,
  3    name        VARCHAR2(15),
  4    description VARCHAR2(22),
  5    price       NUMBER(5, 2),
  6    days_valid  NUMBER
  7  )
  8  /
Type created.
SQL>
SQL> CREATE TABLE object_products OF ProductType
  2  /
Table created.
SQL>
SQL> INSERT INTO object_products (
  2    id, name, description, price, days_valid
  3  ) VALUES (
  4    2, "AAA", "BBB", 2.99, 5
  5  );
1 row created.
SQL>
SQL> select * from object_products;
 ID NAME            DESCRIPTION                 PRICE DAYS_VALID
--- --------------- ---------------------- ---------- ----------
  2 AAA             BBB                          2.99          5
SQL>
SQL> drop table object_products;
Table dropped.
SQL>
SQL>

UPDATE a Table that Contains Row Objects (TCRO)

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
  2  /
Table created.
SQL>
SQL> CREATE TABLE client (name VARCHAR2(20),
  2    address REF addressType scope is address_table);
Table created.
SQL>
SQL>
SQL> DESC client;
 Name        Null?    Type
 ----------
 NAME                 VARCHAR2(20)
 ADDRESS              REF OF ADDRESSTYPE
SQL>
SQL> INSERT INTO client VALUES ("Jones",null);
1 row created.
SQL>
SQL>
SQL> SELECT *
  2  FROM client;
NAME                 ADDRESS
-------------------- --------------------------------------------------
Jones
SQL>
SQL>
SQL> UPDATE client SET address =
  2  (SELECT REF(aa)
  3  FROM address_table aa
  4  WHERE aa.city LIKE "Mob%")
  5  WHERE name = "Jones"
  6
SQL> select * from client;
NAME                 ADDRESS
-------------------- --------------------------------------------------
Jones
SQL>
SQL> drop table address_table;
Table dropped.
SQL> drop table client;
Table dropped.
SQL> drop type addresstype;
Type dropped.

Updating a Row in the object Table

SQL>
SQL>
SQL>
SQL> CREATE Or Replace TYPE ProductType AS OBJECT (
  2    id          NUMBER,
  3    name        VARCHAR2(15),
  4    description VARCHAR2(22),
  5    price       NUMBER(5, 2),
  6    days_valid  NUMBER
  7  )
  8  /
Type created.
SQL>
SQL> CREATE TABLE object_products OF ProductType
  2  /
Table created.
SQL>
SQL> INSERT INTO object_products (
  2    id, name, description, price, days_valid
  3  ) VALUES (
  4    1, "AAA", "BBB", 2.99, 5
  5  );
1 row created.
SQL>
SQL> select * from object_products;
 ID NAME            DESCRIPTION                 PRICE DAYS_VALID
--- --------------- ---------------------- ---------- ----------
  1 AAA             BBB                          2.99          5
SQL>
SQL> UPDATE object_products
  2  SET description = "new"
  3  WHERE id = 1;
1 row updated.
SQL> select * from object_products;
 ID NAME            DESCRIPTION                 PRICE DAYS_VALID
--- --------------- ---------------------- ---------- ----------
  1 AAA             new                          2.99          5
SQL>
SQL> drop table object_products;
Table dropped.
SQL>

You can also use an object type to define an entire table, and the table is known as an object table.

SQL>
SQL>
SQL> CREATE or replace TYPE EmployeeType AS OBJECT (
  2    id          NUMBER,
  3    name        VARCHAR2(15),
  4    description VARCHAR2(22),
  5    salary       NUMBER(5, 2)
  6  );
  7  /
Type created.
SQL> CREATE TABLE object_employee OF EmployeeType;
Table created.
SQL>
SQL> desc object_employee;
 Name                Null?    Type
 ID                           NUMBER
 NAME                         VARCHAR2(15)
 DESCRIPTION                  VARCHAR2(22)
 SALARY                       NUMBER(5,2)
SQL>
SQL> drop table object_employee;
Table dropped.
SQL>
SQL>
SQL>