Comparing Object Values

You can compare the value of two objects in a WHERE clause of a query using the equality operator (=).


CREATE TYPE t_address AS OBJECT (
    street VARCHAR2(15),
    city VARCHAR2(15),
    state CHAR(2),
    zip VARCHAR2(5)
);
/

CREATE TYPE t_person AS OBJECT (
    id INTEGER,
    first_name VARCHAR2(10),
    last_name VARCHAR2(10),
    dob DATE,
    phone VARCHAR2(12),
    address t_address
);
/


CREATE TABLE emp OF t_person;

SQL> INSERT INTO emp VALUES (
  2       t_person(1, 'Jason', 'Brown', '01-FEB-1995', '999-555-9999',t_address(
'Main Street', 'Smalltown', 'CA', '12345'))
  3  );

1 row created.

SQL>


SQL> SELECT e.id, e.first_name, e.last_name, e.dob FROM emp e
  2  WHERE VALUE(e) = t_person(1, 'Jason', 'Brown', '01-FEB-1995', '999-555-9999',t_address(
  3  'Main Street', 'Smalltown', 'CA', '12345'));

        ID FIRST_NAME LAST_NAME  DOB
---------- ---------- ---------- ---------
         1 Jason      Brown      01-FEB-95

SQL>

You can also use the <> and IN operators in the WHERE clause:

 
SQL> SELECT e.id, e.first_name, e.last_name, e.dob
  2  FROM emp e WHERE VALUE(e) <>
  3  t_person(1, 'John', 'Brown', '28-FEB-1987', '111-111-1111',
  4  t_address('Main Street', 'Smalltown', 'CA', '12345')
  5  );

        ID FIRST_NAME LAST_NAME  DOB
---------- ---------- ---------- ---------
         1 Jason      Brown      01-FEB-95

SQL>

Home »
Oracle »
PL/SQL »

Object Types: