[Chapter 9] 9.2 UTL_RAW: Manipulating Raw Data

9.2 UTL_RAW: Manipulating Raw Data

The UTL_RAW package contains a set of programs that allow you to manipulate raw data. This package was originally written as a component of the Oracle Server's advanced replication option, and it supported procedural replication of data across different NLS (National Language Support) language databases. By converting data to RAW, the remote procedure calls would not perform NLS conversion, thus preserving the nature of some special data. The functions included in this package actually go beyond this original functionality and provide a toolkit for the manipulation of raw data that is not otherwise available in the Oracle Server product. These functions perform a number of special operations: conversion and coercion, slicing and dicing of raw data, and bit-fiddling, all described in the next section.

Other than replication support, there are a number of advantages Oracle can offer in storing raw data in the database, such as tighter integration with the rest of the application, transaction-level consistency, concurrency, and recoverability. One of the difficulties in the use of raw data in an Oracle database has been in the poor support for manipulation of this data. The UTL_RAW package provides this support.

9.2.1 Getting Started with UTL_RAW

The UTL_RAW package is created when the Oracle database is installed. The utlraw.sql script (found in the built-in packages source code directory, as described in Chapter 1 ) contains the source code for this package's specification and body. This script is called by catrep.sql , which is run when the advanced replication option of the Oracle database is installed. If this package is not already installed, check to see if these files are in your admin subdirectory. If so, you can connect as SYS and install this package by running the two scripts in the following order:

SQL> @utlraw.sql
SQL> @prvtrawb.plb

9.2.1.1 UTL_RAW programs

Table 9.4 lists the programs provided by the UTL_RAW package. For a discussion of some of the concepts underlying the operations performed by these programs, see the next section, " Section 9.2.2, "Raw Data Manipulation Concepts" ."

Table 9.4: UTL_RAW Programs
Name	Description	Use In SQL
BIT_AND	Performs bitwise logical AND of the values in raw r1 with raw r2 and returns the ANDed result raw.	Yes
BIT_COMPLEMENT	Performs bitwise logical "complement" of the values in raw r and returns the "complemented" result raw.	Yes
BIT_OR	Performs bitwise logical OR of the values in raw r1 with raw r2 and returns the ORed result raw.	Yes
BIT_XOR	Performs bitwise logical "exclusive or" (XOR) of the values in raw r1 with raw r2 and returns the XORed result raw.	Yes
CAST_TO_RAW	Converts a VARCHAR2 string represented using N data bytes into a raw with N data bytes.	Yes
CAST_TO_VARCHAR2	Converts a raw represented using N data bytes into a VARCHAR2 string with N data bytes.	Yes
COMPARE	Compares raw r1 against raw r2. Returns 0 if r1 and r2 are identical; otherwise, returns the position of the first byte from r1 that does not match r2.	Yes
CONCAT	Concatenates a set of up to 12 raws into a single raw.	Yes
CONVERT	Converts a raw from one character set to another character set.	Yes
COPIES	Returns N copies of the original raw concatenated together.	Yes
LENGTH	Returns the length in bytes of a raw.	Yes
OVERLAY	Overlays the specified portion of a raw with a different raw value.	Yes
REVERSE	Reverses the byte sequence in the raw from end to end.	Yes
SUBSTR	Returns the specified sub-portion of a raw.	Yes
TRANSLATE	Translates original bytes in the raw with the specified replacement set.	Yes
TRANSLITERATE	Translates original bytes in the raw with the specified replacement set following rules, which result in the transliterated raw always being the same length as the original raw.	Yes
XRANGE	Returns a raw containing all valid 1-byte encodings in succession beginning with the value start_byte and ending with the value end_byte.	Yes

UTL_RAW does not declare any exceptions or nonprogram elements.

9.2.2 Raw Data Manipulation Concepts

This section provides an overview of the types of data manipulation you might perform on raw data.

9.2.2.1 Conversion and coercion

Conversion refers to functions that convert raw byte strings to other values. Coercion is a specialized conversion that changes the datatype but not the data itself. UTL_RAW has functions that convert from one NLS language set to another, from one set of raw byte strings to another, and from raw datatypes to VARCHAR2 datatypes (as well as from VARCHAR2 to raw). The coercion operations supported by Oracle involving raw datatypes via the standard SQL functions are raw-to-hex and hex-to-raw; via UTL_RAW functions, they are raw-to-VARCHAR2 and VARCHAR2-to-raw. Notably unsupported are raw-to/from-numeric datatypes and raw-to/from-date datatypes.

9.2.2.2 Slicing and dicing

Slicing and dicing refers to functions that divide and combine raw byte strings in various ways. These functions include COMPARE, CONCATENATE, COPY, LENGTH, OVERLAY, REVERSE, and SUBSTRING.

9.2.2.3 Bit-fiddling

Bit-fiddling refers to the manipulation of individual bits. Because bits are the smallest possible unit of storage, bit-fiddling provides a highly efficient storage mechanism. Bitmap indexes take advantage of this and offer substantial disk savings over traditional Btree indexes. The Oracle kernel supports the bitwise AND function natively via the undocumented function BITAND(x,x),[ 1 ] but the other bitwise operations needed to support bitmasks are supported only via the UTL_RAW package.

[1] See the definitions of some V$ tables, such as V$session_wait, in the V$fixed_view_definition view.

Bitmasks are commonly used to combine a number of flags or semaphores into a single object as follows:

To see if a bit/flag/semaphore is set, use the bitwise AND function.
To turn a bit on or combine bitmasks, use the bitwise OR function.
To turn a bit off, use the bitwise OR and NOT functions together.
To toggle a bit, use the bitwise XOR function.

Other bitwise functions, such as shift left and shift right, are supported in C and other languages, but not in PL/SQL or UTL_RAW.

To better understand bitmasks and what these functions do, let's look at some examples of their use. A mask is a bit that represents some data; for example, each day of the month can be represented by one bit as follows.

The first of the month is the bit mask:

0000 0000 0000 0000 0000 0000 0000 0001 or hex 0000 0001

The second of the month is the bit mask:

0000 0000 0000 0000 0000 0000 0000 0010 or hex 0000 0002
....

The 26th of the month is the bit mask:

0000 0010 0000 0000 0000 0000 0000 0000 or hex 0200 0000

And so on. In a single 32-bit string (4 bytes), any combination of days of the month can be set. In a scheduling application, we may want to find out if the variable DayInQuestion has the bit set for the 26th. We can perform a bitwise AND on the variable and the mask like this:

DayInQuestion      
0000 0111 1111 1000 0000 0000 0000 0000
  Bits 20-27 set   
                                AND
Mask for the 26th  
0000 0010 0000 0000 0000 0000 0000 0000

-----------------------------------------------------------------------------
Result             
0000 0010 0000 0000 0000 0000 0000 0000
  True

Likewise, if the variable needs to be checked for any of the bits 14th through 21st, then the masks for the 14th through 21st can be combined (via bitwise OR) and compared to the variable.

DayInQuestion      
0000 0111 1111 1000 0000 0000 0000 0000  
 Bits 20-27 set
                                 AND
Mask               
0000 0000 0001 1111 1110 0000 0000 0000 
  Bits 14-21 set
-----------------------------------------------------------------------------
Result             
0000 0000 0001 1000 0000 0000 0000 0000 
  True

The UTL_RAW package can also be used separately from replication, and offers facilities for manipulating raw data types that are not found elsewhere in the Oracle Server product. Oracle has a robust set of functions available for the structured datatypes RAW, CHARACTER, NUMERIC, and DATE.

9.2.3 The UTL_RAW Interface

This section describes the programs available through the UTL_RAW package.

9.2.3.1 The UTL_RAW. BIT_AND function

The BIT_AND function performs a bitwise logical AND of two input raw strings. If input strings are different lengths, the return value is the same length as the longer input string. The return value is the bitwise AND of the two inputs up to the length of the shorter input string, with the remaining length filled from the unprocessed data in the longer input string. If either input string is NULL, the return value is NULL. Here's the specification for this function:

FUNCTION UTL_RAW.BIT_AND
   (r1 IN RAW 
   ,r2 IN RAW)
RETURN RAW;

Parameters are summarized in the following table.

Parameter	Description
r1	Raw string to AND with r2
r2	Raw string to AND with r1

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 The UTL_RAW.BIT_COMPLEMENT function

The BIT_COMPLEMENT function performs a logical NOT, or one's complement, of the raw input string r1. The complement of a raw string flips all 0 bits to 1 and all 1 bits to 0,

FUNCTION UTL_RAW.COMPLEMENT
   (r1 IN RAW)
RETURN RAW;

where r1 is the raw input string.

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 The UTL_RAW.BIT_OR function

The BIT_OR function performs a bitwise logical OR of the two input raw strings r1 and r2. If r1 and r2 are of different length, the return value is the same length as the longer input string. The return value is the bitwise OR of the two inputs up to the length of the shorter input string, with the remaining length filled from the unprocessed data in the longer input string. If either input string is NULL, the return value is NULL.

FUNCTION UTL_RAW.BIT_OR
   (r1 IN RAW
   ,r2 IN RAW)
RETURN RAW;

Parameters are summarized in the following table.

Parameter	Description
r1	Raw string to OR with r2
r2	Raw string to OR with r1

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 The UTL_RAW. BIT_XOR function

The BIT_XOR function performs a bitwise logical XOR of the two input raw strings r1 and r2. If r1 and r2 are of different lengths, the return value is the same length as the longer input string. The return value is the bitwise XOR of the two inputs, up to the length of the shorter input string with the remaining length filled from the unprocessed data in the longer input string. If either input string is NULL, the return value is NULL. Here's the specification:

FUNCTION UTL_RAW.BIT_XOR
   (r1 IN RAW 
   ,r2 IN RAW)
RETURN RAW;

Parameters are summarized in the following table.

Parameter	Description
r1	Raw string to XOR with r2
r2	Raw string to XOR with r1

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 The UTL_RAW.CAST_TO_RAW function

The CAST_TO_RAW function converts the VARCHAR2 input string into a raw datatype. The data is not altered; only the data type is changed. This is essentially a VARCHAR2_to_RAW function,

FUNCTION UTL_RAW.CAST_TO_RAW
   (c IN VARCHAR2)
RETURN RAW;

where c is the text string that should be converted to a raw datatype.

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 The UTL_RAW.CAST_TO_VARCHAR2 function

The CAST_TO_VARCHAR2 function converts the raw input string into a VARCHAR2 datatype. The data is not altered; only the data type is changed. The current NLS language is used. The specification is,

FUNCTION UTL_RAW.CAST_TO_VARCHAR2
   (r IN RAW)
RETURN VARCHAR2;

where r is the raw string that should be converted into a VARCHAR2.

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 The UTL_RAW. COMPARE function

The COMPARE function does a binary compare of the two raw input strings and returns the number of the first byte position where the two strings differ. If the two strings are identical, a zero is returned. If the two input strings are different lengths, then the pad character is repeatedly appended to the shorter string, extending it to the length of the longer string. The default pad character is 0x00 (binary zero).

FUNCTION UTL_RAW.COMPARE
   (r1 IN RAW 
   ,r2 IN RAW 
   ,pad IN RAW DEFAULT NULL)
RETURN NUMBER;

The parameters for this program are summarized in this table.

Parameter	Description
r1	The first input string to compare
r2	The second input string to compare
pad	The single character used to right pad the shorter of two unequal length strings

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 The UTL_RAW.CONCAT function

The CONCAT function is used to concatenate a set of 12 raw strings into a single raw string. The size of the concatenated result must not exceed 32K or the procedure will raise the ORA-6502 exception.

FUNCTION UTL_RAW.CONCAT
   (r1 IN RAW DEFAULT NULL
   ,r2 IN RAW DEFAULT NULL
   ,r3 IN RAW DEFAULT NULL
   ,r4 IN RAW DEFAULT NULL
   ,r5 IN RAW DEFAULT NULL
   ,r6 IN RAW DEFAULT NULL
   ,r7 IN RAW DEFAULT NULL
   ,r8 IN RAW DEFAULT NULL
   ,r9 IN RAW DEFAULT NULL
   ,r10 IN RAW DEFAULT NULL
   ,r11 IN RAW DEFAULT NULL
   ,r12 IN RAW DEFAULT NULL)
RETURN RAW;

The parameters for this program are summarized in this table.

Parameter	Description
r1	First piece of raw data to be concatenated
r2	Second piece of raw data to be concatenated
r3	Third piece of raw data to be concatenated
r4	Fourth piece of raw data to be concatenated
r5	Fifth piece of raw data to be concatenated
r6	Sixth piece of raw data to be concatenated
r7	Seventh piece of raw data to be concatenated
r8	Eighth piece of raw data to be concatenated
r9	Ninth piece of raw data to be concatenated
r10	Tenth piece of raw data to be concatenated
r11	Eleventh piece of raw data to be concatenated
r12	Twelfth piece of raw data to be concatenated

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 The UTL_RAW. CONVERT function

The CONVERT function converts the input raw string r from one installed NLS character set to another installed NLS character set. Here's the specification:

FUNCTION UTL_RAW.CONVERT
   (r IN RAW 
   ,to_charset IN VARCHAR2 
   ,from_charset IN VARCHAR2)
RETURN RAW;

Parameters are summarized in the following table.

Parameter	Description
r	The raw string to be converted
to_charset	The name of the output NLS character set
from_charset	The name of the input NLS character set

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 The UTL_RAW. COPIES function

The COPIES function concatenates the input raw string r, n number of times. Here's the specification:

FUNCTION UTL_RAW.COPIES
   (r IN RAW 
   ,n IN NUMBER)
RETURN RAW;

The parameters for this program are summarized in this table.

Parameter	Description
r	The input raw string that is to be copied
n	The number of copies of the input string to make (must be positive)

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 The UTL_RAW.L ENGTH function

The LENGTH function returns the number of bytes in the raw input string given by the r parameter,

FUNCTION UTL_RAW.LENGTH
   (r IN RAW)
RETURN NUMBER;

where r is the raw input string.

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 The UTL_RAW. OVERLAY function

The OVERLAY function overwrites the specified section of the target raw string with the string specified in the overlay_str parameter and returns the overwritten raw string. The overwriting starts pos bytes into the target string and continues for len bytes, right-padding the target with the pad parameter as needed to extend the target, if necessary. The len parameter must be greater than 0 and pos must be greater than 1. If pos is greater than the length of the target string, then the target is right-padded with pad before the overlaying begins. Here's the specification:

FUNCTION UTL_RAW.OVERLAY
   (overlay_str IN RAW
   ,target IN RAW
   ,pos IN BINARY_INTEGER DEFAULT 1
   ,len IN BINARY_INTEGER DEFAULT NULL
   pad IN RAW DEFAULT NULL)
RETURN RAW;

The parameters for this program are summarized in the following table.

Parameter	Description
overlay_str	The raw string used to overwrite to target
target	The raw string that is to be overlaid/overwritten
pos	The byte position in the target to begin overlaying; the default is 1
len	The number of bytes to overwrite; the default is the length of overlay_str
pad	The pad character to fill in extra space if needed; the default is 0x00

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 The UTL_RAW.REVERSE function

The REVERSE function reverses the input raw string and returns this reversed string.

FUNCTION UTL_RAW.REVERSE
   (r IN RAW)
RETURN RAW;

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 The UTL_RAW. SUBSTR function

The SUBSTR function returns a substring of the input raw string r beginning at pos and extending for len bytes. If pos is positive, the substring extends len bytes from the left; if pos is negative, the substring extends len bytes from the right (the end backwards). The value of pos cannot be 0. The default for len is to the end of the string r. If r is NULL, then NULL is returned. Here's the specification:

FUNCTION UTL_RAW.SUBSTR
    (r IN RAW
    ,pos IN BINARY_INTEGER
    ,len IN BINARY_INTEGER DEFAULT NULL)
RETURN RAW;

Parameters are summarized in the following table.

Parameter	Description
r	The input raw string, from which the substring is extracted
pos	The starting position for the substring extraction
len	The length of the substring to extract; the default is to the end of the input string r

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 The UTL_RAW. TRANSLATE function

The TRANSLATE function translates bytes in the input raw sting r, substituting bytes found in from_set with positionally corresponding bytes in to_set. The translated string is returned. Bytes in r that do not appear in from_set are not modified. If from_set is longer than to_set, then the unmatched bytes in from_set are removed from the return string. Here's the specification:

FUNCTION UTL_RAW.TRANSLATE
   (r IN RAW 
   ,from_set IN RAW
   ,to_set IN RAW)
RETURN RAW;

Parameters are summarized in the following table.

Parameter	Description
r	The input raw string to be translated
from_set	The list of bytes to translate
to_set	The list of bytes that from_set bytes are translated to

TRANSLATE is similar to TRANSLITERATE; however, with TRANSLATE, the return string can be shorter than the input string r. TRANSLITERATE return strings are always the same length as the input string r. Also, TRANSLATE requires values for from_set, and to_set while TRANSLITERATE has defaults for these inputs.

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 The UTL_RAW. TRANSLITERATE function

The TRANSLITERATE function translates bytes in the input raw sting r, substituting bytes found in from_set with positionally corresponding bytes in to_set. The translated string is returned. Bytes in r that do not appear in from_set are not modified. If from_set is longer than to_set, then the unmatched bytes in from_set are right-padded with the pad byte. The return string is always the same length as the input string r. The specification follows:

FUNCTION UTL_RAW.TRANSLITERATE
   (r IN RAW 
   ,to_set IN RAW DEFAULT NULL
   ,from_set IN RAW DEFAULT NULL
   ,pad IN RAW DEFAULT NULL)
RETURN RAW;

TRANSLITERATE is similar to TRANSLATE, but it differs in that the return string is always the same length as the input string (r). TRANSLITERATE is just like TRANSLATE if to_set and from_set are the same length. If from_set is longer than to_set, then to_set is right-padded with the pad byte. TRANSLITERATE allows NULL from_set, to_set, and pad parameters.

Parameters are summarized in the following table.

Parameter	Description
r	Input string to be translated
from_set	The list of bytes to be translated; the default is 0x00 through 0xFF
to_set	The list of bytes that from_set bytes are translated to; the default is NULL
pad	If from_set is shorter than to_set, then this pad byte is the translation character for any unmatched bytes in from_set; the default is 0x00

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 The UTL_RAW. XRANGE function

The XRANGE function returns a raw string containing all bytes in order beginning with the start_byte parameter and ending with end_byte. If start_byte is greater than end_byte, then the return string wraps from 0XFF to 0X00.

FUNCTION UTL_RAW.XRANGE
   (start_byte IN RAW DEFAULT 0x00
   ,end_byte IN RAW DEFAULT 0xFF)
RETURN RAW;

The parameters for this program are summarized in the following table.

Parameter	Description
start_byte	Start byte; the default is 0x00.
end_byte	End byte; the default is 0xFF.

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 UTL_REF: Referencing Objects (Oracle8.0.4)

The UTL_REF package provides a PL/SQL interface that allows you to select and modify objects (instances of an object type) in an object table without having to specify or know about the underlying database table. With UTL_REF, you only need a reference to the object in order to identify it in the database and perform the desired operations. With UTL_REF, you can do any of the following:

Select or retrieve an object from the database
Lock an object so that no other session can make changes to the object
Select and lock an object in a single operation (similar to SELECT FOR UPDATE)
Update the contents of an object
Delete an object

You will typically use UTL_REF programs when you have references to an object and one of the following is true:

You do not want to have to resort to an SQL statement to perform the needed action.
You do not even know the name of the table that contains the object, and therefore cannot rely on SQL to get your job done.

Before getting into the details, let's start with an initial example of how you might use the UTL_REF packages.

You will be able to use UTL_REF programs only to select or modify objects in an object table. An object table is a table in which each row of the table is an object. Here are the steps one might take to create an object table.

First, create an object type:

CREATE TYPE hazardous_site_t IS OBJECT (
   name VARCHAR2(100),
   location VARCHAR2(100),
   dixoin_level NUMBER,
   pcb_level NUMBER,
   METHOD FUNCTION cleanup_time RETURN NUMBER);

Now you can create a table of these objects:

CREATE TABLE hazardous_sites OF hazardous_site_t;

As you will see in the headers for the UTL_REF programs, Oracle has provided a special parameter-passing syntax called ANY. This syntax allows us to pass references and objects of any object type in and out of the programs. This behavior is not otherwise available in Oracle8 built-in packages or the code that you yourself can write using object types.

9.2.4.1 Getting Started with UTL_REF

The UTL_REF package is created when the Oracle8.0.4 (or later) database is installed. The utlref.sql script (found in the built-in packages source code directory, as described in Chapter 1 ) contains the source code for this package's specification. The script is called by catproc.sql , which is normally run immediately after the database is created. The script creates the public synonym UTL_REF for the package and grants EXECUTE privilege on the package to public. All Oracle users can reference and make use of the package.

Every program in this package runs as "owner." This means that programs in the UTL_REF package operate within the privileges of the session running those programs. You will be able to select and modify only objects to which your session has been granted the necessary privileges.

9.2.4.1.1 UTL_REF programs

Table 9.5 lists the programs defined for the UTL_REF packages.

Table 9.5: UTL_REF Programs
Name	Description	Use in SQL
DELETE_OBJECT	Deletes an object from the underlying object table	No
LOCK_OBJECT	Locks an object so that another session cannot change the object	No
SELECT_OBJECT	Selects an object based on its reference, returning that object as an OUT argument	No
UPDATE_OBJECT	Updates the object specified by the reference by replacing it with the object you pass to the program	No

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 UTL_REF Interface

This section describes the programs available through the UTL_REF package. A single, extended example at the end of the chapter shows how you might be able to take advantage of the UTL_REF programs in your own applications.

9.2.5.1 The UTL_REF. DELETE_OBJECT procedure

Use the DELETE_OBJECT procedure to delete an object (actually, the row containing that object) specified by the given reference. The header is,

PROCEDURE UTL_REF.DELETE_(reference IN REF ANY);

where reference identifies the object.

This program effectively substitutes for the following kind of SQL statement:

DELETE FROM the_underlying_object_table t
 WHERE REF (t) = reference;

In contrast to this SQL statement, with DELETE_OBJECT you will not need to specify the name of the underlying database object table to retrieve the object.

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 The UTL_REF. LOCK_OBJECT procedure

Use the LOCK_OBJECT procedure to lock or lock and retrieve an object for a given reference. The header is overloaded as follows:

PROCEDURE UTL_REF.LOCK_OBJECT (reference IN REF ANY);

PROCEDURE UTL_REF.LOCK_OBJECT 
   (reference IN REF ANY
   ,object IN OUT ANY);

Parameters are summarized in the following table.

Parameter	Description
reference	The reference to the object
object	The value of the object selected from the database (if supplied)

If you call LOCK_OBJECT and do not provide a second argument, then the object will be locked, but that object will not be returned to the calling program.

This program effectively substitutes for the following type of SQL statement:

SELECT VALUE (t)
  INTO object
  FROM the_underlying_object_table t
 WHERE REF (t) = reference
   FOR UPDATE;

In contrast to this SQL statement, with LOCK_OBJECT you will not need to specify the name of the underlying database object table to retrieve the object.

NOTE: It is not necessary to lock an object before you update or delete it. By requesting a lock, however, you ensure that another session cannot even attempt to make changes to that same object until you commit or roll back.

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 The UTL_REF. SELECT_OBJECT procedure

Use the SELECT_OBJECT procedure to retrieve an object for a given reference. The header follows:

PROCEDURE UTL_REF.SELECT_OBJECT 
   (reference IN REF ANY
   ,object IN OUT ANY);

Parameters are summarized in the following table.

Parameter	Description
reference	The reference to the object
object	The value of the object selected from the database

This program effectively substitutes for the following type of SQL statement:

SELECT VALUE (t)
  INTO object
  FROM the_underlying_object_table t
 WHERE REF (t) = reference;

In contrast to this SQL statement, with SELECT_OBJECT you will not need to specify the name of the underlying database object table to retrieve the object.

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 The UTL_REF. UPDATE_OBJECT procedure

Use the UPDATE_OBJECT procedure to replace an object in the database specified by a given reference with your "replacement" object. Here's the header:

PROCEDURE UTL_REF.UPDATE_OBJECT 
   (reference IN REF ANY
   ,object IN ANY);

Parameters are summarized in the following table.

Parameter	Description
reference	The reference to the object
object	The object that is to be placed in the row of the object table specified by the reference

This program effectively substitutes for the following type of SQL statement:

UPDATE the_underlying_object_table t 
   SET VALUE (t) = object
 WHERE REF (t) = reference;

In contrast to this SQL statement, with UPDATE_OBJECT you will not need to specify the name of the underlying database object table to retrieve the object.

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.

9.2.6 UTL_REF Example

Let's start with an object type that can hold various types of documents

CREATE OR REPLACE TYPE Document_t AS OBJECT (
   doc_id NUMBER,
   author VARCHAR2(65),
   created DATE,
   revised DATE,
   body BLOB,
   MEMBER PROCEDURE update_revised
);
/

To keep this example simple, we'll implement only a single object method:

CREATE OR REPLACE TYPE BODY Document_t
AS
   MEMBER PROCEDURE update_revised
   IS
   BEGIN
      revised := SYSDATE;
   END;
END;
/

Here's a table that will hold any kind of document:

CREATE TABLE documents OF Document_t;

We might have a requisition type that has a special type of document. Each requisition contains a REF to a particular document.

CREATE OR REPLACE TYPE Requisition_t AS OBJECT (
   doc_ref REF Document_t,
   needed DATE,
   approved DATE,
   MEMBER PROCEDURE update_revision_date,
   MEMBER FUNCTION has_valid_need_date RETURN BOOLEAN
);
/

In a moment, we're going to look at an example of UTL_REF that implements the type body of Requisition_t. But let's first look at life without UTL_REF. Not only do we have to write SQL, we also have to know the table name in each statement where we need access to a persistent object. In fact, the following methods are hard-coded to work with only one particular table implementation (not good):

CREATE OR REPLACE TYPE BODY Requisition_t
AS
   MEMBER FUNCTION has_valid_need_date RETURN BOOLEAN
   IS
      document Document_t;
      CURSOR doc_cur IS                /* Ugly! */
         SELECT VALUE(d) 
           FROM documents d
          WHERE REF(d) = SELF.doc_ref;
   BEGIN
      OPEN doc_cur;
      FETCH doc_cur INTO document;    /* Ditto */
      CLOSE doc_cur;
      IF document.created > SELF.approved
      THEN
         RETURN FALSE;
      ELSE
         RETURN TRUE;
      END IF;
   END;
   MEMBER PROCEDURE update_revision_date
   IS
   BEGIN
      UPDATE documents d            /* Even uglier */
         SET revised = SYSDATE
       WHERE REF(d) = SELF.doc_ref;
   END;
END;
/

Let's turn now to see what UTL_REF can do for us:

CREATE OR REPLACE TYPE BODY Requisition_t
AS
   MEMBER FUNCTION has_valid_need_date RETURN BOOLEAN
   IS
      document Document_t;
   BEGIN
      /* UTL_REF.SELECT_OBJECT allows us to retrieve the document object 
      || from persistent database storage into a local variable.  No muss, 
      || no fetch, no bother!  SELECT_OBJECT finds the table and object 
      || for us.
      */
      UTL_REF.SELECT_OBJECT (SELF.doc_ref, document);
      /* Now that we have retrieved the document object, we can
      || easily gain access to its attributes:
      */
      IF document.created > SELF.approved
      THEN
         RETURN FALSE;
      ELSE
         RETURN TRUE;
      END IF;
   END;
   MEMBER PROCEDURE update_revision_date
   IS
      document Document_t;
   BEGIN
      /* To update the revision date of the requisition object,
      || we'll simply "delegate" to the referenced document.
      || First we retrieve it...
      */
      UTL_REF.SELECT_OBJECT (SELF.doc_ref, document);
      /* ...then we can invoke a method on the newly retrieved
      || (but transient) object.  Notice that we do NOT update
      || the attribute directly, but rely instead on the public
      || method supplied for this purpose.
      */
      document.update_revised;
      /* ...and now we easily update the data in the underlying table
      || (whatever table it is...we don't know or care!)
      */
      UTL_REF.UPDATE_OBJECT(SELF.doc_ref, document);
   END;
END;
/

Since UTL_REF frees us from dependence on the specific underlying table, it allows us to achieve greater reuse, portability, modularity, and resilience to change.