Q
Problem solve Get help with specific problems with your technologies, process and projects.

Comparing user-defined objects, part 1

Is there a way to compare two instances of a user-defined object? I have defined a TYPE:

CREATE OR REPLACE TYPE  rec_mgach
AS OBJECT
(
    gach_id         NUMBER (10),
    gach_dt_modif   DATE,
    mgach_bat_plais VARCHAR2 (2)
);
But if I try to compare two of them in a PL/SQL program, I get
 PLS-00526: A MAP or ORDER function is required for comparing
 objects in PL/SQL.

When you create an object, Oracle automatically creates a crude equality function, which you can use in WHERE clauses, such as

WHERE   a.mgach =  b.mgach  AND
        a.mgach != a.xgach
Two instances of an object are considered equal if none of their component members are NULL and each member in one equals the corresponding member in the other. To do anything else, you have to write either a map function that returns some primitive datatype (string, number or DATE), or an order function that returns a number. With these functions, you make the rules for what is considered "equal", "less than" or "greater than" when dealing with objects.

A map function returns a simple value that represents the object in comparison operations. Here's an example of a map function:
CREATE OR REPLACE TYPE  rec_mgach
AS OBJECT
(
    gach_id         NUMBER (10),
    gach_dt_modif   DATE,
    mgach_bat_plais VARCHAR2 (2),
    MAP MEMBER FUNCTION  to_varchar2
        RETURN VARCHAR2
);

CREATE OR REPLACE TYPE BODY  rec_mgach
AS

-- *****************************
-- **  t o _ v a r c h a r 2  **
-- *****************************

--  to_varchar2 returns a string that can be used for
--  comparing and sorting rec_mgach objects.

--  The rules for sorting are
--    (1) Compare mgach_bat_plais first, using normal 
--        (case-sensitive) string comparison
--    (2) In case of a tie, compare gach_dt_modif
--        (date only, ignore time of day)
--    (3) gach_id does not count in comparisons
--  In all cases, NULL values are considered 
--  "less" than any non-NULL value.

--  This function treats NULL mgach_bat_plais values 
--  as '  ' (two blanks).
--  If that is not less than any possible non-NULL value
--    of mgach_bat_plais, this function will have to be
--    revised.
--  Similarly NULL gach_dt_modif values are treated as if
--    they are in a year before 1 C.E.

MAP MEMBER FUNCTION  to_varchar2
RETURN VARCHAR2
IS
    dt_text  VARCHAR2 (10);
BEGIN
    IF  SELF.mgach_bat_plais  IS NULL
    THEN
        dt_text := '0000-00-00';
    ELSE
        dt_text := TO_CHAR (SELF.gach_dt_modif, 'YYYY-MM-DD');
    END IF;

    RETURN  RPAD ( NVL (SELF.mgach_bat_plais, '  '),
                   2
                 )
            ||
            dt_text;
END   to_varchar2;

END;
When you ask Oracle to compare two instances of an object that has a map function, it compares the results of the function instead. In other words, when you say
    IF  mgach1 = mgach2  THEN ...
Oracle acts as if you had said
    IF  mgach1.to_varchar2 () = 
        mgach2.to_varchar2 ()  THEN ...

Continue to Part 2...


This was last published in August 2003

Dig Deeper on Using Oracle PL-SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close