PL/SQL Raw datatype variable comparison

Question

Is it possible to compare variables of raw datatypes? I'm working with XMLDOM.DomNodes, which is records with one field by itself:

TYPE DOMNode IS RECORD (id RAW(12));

So I have two nodes, then could I compare them by their id fields? I tried several samples and at first glance it seems to work:

  FUNCTION findParentNode(p_node IN xmldom.domnode) RETURN PLS_INTEGER
  AS
      nRetVal               PLS_INTEGER;
  BEGIN
     FOR i IN ParentNodes.First .. ParentNodes.Last
     LOOP 
         IF ParentNodes(i).id = p_node.id THEN               
            nRetVal := i;
            EXIT;
         END IF;         
     END LOOP;

     RETURN nRetVal;
  END;

but one thing in Oracle documentation worries me: Raw data is like VARCHAR2 data, except that PL/SQL does not interpret raw data What does it mean? If pl/sql doesn't interpret raw, then could it compare?

score 6 · Accepted Answer · answered Feb 10 '12 at 09:10

You can use the = operator if you want to see if two RAW variables have the same values.

SQL> DECLARE
  2     a RAW(255) := utl_raw.cast_to_raw('abcdef');
  3     b RAW(50) := utl_raw.cast_to_raw('abcdef');
  4  BEGIN
  5     IF a = b THEN
  6        dbms_output.put_line('a = b');
  7     ELSE
  8        dbms_output.put_line('a != b');
  9     END IF;
 10  END;
 11  /
a = b

When the documentation states that RAW is like VARCHAR2, but not interpreted, it means that you can affect, store and even compare RAW variables just like you would VARCHAR2, but the binary value is never matched against a character set.

VARCHAR2 variables, on the other hand, can be converted because of a mismatch between the character sets of the database and the client.

RAW is a string of bytes instead of a string of characters.

score 2 · Answer 2 · answered Feb 10 '12 at 07:39

I'm not sure what the documentation means by that but to compare two raws I'd use the UTL_RAW.COMPARE function. See here for the details.

UTL_RAW.COMPARE compares one raw value to another raw value. If they are identical, then UTL_RAW.COMPARE returns zero. If they are not identical, then COMPARE returns the position of the first byte that does not match. If the input values have different lengths, then the shorter input value is padded on the right by a value that you specify.

It seems to me that = operator also can compare raws, but if it goes wrong, I'll use your advice. Thanks — Andrey Khataev, Feb 10 '12 at 10:20

score -1 · Answer 3 · answered Feb 14 '13 at 21:50

-1

You must use:

IF(NVL(a,'X') != NVL(b,'Y')) THEN
.....

Oracle does not compare empty/null strings correctly, so you must specify that if it is null or empty, then give it a different meaning than the other.

answered Feb 14 '13 at 21:50

Israel Margulies

8,656
2
30
26

PL/SQL Raw datatype variable comparison

3 Answers3