9.2.3.1.1 Restrictions

This program asserts the following purity level with the RESTRICT_REFERENCES pragma:

PRAGMA RESTRICT_REFERENCES(BIT_AND, WNDS, RNDS, WNPS, RNPS);

9.2.3.1.2 Example

To check if a bit is turned on in a bit flag variable using a bitmask, you can use the BIT_AND function. This section of example code also uses the BIT_OR function to merge bitmasks:

DECLARE   fourteenth     VARCHAR2(8);   fifteenth      VARCHAR2(8);   twentieth      VARCHAR2(8);   mask           RAW(4);   bitfield1      VARCHAR2(8);   bitfield2      VARCHAR2(8); BEGIN   /* set bitfield1 for the 15th through 18th */   bitfield1 := '0003C000';    /* set bitfield2 for the 26st */   bitfield2 := '02000000';    /* set the mask for the 14th */   fourteenth := '00002000';    /* set the mask for the 15th */   fifteenth := '00004000';    /* set the mask for the 20th */   twentieth := '00080000';    /* merge the masks for the 14th, 15th and 20th */   mask := UTL_RAW.BIT_OR(hextoraw(fourteenth),hextoraw(fifteenth));   mask := UTL_RAW.BIT_OR(mask,hextoraw(twentieth));    /* check to see if the bitfields have the 14th, 15th, or 20th set */   if 
UTL_RAW.BIT_AND
(mask,hextoraw(bitfield1)) = '00000000' then     dbms_output.put_line('bitfield1 is not set');   else     dbms_output.put_line('bitfield1 is set');   end if;    if UTL_RAW.BIT_AND(mask,hextoraw(bitfield2)) = '00000000' then     dbms_output.put_line('bitfield2 is not set');   else     dbms_output.put_line('bitfield2 is set');   end if;  END;

This is the output from this code:

Bitfield1 is set  Bitfield2 is 

Anot 

set

9.2.3.2.1 Restrictions

This program asserts the following purity level with the RESTRICT_REFERENCES pragma:

PRAGMA RESTRICT_REFERENCES(BIT_COMPLEMENT, WNDS, RNDS, WNPS, RNPS);

9.2.3.2.2 Example

To turn off a bit, regardless of its original state, in a bit flag variable using a bitmap, you can use the BIT_COMPLEMENT function together with the BIT_AND function.

DECLARE   fourteenth     VARCHAR2(8);   fifteenth      VARCHAR2(8);   twentieth      VARCHAR2(8);   mask           RAW(4);   bitfield1      VARCHAR2(8);   bitfield2      VARCHAR2(8); BEGIN   /* set the bitfield for the 15th through 18th */   bitfield1 := '0003C000';    /* set the bitfield for the 26st */   bitfield2 := '02000000';    /* set the mask for the 14th */   fourteenth := '00002000';    /* set the mask for the 15th */   fifteenth := '00004000';    /* set the mask for the 20th */   twentieth := '00080000';    /* merge the masks for the 14th, 15th and 20th */   mask := UTL_RAW.BIT_OR(hextoraw(fourteenth),hextoraw(fifteenth));   mask := UTL_RAW.BIT_OR(mask,hextoraw(twentieth));   mask := UTL_RAW.BIT_OR(mask,hextoraw(twentieth));    /* check to see if the bitfields have the 14th, 15th, or 20th set */   if UTL_RAW.BIT_AND(mask,hextoraw(bitfield1)) = '00000000' then     dbms_output.put_line('bitfield1 is not set');   else     dbms_output.put_line('bitfield1 is set');   end if;    if UTL_RAW.BIT_AND(mask,hextoraw(bitfield2)) = '00000000' then     dbms_output.put_line('bitfield2 is not set');   else     dbms_output.put_line('bitfield2 is set');   end if;    /* turn off bit 15 in the mask */   mask := UTL_RAW.BIT_AND(mask,UTL_RAW.BIT_COMPLEMENT(hextoraw(fifteenth)));    /* check to see if the bitfield1 has the 14th, 15th, or 20th set */   if UTL_RAW.BIT_AND(mask,hextoraw(bitfield1)) = '00000000' then     dbms_output.put_line('bitfield1 is not set');   else     dbms_output.put_line('bitfield1 is set');   end if; END;

This is the output from the above code:

bitfield1 is set bitfield2 is not set bitfield1 is not set

9.2.3.3.1 Restrictions

This program asserts the following purity level with the RESTRICT_REFERENCES pragma:

PRAGMA RESTRICT_REFERENCES(BIT_OR, WNDS, RNDS, WNPS, RNPS);

9.2.3.3.2 Example

To turn on a bit in a bit flag variable using a bitmask, or to merge bitmasks, you can use the BIT_OR function, as shown in the example from BIT_AND.

9.2.3.4.1 Restrictions

This program asserts the following purity level with the RESTRICT_REFERENCES pragma:

PRAGMA RESTRICT_REFERENCES(BIT_XOR, WNDS, RNDS, WNPS, RNPS);

9.2.3.4.2 Example

To toggle a bit (if it is off, turn it on, and if it is on, turn it off) in a bit flag variable using a bitmask, use the BIT_XOR function as follows:

DECLARE   fourteenth     VARCHAR2(8);   fifteenth      VARCHAR2(8);   twentieth      VARCHAR2(8);   mask           RAW(4);   bitfield1      VARCHAR2(8);   bitfield2      VARCHAR2(8); BEGIN   /* set the bitfield for the 15th through 18th */   bitfield1 := '0003C000';   /* set the bitfield for the 26st */   bitfield2 := '02000000';   /* set the mask for the 14th */   fourteenth := '00002000';   /* set the mask for the 15th */   fifteenth := '00004000';   /* set the mask for the 20th */   twentieth := '00080000';   /* merge the masks for the 14th, 15th and 20th */   mask := UTL_RAW.BIT_OR (HEXTORAW (fourteenth),HEXTORAW (fifteenth));   mask := UTL_RAW.BIT_OR (mask, HEXTORAW (twentieth));   /* check to see IF the bitfields have the 14th  or 20th set */   IF UTL_RAW.BIT_AND (mask, HEXTORAW (bitfield1)) = '00000000' THEN     DBMS_OUTPUT.PUT_LINE ('bitfield1 is not set');   ELSE     DBMS_OUTPUT.PUT_LINE ('bitfield1 is set');   END IF;   IF UTL_RAW.BIT_AND (mask, HEXTORAW (bitfield2)) = '00000000' THEN     DBMS_OUTPUT.PUT_LINE ('bitfield2 is not set');   ELSE     DBMS_OUTPUT.PUT_LINE ('bitfield2 is set');   END IF;   /* toggle bit 15 in the mask */   mask := UTL_RAW.BIT_XOR (mask, HEXTORAW (fifteenth));   /* check to see IF the bitfield1 has the 14th, 15th, or 20th set */   IF UTL_RAW.BIT_AND (mask, HEXTORAW (bitfield1)) = '00000000' THEN     DBMS_OUTPUT.PUT_LINE ('bitfield1 is not set');   ELSE     DBMS_OUTPUT.PUT_LINE ('bitfield1 is set');   END IF; END; /

This is the output from the previous example:

bitfield1 is set bitfield2 is not set bitfield1 is 



not 

set

9.2.3.5.1 Restrictions

This program asserts the following purity level with the RESTRICT_REFERENCES pragma:

PRAGMA RESTRICT_REFERENCES(CAST_TO_RAW, WNDS, RNDS, WNPS, RNPS);

9.2.3.5.2 Example

For an example of CAST_TO_RAW, see " Section 9.2.3.15, "The UTL_RAW. TRANSLATE function" " later in this chapter.

9.2.3.6.1 Restrictions

This program asserts the following purity level with the RESTRICT_REFERENCES pragma:

PRAGMA RESTRICT_REFERENCES(CAST_TO_VARCHAR2, WNDS, RNDS, WNPS, RNPS);

9.2.3.6.2 Example

The data dictionary views USER_TAB_COLUMNS, ALL_TAB_COLUMNS, and DBA_TAB_COLUMNS have the first 32 bytes of the lowest and highest data values for each column in analyzed tables. Unfortunately, this data is of data type RAW and not very readable by humans. The CAST_TO_VARCHAR2 function can be used on character datatype columns to see these data in more readable form.

SELECT column_name, UTL_RAW.CAST_TO_VARCHAR2(low_value)       ,UTL_RAW.CAST_TO_VARCHAR2(high_value) FROM  user_tab_columns WHERE table_name = 'FOO_TAB'  AND  column_name = 'VCHAR1'

9.2.3.7.1 Restrictions

This program asserts the following purity level with the RESTRICT_REFERENCES pragma:

PRAGMA RESTRICT_REFERENCES(COMPARE, WNDS, RNDS, WNPS, RNPS);

9.2.3.7.2 Example

Here is an example of the COMPARE function:

DECLARE   r_string1      RAW(16);   r_string2      RAW(16);   diff_position  INTEGER;  BEGIN   r_string1 := UTL_RAW.CAST_TO_RAW('test string1');   r_string2 := UTL_RAW.CAST_TO_RAW('test string2');   diff_position := UTL_RAW.COMPARE(r_string1,r_string2);   DBMS_OUTPUT.PUT_LINE (      'r_string1='|| UTL_RAW.CAST_TO_VARCHAR2(r_string1));   DBMS_OUTPUT.PUT_LINE (      'r_string2='|| UTL_RAW.CAST_TO_VARCHAR2(r_string2));   DBMS_OUTPUT.PUT_LINE ('diff_position='|| diff_position); END; /

Sample output follows:

r_string1=test string1 r_string2=test string2 diff_position=12

9.2.3.8.1 Exceptions

The VALUE_ERROR exception (ORA-6502) is raised if the returned raw string exceeds 32K. The documentation from Oracle 7.3 and 8.0 indicates that this is to be revised in a future release , so don't count on this exception to remain unchanged.

9.2.3.8.2 Restrictions

This program asserts the following purity level with the RESTRICT_REFERENCES pragma:

PRAGMA RESTRICT_REFERENCES(CONCAT, WNDS, RNDS, WNPS, RNPS);

9.2.3.8.3 Example

For an example of CONCAT, see the example for TRANSLATE.

9.2.3.9.1 Exceptions

The VALUE_ERROR exception (ORA-6502) is raised if the input raw string is missing, NULL, or has zero length. This exception is also raised if the from_charset or to_charset parameters are missing, NULL, zero length, or name an invalid character set. The documentation from both Oracle 7.3 and 8.0 indicates that this is to be revised in a future release , so don't count on this exception to remain unchanged.

9.2.3.9.2 Restrictions

This program asserts the following purity level with the RESTRICT_REFERENCES pragma:

PRAGMA RESTRICT_REFERENCES(CONVERT, WNDS, RNDS, WNPS, RNPS);

9.2.3.10.1 Exceptions

The VALUE_ERROR exception (ORA-6502) is raised if the input raw string r is missing, NULL, or has zero length. This exception is also raised if the input number of copies n is less than 1 (n < 1). The documentation from both Oracle 7.3 and 8.0 indicates that this is to be revised in a future release , so don't count on this exception to remain unchanged.

9.2.3.10.2 Restrictions

This program asserts the following purity level with the RESTRICT_REFERENCES pragma:

PRAGMA RESTRICT_REFERENCES(COPIES, WNDS, RNDS, WNPS, RNPS);

9.2.3.10.3 Example

Here is an example of the COPIES function:

DECLARE   r_string1      RAW(64);   r_repeat       RAW(16);  BEGIN   r_repeat := UTL_RAW.CAST_TO_RAW('Test ');   r_string1 := UTL_RAW.COPIES(r_repeat,4);   DBMS_OUTPUT.PUT_LINE (      'r_string1='||UTL_RAW.CAST_TO_VARCHAR2(r_string1)); END; /

Sample output follows:

r_string1=Test Test Test Test

9.2.3.11.1 Restrictions

This program asserts the following purity level with the RESTRICT_REFERENCES pragma:

PRAGMA RESTRICT_REFERENCES(LENGTH, WNDS, RNDS, WNPS, RNPS);

9.2.3.11.2 Example

Here is an example of the LENGTH function:

  r_1            RAW(32000);   r_2            RAW(32000);   r_3            RAW(32000); BEGIN   r_1 := UTL_RAW.XRANGE (hextoraw('00'),hextoraw('FF'));   r_2 := UTL_RAW.CONCAT (r_1,r_1,r_1,r_1,r_1,r_1,r_1,r_1);   r_3 := UTL_RAW.CONCAT (r_2,r_2,r_2,r_2,r_2,r_2,r_2,r_2);   DB<S_OUTPUT.PUT_LINE ('Length of r_1='||UTL_RAW.LENGTH(r_1));   DBMS_OUTPUT.PUT_LINE ('Length of r_2='||UTL_RAW.LENGTH(r_2));   DBMS_OUTPUT.PUT_LINE ('Length of r_3='||UTL_RAW.LENGTH(r_3)); END; /

Sample output follows:

Length of r_1=256 Length of r_2=2048 Length of r_3=16384

9.2.3.12.1 Exceptions

The VALUE_ERROR exception (ORA-6502) is raised if one of the folowing occurs:

• The input raw string overlay is NULL or has zero length

• The input target is missing or undefined

• The length of the target exceeds the maximum length of a raw, len < 0, or pos < 1

The documentation from both version 7.3 and 8.0 indicates that this is to be revised in a future release , so don't count on this exception to remain unchanged.

9.2.3.12.2 Restrictions

This program asserts the following purity level with the RESTRICT_REFERENCES pragma:

PRAGMA RESTRICT_REFERENCES(OVERLAY, WNDS, RNDS, WNPS, 

RNPS)

;

9.2.3.12.3 Example

Here is an example of the OVERLAY function:

DECLARE   r_input        RAW(40);   r_overlay      RAW(40);   start_position INTEGER;   overlay_length INTEGER;   r_pad          RAW(2);   r_output       RAW(40);  BEGIN   -- set the parameters   r_input := UTL_RAW.CAST_TO_RAW (      'This is the full length text string');   r_overlay := UTL_RAW.CAST_TO_RAW ('overlaid part');   start_position := 13;   overlay_length := 8;   r_pad := UTL_RAW.CAST_TO_RAW ('.');   r_output := UTL_RAW.OVERLAY (      r_overlay, r_input, start_position, overlay_length,r_pad);    DBMS_OUTPUT.PUT_LINE (      'r_input        ='|| utl_raw.cast_to_varchar2(r_input));   DBMS_OUTPUT.PUT_LINE (      'r_output(len 8)='|| UTL_RAW.CAST_TO_VARCHAR2(r_output));   overlay_length := 16;   r_output := UTL_RAW.OVERLAY (      r_overlay, r_input, start_position , overlay_length, r_pad);   DBMS_OUTPUT.PUT_LINE (      'r_output(len16)='|| UTL_RAW.CAST_TO_VARCHAR2(r_output)); END; /

Sample output follows:

r_input        =This is the full length text string r_output(len 8)=This is the overlaidgth text string r_output(len16)=This is the overlaid part... string

9.2.3.13.1 Exceptions

The VALUE_ERROR exception (ORA-6502) is raised if the input raw string (r) is null or has zero length. The documentation from both Oracle 7.3 and 8.0 indicates that this is to be revised in a future release , so don't count on this exception to remain unchanged.

9.2.3.13.2 Restrictions

This program asserts the following purity level with the RESTRICT_REFERENCES pragma:

PRAGMA RESTRICT_REFERENCES(REVERSE, WNDS, RNDS, WNPS, RNPS);

9.2.3.13.3 Example

Here is an example of the REVERSE function:

DECLARE   r_string       RAW(16);   r_reverse      RAW(16);  BEGIN   r_string := UTL_RAW.CAST_TO_RAW('Java Beans');   r_reverse := UTL_RAW.REVERSE(r_string);   DBMS_OUTPUT.PUT_LINE (      'r_string='|| UTL_RAW.CAST_TO_VARCHAR2(r_string));   DBMS_OUTPUT.PUT_LINE (      'r_reverse='|| UTL_RAW.CAST_TO_VARCHAR2(r_reverse)); END;

Sample output follows:

r_string=Java Beans r_reverse=snaeB avaJ

9.2.3.14.1 Exceptions

The VALUE_ERROR exception (ORA-6502) is raised if pos is 0 or len is less than 0. The documentation from both Oracle 7.3 and 8.0 indicates that this is to be revised in a future release , so don't count on this exception to remain unchanged.

9.2.3.14.2 Restrictions

This program asserts the following purity level with the RESTRICT_REFERENCES pragma:

PRAGMA RESTRICT_REFERENCES(SUBSTR, WNDS, RNDS, WNPS, RNPS);

9.2.3.14.3 Example

Here is an example of the SUBSTR function:

DECLARE   r_string       RAW(32);   r_substring    RAW(16);  BEGIN   r_string := UTL_RAW.CAST_TO_RAW('This is the test string');   r_substring := UTL_RAW.SUBSTR(r_string,9,8);   DBS_OUTPUT.PUT_LINE (      'r_string='|| UTL_RAW.CAST_TO_VARCHAR2(r_string));   DBMS_OUTPUT,PUT_LINE (      'r_substring='|| UTL_RAW.CAST_TO_VARCHAR2(r_substring)); END;

Sample output follows:

r_string=This is the test string r_substring=the test

9.2.3.15.1 Exceptions

The VALUE_ERROR exception (ORA-6502) is raised if the r, from_set, or to_set parameters are NULL or have zero length. The documentation from both Oracle 7.3 and 8.0 indicates that this is to be revised in a future release , so don't count on this exception to remain unchanged.

9.2.3.15.2 Restrictions

This program asserts the following purity level with the RESTRICT_REFERENCES pragma:

PRAGMA RESTRICT_REFERENCES(TRANSLATE, WNDS, RNDS, WNPS, RNPS);

9.2.3.15.3 Example

An example use of TRANSLATE is a switch case function that switches the case of every character in a text string, swapping upper and lowercase characters. This function also makes use of other UTL_RAW functions: CAST_TO_RAW, XRANGE, and CONCAT. This method may not be the most efficient case-switching technique, but it serves to demonstrate the functions nicely.

CREATE OR REPLACE FUNCTION switch_case(c_in IN VARCHAR2) RETURN VARCHAR2 IS   r_in           RAW(2000);   r_out          RAW(2000);   r_upper        RAW(32);   r_lower        RAW(32);   r_upper_lower  RAW(64);   r_lower_upper  RAW(64);  BEGIN   /* Convert input to raw */   r_in := UTL_RAW.CAST_TO_RAW(c_in);    /* Get raw string of uppercase letters from 'A' to 'Z' */   r_upper := UTL_RAW.XRANGE(UTL_RAW.CAST_TO_RAW('A'),      UTL_RAW.CAST_TO_RAW('Z'));      /* Get raw string of lowercase letters from 'a' to 'z' */   r_lower := UTL_RAW.XRANGE(UTL_RAW.CAST_TO_RAW('a'),      UTL_RAW.CAST_TO_RAW('z'));   /* Create a raw string of uppercase followed by lowercase letters */   r_upper_lower := UTL_RAW.CONCAT(r_upper , r_lower);   /* Create a raw string of lowercase followed by uppercase letters */   r_lower_upper := UTL_RAW.CONCAT(r_lower , r_upper);    /* Translate upper to lower and lower to upper for the input string */   r_out := UTL_RAW.TRANSLATE(r_in , r_upper_lower , r_lower_upper );    /* Convert the result back to varchar2 and return the result */   return(UTL_RAW.CAST_TO_VARCHAR2(r_out)); END; /

Sample output follows:

SQL> select switch_case('This Is A Test') from dual;  SWITCH_CASE('THISISATEST') ---------------------------------------------------- tHIS iS a tEST

9.2.3.16.1 Exceptions

The VALUE_ERROR exception (ORA-6502) is raised if r is null or has 0 length. The documentation from both Oracle 7.3 and 8.0 indicates that this is to be revised in a future release , so don't count on this exception to remain unchanged.

9.2.3.16.2 Restrictions

This program asserts the following purity level with the RESTRICT_REFERENCES pragma:

PRAGMA RESTRICT_REFERENCES(TRANSLITERATE, WNDS, RNDS, WNPS, RNPS);

9.2.3.16.3 Example

An example use of TRANSLITERATE is a make_lower function that switches uppercase characters in a text string to lowercase characters, converting spaces, dashes, and dots to underscores. This function also makes use of other UTL_RAW functions: CAST_TO_RAW, XRANGE, and CONCAT. This method may not be the most efficient technique for this conversion, but it serves to demonstrate some UTL_RAW functions in an easily understandable context.

CREATE OR REPLACE FUNCTION make_lower(c_in IN VARCHAR2) RETURN VARCHAR2 IS   r_in           RAW(2000);   r_out          RAW(2000);   r_upper        RAW(48);   r_lower        RAW(32);   r_underscore   RAW(1); BEGIN   -- convert the input to raw   r_in := UTL_RAW.CAST_TO_RAW(c_in);   r_underscore := UTL_RAW.CAST_TO_RAW('_');   -- start the from characters with the uppercase letters   r_upper := UTL_RAW.XRANGE(UTL_RAW.CAST_TO_RAW('A'),UTL_RAW.CAST_TO_RAW('Z'));   -- space, dash and dot to the from list of characters   r_upper := UTL_RAW.CONCAT(r_upper,UTL_RAW.CAST_TO_RAW(' ')             ,UTL_RAW.CAST_TO_RAW('-'),UTL_RAW.CAST_TO_RAW('.'));   -- set the to characters to be lowercase letters   r_lower := UTL_RAW.XRANGE(UTL_RAW.CAST_TO_RAW('a'),UTL_RAW.CAST_TO_RAW('z'));   -- convert the uppercase to lowercase and punctuation marks to underscores   r_out := UTL_RAW.TRANSLITERATE(r_in , r_lower , r_upper, r_underscore);   -- return the character version   return(UTL_RAW.CAST_TO_VARCHAR2(r_out)); END;

Sample output follows:

SQL> exec DBMS_OUTPUT.PUT_LINE (make_lower('This.is-A tEst')); this_is_a_test

9.2.3.17.1 Restrictions

This program asserts the following purity level with the RESTRICT_REFERENCES pragma:

PRAGMA RESTRICT_REFERENCES(XRANGE, WNDS, RNDS, WNPS, RNPS);

9.2.3.17.2 Example

For an example of XRANGE, see the example for TRANSLATE or TRANSLITERATE.

9.2.4.1.1 UTL_REF programs

Table 9.5 lists the programs defined for the UTL_REF packages.

UTL_REF does not declare any nonprogram elements.

9.2.4.1.2 UTL_REF exceptions

UTL_REF does not declare any exceptions. However, you may encounter any of the following Oracle exceptions when running the UTL_REF programs:

ORA-00942

Insufficient privileges. You must have the appropriate privileges on the underlying database table.

ORA-01031

Insufficient privileges. You attempted to update an object table on which you have only SELECT privileges. You must have the appropriate privileges on the underlying database table.

ORA-08177

Cannot serialize access for this transaction. You have tried to change data after the start of a serialized transaction.

ORA-00060

Deadlock detected while waiting for resource. Your session and another session are waiting for a resource locked by the other. You will need to wait or ROLLBACK.

ORA-01403

No data found. The REF is NULL or otherwise not associated with an object in the database.

9.2.5.1.1 Restrictions

Note the following restrictions on calling DELETE_OBJECT:

• The program does not assert a purity level with the RESTRICT_REFERENCES pragma.

• You cannot call this program from within an SQL statement, either directly or indirectly.

9.2.5.2.1 Restrictions

Note the following restrictions on calling LOCK_OBJECT:

• The program does not assert a purity level with the RESTRICT_REFERENCES pragma.

• You cannot call this program from within an SQL statement, either directly or indirectly.

9.2.5.3.1 Restrictions

Note the following restrictions on calling SELECT_OBJECT:

• The program does not assert a purity level with the RESTRICT_REFERENCES pragma.

• You cannot call this program from within an SQL statement, either directly or indirectly.

9.2.5.3.2 Example

In the following procedure, I use the SELECT_OBJECT built-in to retrieve the object based on the passed-in reference:

CREATE OR REPLACE PROCEDURE show_emp (emp_in IN REF employee_t) IS    emp_obj employee_t BEGIN    UTL_REF.SELECT_OBJECT (emp_in, emp_obj);    DBMS_OUTPUT.PUT_LINE (emp_obj.name); 

END;.

9.2.5.4.1 Restrictions

Note the following restrictions on calling UPDATE_OBJECT:

• The program does not assert a purity level with the RESTRICT_REFERENCES pragma.

• You cannot call this program from within an SQL statement, either directly or indirectly.