Creation of a REF Request and REF Passing Method Set

CREATE OR REPLACE TYPE BODY emp_t IS
MEMBER FUNCTION give_ref(id IN integer)
  RETURN REF emp_t  IS
  ref_val REF emp_t;
BEGIN
  SELECT REF(e) INTO ref_val
  FROM employees e
  SEE CODE DEPOT FOR FULL SCRIPTS
END;
END;
/
 
CREATE or replace TYPE company_t (
   company_name          VARCHAR2(32),
   company_business     VARCHAR2(32),
   company_contact        name_t,
   contact_home              VARCHAR2(20))
/
 
rem
rem The job_sites object is an independent one-to-many
rem from employees. We will REF emp_t and EMPLOYEES
rem
 
CREATE OR REPLACE TYPE site_t as object (
  site_id                          INTEGER,
  company                      company_t,
  address                       address_t,
  employee_id_r             REF emp_t,
MEMBER FUNCTION get_ref
   (emp_id IN integer)
   RETURN REF emp_t)
/

rem
rem job_sites is an object table definition
rem

SEE CODE DEPOT FOR FULL SCRIPTS

If you like Oracle tuning, you may enjoy the book Oracle Tuning: The Definitive Reference , with over 900 pages of BC's favorite tuning tips & scripts.  You can buy it directly from the publisher and save 30%, and get instant access to the code depot of Oracle tuning scripts.