 | Chapter 9 Datatype Packages |  |
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.
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
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"."
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.
This section provides an overview of the types of data manipulation you might perform on raw data.
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.
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.
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:
DayInQuestion0000 0111 1111 1000 0000 0000 0000 0000Bits 20-27 set AND Mask for the 26th0000 0010 0000 0000 0000 0000 0000 0000----------------------------------------------------------------------------- Result0000 0010 0000 0000 0000 0000 0000 0000True
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.
DayInQuestion0000 0111 1111 1000 0000 0000 0000 0000Bits 20-27 set AND Mask0000 0000 0001 1111 1110 0000 0000 0000Bits 14-21 set ----------------------------------------------------------------------------- Result0000 0000 0001 1000 0000 0000 0000 0000True
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.
This section describes the programs available through the UTL_RAW package.
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 |
This program asserts the following purity level with the RESTRICT_REFERENCES pragma:
PRAGMA RESTRICT_REFERENCES(BIT_AND, WNDS, RNDS, WNPS, RNPS);
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
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.
This program asserts the following purity level with the RESTRICT_REFERENCES pragma:
PRAGMA RESTRICT_REFERENCES(BIT_COMPLEMENT, WNDS, RNDS, WNPS, RNPS);
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
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 |
This program asserts the following purity level with the RESTRICT_REFERENCES pragma:
PRAGMA RESTRICT_REFERENCES(BIT_OR, WNDS, RNDS, WNPS, RNPS);
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.
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 |
This program asserts the following purity level with the RESTRICT_REFERENCES pragma:
PRAGMA RESTRICT_REFERENCES(BIT_XOR, WNDS, RNDS, WNPS, RNPS);
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
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.
This program asserts the following purity level with the RESTRICT_REFERENCES pragma:
PRAGMA RESTRICT_REFERENCES(CAST_TO_RAW, WNDS, RNDS, WNPS, RNPS);
For an example of CAST_TO_RAW, see "Section 9.2.3.15, "The UTL_RAW.TRANSLATE function"" later in this chapter.
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.
This program asserts the following purity level with the RESTRICT_REFERENCES pragma:
PRAGMA RESTRICT_REFERENCES(CAST_TO_VARCHAR2, WNDS, RNDS, WNPS, RNPS);
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'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 |
This program asserts the following purity level with the RESTRICT_REFERENCES pragma:
PRAGMA RESTRICT_REFERENCES(COMPARE, WNDS, RNDS, WNPS, RNPS);
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
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 |
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.
This program asserts the following purity level with the RESTRICT_REFERENCES pragma:
PRAGMA RESTRICT_REFERENCES(CONCAT, WNDS, RNDS, WNPS, RNPS);
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 |
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.
This program asserts the following purity level with the RESTRICT_REFERENCES pragma:
PRAGMA RESTRICT_REFERENCES(CONVERT, WNDS, RNDS, WNPS, RNPS);
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) |
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.
This program asserts the following purity level with the RESTRICT_REFERENCES pragma:
PRAGMA RESTRICT_REFERENCES(COPIES, WNDS, RNDS, WNPS, RNPS);
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
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.
This program asserts the following purity level with the RESTRICT_REFERENCES pragma:
PRAGMA RESTRICT_REFERENCES(LENGTH, WNDS, RNDS, WNPS, RNPS);
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
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 |
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.
This program asserts the following purity level with the RESTRICT_REFERENCES pragma:
PRAGMA RESTRICT_REFERENCES(OVERLAY, WNDS, RNDS, WNPS, RNPS);
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
The REVERSE function reverses the input raw string and returns this reversed string.
FUNCTION UTL_RAW.REVERSE (r IN RAW) RETURN RAW;
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.
This program asserts the following purity level with the RESTRICT_REFERENCES pragma:
PRAGMA RESTRICT_REFERENCES(REVERSE, WNDS, RNDS, WNPS, RNPS);
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
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 |
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.
This program asserts the following purity level with the RESTRICT_REFERENCES pragma:
PRAGMA RESTRICT_REFERENCES(SUBSTR, WNDS, RNDS, WNPS, RNPS);
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
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.
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.
This program asserts the following purity level with the RESTRICT_REFERENCES pragma:
PRAGMA RESTRICT_REFERENCES(TRANSLATE, WNDS, RNDS, WNPS, RNPS);
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 tESTThe 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 |
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.
This program asserts the following purity level with the RESTRICT_REFERENCES pragma:
PRAGMA RESTRICT_REFERENCES(TRANSLITERATE, WNDS, RNDS, WNPS, RNPS);
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;SQL> exec DBMS_OUTPUT.PUT_LINE (make_lower('This.is-A tEst'));
this_is_a_testThe 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. |
This program asserts the following purity level with the RESTRICT_REFERENCES pragma:
PRAGMA RESTRICT_REFERENCES(XRANGE, WNDS, RNDS, WNPS, RNPS);
For an example of XRANGE, see the example for TRANSLATE or TRANSLITERATE.
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.
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.
Table 9.5 lists the programs defined for the UTL_REF packages.
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.
UTL_REF does not declare any exceptions. However, you may encounter any of the following Oracle exceptions when running the UTL_REF programs:
Insufficient privileges. You must have the appropriate privileges on the underlying database table.
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.
Cannot serialize access for this transaction. You have tried to change data after the start of a serialized transaction.
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.
No data found. The REF is NULL or otherwise not associated with an object in the database.
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.
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.
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.
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.
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.
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.
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.
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;.
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.
Note the following restrictions on calling UPDATE_OBJECT:
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.
|  | |
| 9.1 DBMS_ROWID: Working with the ROWID Pseudo-Column (Oracle8 only) |  | 10. Miscellaneous Packages |
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.