Method Basics

When declaring map and order methods for comparative use of user defined data types, it is important to note that there can be only one map or order method per type. One or the other may be specified, but not both. The following code example illustrates the use of map methods:

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,
MAP MEMBER FUNCTION give_site_id
RETURN INTEGER)
/
 
CREATE TYPE BODY site_t AS
 MEMBER FUNCTION get_ref (emp_id IN integer)
 RETURN INTEGER IS
 status INTEGER:=0;
 BEGIN
    SELF.employee_id_r := emp_t.give_ref(emp_id);
    RETURN status;
 EXCEPTION
 WHEN others THEN
    status := 1;
    RETURN status;
 END;
 MAP MEMBER FUNCTION give_site_id
 RETURN INTEGER IS
 BEGIN
      RETURN site_id;
 END;
END;

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.