CREATE TYPE XR_ADDRESS_TYPE AS OBJECT (
 STREET VARCHAR2(40),
 CITY VARCHAR2(40),
 PROV VARCHAR2(40)
)|
CREATE TABLE XR_EMP_ADDR ( 
  EMPNO NUMBER(4) NOT NULL, 
  FNAME VARCHAR2(40), 
  LNAME VARCHAR2(40), 
  ADDRESS XR_ADDRESS_TYPE, 
  PRIMARY KEY (EMPNO) 
)|
CREATE PROCEDURE GET_EMPLOYEES_BY_PROV(X IN XR_ADDRESS_TYPE, Y OUT SYS_REFCURSOR) AS
BEGIN
    OPEN Y FOR SELECT * FROM XR_EMP_ADDR xrea WHERE xrea.ADDRESS.PROV LIKE X.PROV;
END;
|
INSERT INTO XR_EMP_ADDR (EMPNO, FNAME, LNAME, ADDRESS) VALUES (1, 'Mike', 'Norman', XR_ADDRESS_TYPE('Pinetrail','Nepean','Ont'))|
INSERT INTO XR_EMP_ADDR (EMPNO, FNAME, LNAME, ADDRESS) VALUES (2, 'Rick', 'Barkhouse', XR_ADDRESS_TYPE('Davis Side Rd','Carleton Place','Ont'))|
INSERT INTO XR_EMP_ADDR (EMPNO, FNAME, LNAME, ADDRESS) VALUES (3, 'Merrick', 'Schincariol', XR_ADDRESS_TYPE('do','not','know'))|