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.xgachTwo 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...
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.