C The UTL_PG and UTL_RAW Interface

C.1.1 BIT_AND

BIT_AND performs a bitwise logical AND operation of the values in r1 and r2 and returns the resulting value.

If r1 and r2 have different lengths, then the AND operation is terminated after the last byte of the shorter of the two RAW values. The unprocessed portion of the longer RAW value is appended to the partial result to produce the final result returned. The result length equals the longer of the two input RAW values.

Syntax

function BIT_AND (r1 IN RAW, r2 IN RAW) RETURN RAW;

Where Table C-1 describes the parameters in this function:

Defaults

None.

Return Value

A RAW value which is the bitwise logical AND of r1 and r2. Or a null value if either r1 or r2 is null.

Error and Warning Messages

If you receive an ORA-xxxx error or warning message, refer to the Oracle Database Error Messages guide for an explanation and information about how to handle it.

C.1.2 BIT_COMPLEMENT

BIT_COMPLEMENT performs a bitwise logical COMPLEMENT operation of the value r and returns the resulting RAW value. The length of the result equals the length of the input RAW value r.

Syntax

function BIT_COMPLEMENT (r IN RAW) RETURN RAW;

where:

"r" is the RAW value on which to perform the COMPLEMENT operation.

Defaults

None

Return Value

A RAW value which is the bitwise logical COMPLEMENT of r. Or a null value if the input value r is null.

Error and Warning Messages

If you receive an ORA-xxxx error or warning message, refer to the Oracle Database Error Messages guide for an explanation and information about how to handle it.

C.1.3 BIT_OR

BIT_OR performs a bitwise logical OR operation of the values in r1 and r2 and returns the resulting value.

If r1 and r2 have different lengths, then the OR operation is terminated after the last byte of the shorter of the two RAW values. The unprocessed portion of the longer RAW value is appended to the partial result to produce the final result returned. The resulting length equals the longer of the two input values, r1 and r2.

Syntax

function BIT_OR (r1 IN RAW, r2 IN RAW) RETURN RAW;

Where Table C-2 describes the parameters in the function:

Defaults

None

Return Value

A RAW value which is the bitwise logical OR of r1 and r2. Or a null value if both r1 and r2 is null.

Error and Warning Messages

If you receive an ORA-xxxx error or warning message, refer to the Oracle Database Error Messages guide for an explanation and information about how to handle it.

C.1.4 BIT_XOR

BIT_XOR performs a bitwise logical EXCLUSIVE OR operation of the values in r1 and r2 and returns the resulting value.

If r1 and r2 have different lengths, then the EXCLUSIVE OR operation is terminated after the last byte of the shorter of the two RAW values. The unprocessed portion of the longer RAW value is appended to the partial result to produce the final result returned. The result length equals the longer of the two input RAW values.

Syntax

function BIT_XOR (r1 IN RAW, r2 IN RAW) RETURN RAW

Where Table C-3 describes the parameters in this function:

Defaults

None

Return Value

A RAW value which is the bitwise logical XOR of r1 and r2. Or a null value if r1 and r2 have identical values.

Error and Warning Messages

If you receive an ORA-xxxx error or warning message, refer to the Oracle Database Error Messages guide for an explanation and information about how to handle it.

C.1.5 CAST_TO_RAW

CAST_TO_RAW converts a VARCHAR2 c into a RAW with the same number of bytes.

The input is treated as if it is composed of single 8-bit bytes, not characters. Multibyte character boundaries are ignored. The data is not modified in any way, it is just changed to a RAW datatype.

Syntax

function CAST_TO_RAW (c IN VARCHAR2) RETURN RAW;

where: "c" is a VARCHAR2 value to be changed to a RAW value.

Defaults

None

Return Value

A RAW value having the same data and byte length as the input VARCHAR2 value. Or a null value if c is null.

Error and Warning Messages

If you receive an ORA-xxxx error or warning message, refer to the Oracle Database Error Messages guide for an explanation and information about how to handle it.

C.1.6 CAST_TO_VARCHAR2

CAST_TO_VARCHAR2 converts a RAW r into a VARCHAR2 with the same number of data bytes.

The result is treated as if it is composed of single 8-bit bytes, not characters. Multibyte character boundaries are ignored. The data is not modified in any way, it is just changed to a VARCHAR2 datatype.

Syntax

function CAST_TO_VARCHAR2 (r IN RAW) RETURN VARCHAR2;

where:

"r" is a RAW value to be changed to a VARCHAR value.

Defaults

None

Return Value

A VARCHAR2 value having the same data as the RAW input value. Or a null value if r is null.

Error and Warning Messages

If you receive an ORA-xxxx error or warning message, refer to the Oracle Database Error Messages guide for an explanation and information about how to handle it.

C.1.7 COMPARE

COMPARE compares RAW r1 with RAW r2.

If they are identical, COMPARE returns zero. Otherwise, COMPARE returns the position of the first byte that does not match. If the input values are of different length, the shorter RAW value is padded on the right with the byte specified by pad.

Syntax

function COMPARE (r1 IN RAW, r2 IN RAW, 
 pad RAW DEFAULT NULL) RETURN NUMBER;

Where Table C-4 describes the parameters in this function:

Defaults

where "pad" is optional and defaults to x'00'.

Return Value

A value of 0 if the strings are null or identical. Or the position, numbered from 1, of the first mismatched byte.

Error and Warning Messages

If you receive an ORA-xxxx error or warning message, refer to the Oracle Database Error Messages guide for an explanation and information about how to handle it.

C.1.8 CONCAT

CONCAT concatenates a set of up to 12 RAW values (r1 - r12) into a single RAW and returns it.

Input RAW values are appended together in the resulting RAW, left to right, in the order they appear in the parameter list. Input values need not be contiguous. Null input RAW values are skipped and the concatenation continues with the next non-null input RAW value. If the sum of the lengths of the input RAWs exceeds the maximum allowable length for a RAW (32767), an error is returned.

Syntax

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

where:

"r1 - r12" are the RAW items to be concatenated.

Defaults

None

Return Value

A RAW value with the concatenated items.

Error and Warning Messages

If you receive an ORA-xxxx error or warning message, refer to the Oracle Database Error Messages guide for an explanation and information about how to handle it.

C.1.9 CONVERT

CONVERT converts RAW r from character set from_charset to character set to_charset and returns the resulting RAW value.

Both from_charset and to_charset must specify supported character sets defined to the Oracle server.

Syntax

function CONVERT (r IN RAW, 
 to_charset IN VARCHAR2,
 from_charset IN VARCHAR2) RETURN RAW;

Where Table C-5 describes the parameters in this function:

Defaults

None

Return Value

A RAW string of bytes converted according to the specified character set.

Error and Warning Messages

If you receive an ORA-xxxx error or warning message, refer to the Oracle Database Error Messages guide for an explanation and information about how to handle it.

C.1.10 COPIES

COPIES returns n copies of RAW r concatenated together.

Syntax

function COPIES (r IN RAW,
 n IN NUMBER) RETURN RAW;

Where Table C-6 describes the parameters in this function:

Defaults

None

Return Value

The RAW value copied n times.

Error and Warning Messages

If you receive an ORA-xxxx error or warning message, refer to the Oracle Database Error Messages guide for an explanation and information about how to handle it.

C.1.11 LENGTH

LENGTH returns the length in bytes of RAW r.

Syntax

function LENGTH (r IN RAW) RETURN NUMBER;

where:

"r" is the byte stream to be measured.

Defaults

None

Return Value

The current length of the RAW input value.

Error and Warning Messages

If you receive an ORA-xxxx error or warning message, refer to the Oracle Database Error Messages guide for an explanation and information about how to handle it.

C.1.12 OVERLAY

OVERLAY replaces the specified portion of RAW target with RAW overlay, beginning at byte position pos of target, and proceeding for len bytes.

If overlay has fewer than len bytes, then overlay is padded to len bytes using the byte specified by pad. If overlay has more than len bytes, then the extra bytes in overlay are ignored. If len bytes beginning at position pos of target exceeds the length of target, target is extended to contain the entire length of overlay. If pos exceeds the length of target, target is padded with pad bytes to position pos and then target is further extended with overlay bytes.

Syntax

function OVERLAY (overlay IN RAW,
 target IN RAW, 
 pos IN BINARY_INTEGER DEFAULT 1,
 len IN BINARY_INTEGER DEFAULT NULL,
 pad IN RAW DEFAULT NULL) RETURN RAW;

Where Table C-7 describes the parameters in this function:

Defaults

Table C-8 describes the OVERLAY function defaults:

Return Value

The target byte-string overlayed as specified.

Error and Warning Messages

If you receive an ORA-xxxx error or warning message, refer to the Oracle Database Error Messages guide for an explanation and information about how to handle it.

C.1.13 REVERSE

Reverse the byte sequence in RAW r from end-to-end. For example, x'0102F3' would be reversed into x'F30201' and 'xyz' would be reversed to 'zyx'. The result length is the same as the input RAW length.

Syntax

function REVERSE (r IN RAW) RETURN RAW;

where "r" is the RAW value to reverse.

Defaults

None

Return Value

A RAW value containing the reverse of the input RAW value.

Error and Warning Messages

If you receive an ORA-xxxx error or warning message, refer to the Oracle Database Error Messages guide for an explanation and information about how to handle it.

C.1.14 SUBSTR

SUBSTR extracts a portion of RAW r, starting at byte position pos and including len bytes.

If pos is positive, SUBSTR counts from the beginning of r to find the first byte. If pos is negative, SUBSTR counts backward from the end of r. If len is not specified, SUBSTR returns all bytes to the end of r.

Syntax

function SUBSTR (r IN RAW,
                     pos IN BINARY_INTEGER,
                     len BINARY_INTEGER DEFAULT NULL) RETURN RAW;

Where Table C-9 describes the parameters in this function:

Defaults

Defaults to the length of position pos to the end of r.

Return Value

The portion of r beginning at pos for len bytes. Or a null value if r is null.

Error and Warning Messages

If you receive an ORA-xxxx error or warning message, refer to the Oracle Database Error Messages guide for an explanation and information about how to handle it.

C.1.15 TRANSLATE

TRANSLATE returns the RAW r after changing the bytes in from_set according to bytes in to_set.

Successive bytes in r are looked up in from_set and if found, the byte at the same offset in to_set is copied to the result or omitted from the result if the offset exceeds the length of to_set. Bytes that appear in r but not in from_set are copied to the result. Only the first (leftmost) occurrence of a byte in from_set is used and subsequent duplicate occurrences are ignored.

If from_set contains more bytes than to_set, the extra bytes at the end of from_set have no corresponding bytes in to_set. Any bytes in r matching such uncorresponded from_set bytes are omitted from the resulting RAW value.

TRANSLATE differs from TRANSLITERATE in the following ways:

• translation RAWs have no defaults

• r bytes undefined in the to_set translation RAW are omitted

• resulting RAW value can be shorter than the input RAW value

Syntax

function TRANSLATE (r IN RAW,
 from_set IN RAW,
 to_set IN RAW) RETURN RAW;

Table C-10 describes the parameters in this function:

Defaults

None

Return Value

A RAW translated byte-string.

Error and Warning Messages

If you receive an ORA-xxxx error or warning message, refer to the Oracle Database Error Messages guide for an explanation and information about how to handle it.

C.1.16 TRANSLITERATE

TRANSLITERATE returns the RAW r after replacing all occurrences of any bytes in from_set with the corresponding bytes in to_set.

Successive bytes in r are looked up in from_set and, if not found, are copied unaltered to the resulting RAW value. If found, they are replaced in the resulting RAW value by either the byte at the same offset in to_set, or the pad byte when the offset exceeds the to_set length. Bytes found in r but not found in from_set are copied to the result. Only the first (leftmost) occurrence of a byte infrom_set is used; subsequent duplicate occurrences are ignored. The result of TRANSLITERATE is always the same length as RAW r.

If to_set is shorter than from_set, then the pad byte is placed in the resulting RAW value when a selected from_set byte has no corresponding byte in to_set.

TRANSLITERATE differs from TRANSLATE in the following ways:

• r bytes undefined in to_set are padded

• the resulting RAW value is always the same length as the input RAW value

Syntax

function TRANSLITERATE (r IN RAW,
 to_set IN RAW DEFAULT NULL,
 from_set IN RAW DEFAULT NULL,
 pad IN RAW DEFAULT NULL) RETURN RAW;

Where Table C-11 describes the parameters in this function:

Defaults

Table C-12 describes the TRANSLITERATE function defaults:

Return Value

A RAW transliterated byte-string.

Error and Warning Messages

If you receive an ORA-xxxx error or warning message, refer to the Oracle Database Error Messages guide for an explanation and information about how to handle it.

C.1.17 XRANGE

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.

If start_byte is greater than end_byte, the succession of result bytes begin with start_byte, wrap from x'ff' to x'00', and end at end_byte.

If specified, start_byte and end_byte must be single-byte RAW values.

Syntax

function XRANGE (start_byte IN RAW DEFAULT NULL,
 end_byte IN RAW DEFAULT NULL) RETURN RAW;

Where Table C-13 describes the parameters in this function:

Defaults

Where Table C-14 describes the XRANGE function defaults:

Return Value

A RAW value containing a succession of 1-byte hexadecimal encodings.

Error and Warning Messages

If you receive an ORA-xxxx error or warning message, refer to the Oracle Database Error Messages guide for an explanation and information about how to handle it.

C.2.1 Common Parameters

The following UTL_PG functions share several similar parameters among themselves:

• RAW_TO_NUMBER

• MAKE_NUMBER_TO_RAW_FORMAT

• MAKE_RAW_TO_NUMBER_FORMAT

• NUMBER_TO_RAW

These similar parameters are described in detail in Table C-15 and then referenced only by name in subsequent tables listing the parameters for each UTL_PG function in this Appendix.

C.2.2 RAW_TO_NUMBER

RAW_TO_NUMBER converts a RAW byte-string r from the remote host internal format specified by mask, maskopts, envrnmnt, compname, compopts, and nlslang into an Oracle number.

Warnings are issued, if enabled, when the conversion specified conflicts with the conversion implied by the data or when conflicting format specifications are supplied.

For detailed information about the mask, maskopts, envrnmnt, compname, and compopts arguments, refer to "NUMBER_TO_RAW and RAW_TO_NUMBER Argument Values".

Syntax

function RAW_TO_NUMBER (r IN RAW,
 mask IN VARCHAR2,
 maskopts IN VARCHAR2,  
 envrnmnt IN VARCHAR2,
 compname IN VARCHAR2, 
 compopts IN VARCHAR2,
 nlslang IN VARCHAR2,
 wind IN BOOLEAN,
 wmsgbsiz IN BINARY_INTEGER,
 wmsgblk OUT RAW) RETURN NUMBER;

Where Table C-17 describes the parameters in this function:

Defaults and Optional Parameters

Table C-18 describes the default and optional parameters of the RAW_TO_NUMBER function:

Return Value

An Oracle number corresponding in value to r.

Error and Warning Messages

If you receive an ORA-xxxx error or warning message, refer to the Oracle Database Error Messages guide for an explanation and information about how to handle it.

C.2.3 NUMBER_TO_RAW

NUMBER_TO_RAW converts an Oracle number n of declared precision and scale into a RAW byte-string in the remote host internal format specified by mask, maskopts, envrnmnt, compname, compopts, and nlslang.

Warnings are issued, if enabled, when the conversion specified conflicts with the conversion implied by the data or when conflicting format specifications are supplied.

For detailed information about the mask, maskopts, envrnmnt, compname, and compopts arguments, refer to"NUMBER_TO_RAW and RAW_TO_NUMBER Argument Values".

Syntax

function NUMBER_TO_RAW (n IN NUMBER,
 mask IN VARCHAR2,
 maskopts IN VARCHAR2, 
 envrnmnt IN VARCHAR2,
 compname IN VARCHAR2, 
 compopts IN VARCHAR2,
 nlslang IN VARCHAR2,
 wind IN BOOLEAN,
 wmsgbsiz IN BINARY_INTEGER,
 wmsgblk OUT RAW) RETURN RAW;

Where Table C-19 describes the parameters in this function:

Defaults and Optional Parameters

Table C-20 describes the defaults and optional parameters for the NUMBER_TO_RAW function:

Return Value

A RAW value corresponding in value to n.

Error and Warning Messages

If you receive an ORA-xxxx error or warning message, refer to the Oracle Database Error Messages guide for an explanation and information about how to handle it.

C.2.4 MAKE_RAW_TO_NUMBER_FORMAT

MAKE_RAW_TO_NUMBER_FORMAT makes a RAW_TO_NUMBER format conversion specification used to convert a RAW byte-string from the remote host internal format specified by mask, maskopts, envrnmnt, compname, compopts, and nlslang into an Oracle number of comparable precision and scale.

Warnings are issued, if enabled, when the conversion specified conflicts with the conversion implied by the data or when conflicting format specifications are supplied.

This function returns a RAW value containing the conversion format which can be passed to UTL_PG.RAW_TO_NUMBER_FORMAT.

For detailed information about the mask, maskopts, envrnmnt, compname, and compopts arguments, refer to "NUMBER_TO_RAW and RAW_TO_NUMBER Argument Values".

Syntax

function MAKE_RAW_TO_NUMBER_FORMAT (mask IN VARCHAR2,
 maskopts IN VARCHAR2, 
 envrnmnt IN VARCHAR2,
 compname IN VARCHAR2, 
 compopts IN VARCHAR2,
 nlslang IN VARCHAR2,
 wind IN BOOLEAN,
 wmsgbsiz IN BINARY_INTEGER,
 wmsgblk OUT RAW) RETURN RAW;

Where Table C-21 describes the parameters in this function:

Defaults and Optional Parameters

Table C-22 describes the defaults and optional parameters of the MAKE_RAW_TO_NUMBER_FORMAT function:

Return Value

A RAW(2048) format conversion specification for RAW_TO_NUMBER.

Error and Warning Messages

If you receive an ORA-xxxx error or warning message, refer to the Oracle Database Error Messages guide for an explanation and information about how to handle it.

C.2.5 MAKE_NUMBER_TO_RAW_FORMAT

MAKE_NUMBER_TO_RAW_FORMAT makes a NUMBER_TO_RAW format conversion specification used to convert an Oracle number of declared precision and scale to a RAW byte-string in the remote host internal format specified by mask, maskopts, envrnmnt, compname, compopts, and nlslang.

Warnings are issued, if enabled, when the conversion specified conflicts with the conversion implied by the data or when conflicting format specifications are supplied.

This function returns a RAW value containing the conversion format which can be passed to UTL_PG.NUMBER_TO_RAW_FORMAT. The implementation length of the result format RAW is 2048 bytes.

For detailed information about the mask, maskopts, envrnmnt, compname, and compopts arguments, refer to "NUMBER_TO_RAW and RAW_TO_NUMBER Argument Values".

Syntax

function MAKE_NUMBER_TO_RAW_FORMAT (mask IN VARCHAR2, 
 maskopts IN VARCHAR2,
 envrnmnt IN VARCHAR2,
 compname IN VARCHAR2, 
 compopts IN VARCHAR2,
 nlslang IN VARCHAR2,
 wind IN BOOLEAN,
 wmsgbsiz IN BINARY_INTEGER,
 wmsgblk OUT RAW) RETURN RAW;

Where Table C-23 describes the parameters in this function:

Defaults and Optional Parameters

Table C-24 describes the defaults and optional parameters for the MAKE_NUMBER_TO_RAW_FORMAT function:

Return Value

A RAW(2048) format conversion specification for NUMBER_TO_RAW.

Error and Warning Messages

If you receive an ORA-xxxx error or warning message, refer to the Oracle Database Error Messages guide for an explanation and information about how to handle it.

C.2.6 RAW_TO_NUMBER_FORMAT

RAW_TO_NUMBER_FORMAT converts, according to the RAW_TO_NUMBER conversion format r2nfmt, a RAW byte-string rawval in the remote host internal format into an Oracle number.

Syntax

function RAW_TO_NUMBER_FORMAT (rawval IN RAW,
 r2nfmt IN RAW) RETURN NUMBER;

where Table C-25 describes the parameters in this function:

Defaults

None

Return Value

An Oracle number corresponding in value to r.

Error and Warning Messages

If you receive an ORA-xxxx error or warning message, refer to the Oracle Database Error Messages guide for an explanation and information about how to handle it.

C.2.7 NUMBER_TO_RAW_FORMAT

NUMBER_TO_RAW_FORMAT converts, according to the NUMBER_TO_RAW conversion format n2rfmt, an Oracle number numval of declared precision and scale into a RAW byte-string in the remote host internal format.

Syntax

function NUMBER_TO_RAW_FORMAT (numval IN NUMBER,
 n2rfmt IN RAW) RETURN RAW;

Where Table C-26 describes the parameters in this function:

Defaults

None

Return Value

A RAW value corresponding in value to n.

Error and Warning Messages

If you receive an ORA-xxxx error or warning message, refer to the Oracle Database Error Messages guide for an explanation and information about how to handle it.

C.2.8 WMSGCNT

WMSGCNT tests a wmsgblk to determine how many warnings, if any, are present.

Syntax

function WMSGCNT (wmsgblk IN RAW) RETURN BINARY_INTEGER;

Where Table C-27 describes the parameter in this function.

Defaults

None

Return Value

A BINARY_INTEGER value equal to the count of warnings present in the RAW wmsgblk.

Table C-28 lists possible returned values:

Error and Warning Messages

If you receive an ORA-xxxx error or warning message, refer to the Oracle Database Error Messages guide for an explanation and information about how to handle it.

C.2.9 WMSG

WMSG extracts a warning message specified by wmsgitem from wmsgblk.

Syntax

function WMSG (wmsgblk IN RAW,
 wmsgitem IN BINARY_INTEGER,
 wmsgno OUT BINARY_INTEGER,
 wmsgtext OUT VARCHAR2,
 wmsgfill OUT VARCHAR2) RETURN BINARY_INTEGER;

Where Table C-29 describes the parameters in this function:

Defaults

None

Return Value

A BINARY_INTEGER value containing a status return code.

A return code of "0" indicates that wmsgno, wmsgtext, and wmsgfill are assigned and valid.

Error and Warning Messages

If you receive an ORA-xxxx error or warning message, refer to the Oracle Database Error Messages guide for an explanation and information about how to handle it.

Table C-30 describes the error messages you could receive: