Oracle8i Supplied Packages Reference
Release 8.1.5
A68001-01

Library
Product
Contents
Index

Prev Next

59
UTL_RAW

The UTL_RAW package provides SQL functions for manipulating RAW datatypes. This package is necessary because normal SQL functions do not operate on RAWs, and PL/SQL does not allow overloading between a RAW and a CHAR datatype. UTL_RAW also includes subprograms that convert various COBOL number formats to, and from, RAWs.

UTL_RAW is not specific to the database environment, and it may actually be used in other environments as it exists here. For this reason, the prefix UTL has been given to the package, instead of DBMS.

Usage Notes

The are many possible uses for the RAW functions. UTL_RAW allows a RAW "record" to be composed of many elements. By using the RAW datatype, character set conversion will not be performed keeping the RAW in its original format when being transferred through remote procedure calls (RPC).

The RAW functions also provide the ability to manipulate binary data which was previously limited to the hextoraw and rawtohex functions.

Summary of Subprograms

Table 59-1 UTL_RAW Package Subprograms
Subprogram  Description  
CONCAT function
 

Concatenates up to 12 RAWs into a single RAW.  

 
CAST_TO_RAW function
 

Converts a VARCHAR2 represented using n data bytes into a RAW with n data bytes.  

 
CAST_TO_VARCHAR2 function
 

Converts a RAW represented using n data bytes into VARCHAR2 with n data bytes.  

 
LENGTH function
 

Returns the length in bytes of a RAW r.  

 
SUBSTR function
 

Returns len bytes, starting at pos from RAW r.  

 
TRANSLATE function
 

Translates the bytes in the input RAW r according to the bytes in the translation RAWs from_set and to_set.  

 
TRANSLITERATE function
 

Converts the bytes in the input RAW r according to the bytes in the transliteration RAWs from_set and to_set.  

 
OVERLAY function
 

Overlays the specified portion of target RAW with overlay RAW, starting from byte position pos of target and proceding for len bytes.  

 
COPIES function
 

Returns n copies of r concatenated together.  

 
XRANGE function
 

Returns a RAW containing all valid 1-byte encodings in succession, beginning with the value start_byte and ending with the value end_byte.  

 
REVERSE function
 

Reverses a byte sequence in RAW r from end to end.  

 
COMPARE function
 

Compares RAW r1 against RAW r2.  

 
CONVERT function
 

Converts RAW r from character set from_charset to character set to_charset and returns the resulting RAW.  

 
BIT_AND function
 

Performs bitwise logical "and" of the values in RAW r1 with RAW r2 and returns the "anded" result RAW.  

 
BIT_OR function
 

Performs bitwise logical "or" of the values in RAW r1 with RAW r2 and returns the "or'd" result RAW.  

 
BIT_XOR function
 

Performs bitwise logical "exclusive or" of the values in RAW r1 with RAW r2 and returns the "xor'd" result RAW.  

 
BIT_COMPLEMENT function
 

Performs bitwise logical "complement" of the values in RAW r and returns the "complement'ed" result RAW.  

CONCAT function

This function concatenates up to 12 RAWs into a single RAW. If the concatenated size exceeds 32K, then an error is returned

Syntax

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;

Pragmas

pragma restrict_references(concat, WNDS, RNDS, WNPS, RNPS);

Parameters

r1....r12 are the RAW items to concatenate.

Returns

Table 59-2 CONCAT Function Returns
Return  Description  
RAW
 

Containing the items concatenated.  

Errors

There is an error if the sum of the lengths of the inputs exceeds the maximum allowable length for a RAW, which is 32767 bytes.

CAST_TO_RAW function

This function converts a VARCHAR2 represented using n data bytes into a RAW with n data bytes. The data is not modified in any way, only its datatype is recast to a RAW datatype.

Syntax

UTL_RAW.CAST_TO_RAW (
   c IN VARCHAR2) 
  RETURN RAW;

Pragmas

pragma restrict_references(cast_to_raw, WNDS, RNDS, WNPS, RNPS);

Parameters

Table 59-3 CAST_TO_RAW Function Parameters
Parameter  Description  
c
 

VARCHAR2 to be changed to a RAW.  

Returns

Table 59-4 CAST_TO_RAW Function Returns
Return  Description  
RAW
 

Containing the same data as the input VARCHAR2 and equal byte length as the input VARCHAR2 and without a leading length field.  

 
NULL
 

If c input parameter was NULL.  

Errors

None.

CAST_TO_VARCHAR2 function

This function converts a RAW represented using n data bytes into VARCHAR2 with n data bytes.

Note:

When casting to a VARCHAR2, the current NLS character set is used for the characters within that VARCHAR2.  

Syntax

UTL_RAW.CAST_TO_VARCHAR2 (
   r IN RAW) 
  RETURN VARCHAR2;

Pragmas

pragma restrict_references(cast_to_varchar2, WNDS, RNDS, WNPS, RNPS);

Parameters

Table 59-5 CAST_TO_VARCHAR2 Function Parameters
Parameter  Description  
r
 

RAW (without leading length field) to be changed to a VARCHAR2).  

Returns

Table 59-6 CAST_TO_VARCHAR2 Function Returns
Return  Description  
VARCHAR2
 

Containing having the same data as the input RAW.  

 
NULL
 

If r input parameter was NULL.  

Errors

None.

LENGTH function

This function returns the length in bytes of a RAW r.

Syntax

UTL_RAW.LENGTH (
   r IN RAW) 
  RETURN NUMBER;

Pragmas

pragma restrict_references(length, WNDS, RNDS, WNPS, RNPS);

Parameters

Table 59-7 LENGTH Function Parameters
Parameter  Description  
r
 

The RAW byte stream to be measured.  

Returns

Table 59-8 LENGTH Function Returns
Return  Description  
NUMBER
 

Equal to the current length of the RAW.  

Errors

None.

SUBSTR function

This function returns len bytes, starting at pos from RAW r.

Syntax

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

Pragmas

pragma restrict_references(substr, WNDS, RNDS, WNPS, RNPS);

Parameters

If pos is positive, then SUBSTR counts from the beginning of r to find the first byte. If pos is negative, then SUBSTR counts backwards from the end of the r. The value pos cannot be 0.

If len is omitted, then SUBSTR returns all bytes to the end of r. The value len cannot be less than 1.

Table 59-9 SUBSTR Function Parameters
Parameter  Description  
r
 

The RAW byte-string from which a portion is extracted.  

 
pos
 

The byte position in r at which to begin extraction.  

 
len
 

The number of bytes from pos to extract from r (optional).  

Defaults and Optional Parameters

Table 59-10 SUBSTR Function Exceptions
Optional Parameter  Description  
len
 

Position pos through to the end of r.  

Returns

Table 59-11 SUBSTR Function Returns
Return  Description  
portion of r
 

Beginning at pos for len bytes long.  

 
NULL
 

R input parameter was NULL.  

Errors

Table 59-12 SUBSTR Function Errors
Error  Description  
VALUE_ERROR
 

Either pos = 0 or len < 0  

TRANSLATE function

This function translates the bytes in the input RAW r according to the bytes in the translation RAWs from_set and to_set. If a byte in r has a matching byte in from_set, then it is replaced by the byte in the corresponding position in to_set, or deleted.

Bytes in r, but undefined in from_set, are copied to the result. Only the first (leftmost) occurrence of a byte in from_set is used. Subsequent duplicates are not scanned and are ignored. If to_set is shorter than from_set, then the extra from_set bytes have no translation correspondence and any bytes in r matching.

Note:

Difference from TRANSLITERATE:

- Translation RAWs have no defaults.

- r bytes undefined in the to_set translation RAW are deleted.

- Result RAW may be shorter than input RAW r.  

Syntax

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

Pragmas

pragma restrict_references(translate, WNDS, RNDS, WNPS, RNPS);

Parameters

Table 59-13 TRANSLATE Function Parameters
Parameter  Description  
r
 

RAW source byte-string to be translated.  

 
from_set
 

RAW byte-codes to be translated, if present in r.  

 
to_set
 

RAW byte-codes to which corresponding from_str bytes are translated.  

Returns

Table 59-14 TRANSLATE Function Returns
Return  Description  
RAW
 

Translated byte-string.  

Errors

Table 59-15 TRANSLATE Function Errors
Error  Description  
VALUE_ERROR
 

Either:

- r is NULL and/or has 0 length

- from_set is NULL and/or has 0 length

- to_set is NULL and/or has 0 length  

TRANSLITERATE function

This function converts the bytes in the input RAW r according to the bytes in the transliteration RAWs from_set and to_set. Successive bytes in r are looked-up in the from_set, and, if not found, copied unaltered to the result RAW. If found, then they are replaced in the result RAW by either corresponding bytes in the to_set, or the pad byte when no correspondence exists.

Bytes in r, but undefined in from_set, are copied to the result. Only the first (leftmost) occurrence of a byte in from_set is used. Subsequent duplicates are not scanned and are ignored. The result RAW is always the same length as r.

If the to_set is shorter than the from_set, then the pad byte is placed in the result RAW when a selected from_set byte has no corresponding to_set byte (as if the to_set were extended to the same length as the from_set with pad bytes).

Note:

Difference from TRANSLATE:

- r bytes undefined in to_set are padded.

- Result RAW is always same length as input RAW r.  

Syntax

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;

Pragmas

pragma restrict_references(transliterate, WNDS, RNDS, WNPS, RNPS);

Parameters

Table 59-16 TRANSLITERATE Function Parameters
Parameter  Description  
r
 

RAW input byte-string to be converted.  

 
from_set
 

RAW byte-codes to be converted, if present in r (any length).  

 
to_set
 

RAW byte-codes to which corresponding from_set bytes are converted (any length).  

 
pad
 

1 byte used when to-set is shorter than the from_set.  

Defaults and Optional Parameters

Table 59-17 TRANSLITERATE Function Optional Parameters
Optional Parameter  Description  
from_set
 

x'00 through x'ff.  

 
to_set
 

To the NULL string and effectively extended with pad to the length of from_set as necessary.  

 
pad
 

x'00'.  

Returns

Table 59-18 TRANSLITERATE Function Returns
Return  Description  
RAW
 

Converted byte-string.  

Errors

Table 59-19 TRANSLITERATE Function Errors
Error  Description  
VALUE_ERROR
 

R is NULL and/or has 0 length.  

OVERLAY function

This function overlays the specified portion of target RAW with overlay RAW, starting from byte position pos of target and proceeding for len bytes.

If overlay has less than len bytes, then it is extended to len bytes using the pad byte. If overlay exceeds len bytes, then the extra bytes in overlay are ignored. If len bytes beginning at position pos of target exceeds the length of target, then target is extended to contain the entire length of overlay.

len, if specified, must be greater than, or equal to, 0. pos, if specified, must be greater than, or equal to, 1. If pos exceeds the length of target, then target is padded with pad bytes to position pos, and then target is further extended with overlay bytes.

Syntax

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;

Pragmas

pragma restrict_references(overlay, WNDS, RNDS, WNPS, RNPS);

Parameters

Table 59-20 OVERLAY Function Parameters
Parameters  Description  
overlay_str
 

Byte-string used to overlay target.  

 
target
 

Byte-string which is to be overlayed.  

 
pos
 

Position in target (numbered from 1) to start overlay.  

 
len
 

The number of target bytes to overlay.  

 
pad
 

Pad byte used when overlay len exceeds overlay length or pos exceeds target length.  

Defaults and Optional Parameters

Table 59-21 OVERLAY Function Optional Parameters
Optional Parameter  Description  
pos
 

1  

 
len
 

To the length of overlay  

 
pad
 

x'00'  

Returns

Table 59-22 OVERLAY Function Returns
Return  Description  
RAW
 

The target byte_string overlayed as specified.  

Errors

Table 59-23 OVERLAY Function Errors
Error  Description  
VALUE_ERROR
 

Either:

- Overlay is NULL and/or has 0 length

- Target is missing or undefined

- Length of target exceeds maximum length of a RAW

- len < 0

- pos < 1  

COPIES function

This function returns n copies of r concatenated together.

Syntax

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

Pragmas

pragma restrict_references(copies, WNDS, RNDS, WNPS, RNPS);

Parameters

Table 59-24 COPIES Function Parameters
Parameters  Description  
r
 

RAW to be copied  

 
n
 

Number of times to copy the RAW (must be positive).  

Returns

This returns the RAW copied n times.

Errors

Table 59-25 COPIES Function Errors
Error  Description  
VALUE_ERROR
 

Either:

- r is missing, NULL and/or 0 length

- n < 1

- Length of result exceeds maximum length of a RAW  

XRANGE function

This function 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, then the succession of result bytes begin with start_byte, wrap through 'FF'x to '00'x, and end at end_byte. If specified, then start_byte and end_byte must be single byte RAWs.

Syntax

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

Pragmas

pragma restrict_references(xrange, WNDS, RNDS, WNPS, RNPS);

Parameters

Table 59-26 XRANGE Function Parameters
Parameters  Description  
start_byte
 

Beginning byte-code value of resulting sequence.  

 
end_byte
 

Ending byte-code value of resulting sequence.  

Defaults and Optional Parameters

start_byte - x'00'
start_byte - x'00'
end_byte   - x'FF'

Returns

Table 59-27 XRANGE Function Returns
Return  Description  
RAW
 

Containing succession of 1-byte hexadecimal encodings.  

Errors

None.

REVERSE function

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

Syntax

UTL_RAW.REVERSE (
   r IN RAW) 
  RETURN RAW;

Pragmas

pragma restrict_references(reverse, WNDS, RNDS, WNPS, RNPS);

Parameters

Table 59-28 REVERSE Function Parameters
Parameter  Description  
r
 

RAW to reverse.  

Returns

Table 59-29 REVERSE Function Returns
Return  Description  
RAW
 

Containing the "reverse" of r.  

Errors

Table 59-30 REVERSE Function Errors
Error  Description  
VALUE_ERROR
 

R is NULL and/or has 0 length.  

COMPARE function

This function compares RAW r1 against RAW r2. If r1 and r2 differ in length, then the shorter RAW is extended on the right with pad if necessary.

Syntax

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

Pragmas

pragma restrict_references(compare, WNDS, RNDS, WNPS, RNPS);

Parameters

Table 59-31 COMPARE Function Parameters
Parameter  Description  
r1
 

1st RAW to be compared, may be NULL and/or 0 length.  

 
r2
 

2nd RAW to be compared, may be NULL and/or 0 length.  

 
pad
 

Byte to extend whichever of r1 or r2 is shorter.  

Defaults and optional parameters

pad - x'00'

Returns

Table 59-32 COMPARE Function Returns
Return  Description  
NUMBER
 

Equals 0 if RAW byte strings are both NULL or identical; or,

Equals position (numbered from 1) of the first mismatched byte.  

Errors

None.

CONVERT function

This function converts RAW r from character set from_charset to character set to_charset and returns the resulting RAW.

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

Syntax

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

Pragmas

pragma restrict_references(convert, WNDS, RNDS, WNPS, RNPS);

Parameters

Table 59-33 CONVERT Function Parameters
Parameter  Description  
r
 

RAW byte-string to be converted.  

 
to_charset
 

Name of NLS character set to which r is converted.  

 
from_charset
 

Name of NLS character set in which r is supplied.  

Returns

Table 59-34 CONVERT Function Returns
Return  Description  
RAW
 

Byte string r converted according to the specified character sets.  

Errors

Table 59-35 CONVERT Function Errors
Error  Description  
VALUE_ERROR
 

Either:

- r missing, NULL, and/or 0 length

- from_charset or to_charset missing, NULL, and/or 0 length

- from_charset or to_charset names invalid or unsupported  

BIT_AND function

This function performs bitwise logical "and" of the values in RAW r1 with RAW r2 and returns the "anded" result RAW.

If r1 and r2 differ in length, then the "and" operation is terminated after the last byte of the shorter of the two RAWs, and the unprocessed portion of the longer RAW is appended to the partial result. The result length equals the longer of the two input RAWs.

Syntax

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

Pragmas

pragma restrict_references(bit_and, WNDS, RNDS, WNPS, RNPS);

Parameters

Table 59-36 BIT_AND Function Parameters
Parameter  Description  
r1
 

RAW to "and" with r2.  

 
r2
 

RAW to "and" with r1.  

Returns

Table 59-37 BIT_AND Function Returns
Return  Description  
RAW
 

Containing the "and" of r1 and r2.  

 
NULL
 

Either r1 or r2 input parameter was NULL.  

Errors

None.

BIT_OR function

This function performs bitwise logical "or" of the values in RAW r1 with RAW r2 and returns the "or'd" result RAW.

If r1 and r2 differ in length, then the "or" operation is terminated after the last byte of the shorter of the two RAWs, and the unprocessed portion of the longer RAW is appended to the partial result.The result length equals the longer of the two input RAWs.

Syntax

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

Pragmas

pragma restrict_references(bit_or, WNDS, RNDS, WNPS, RNPS);

Parameters

Table 59-38 BIT_OR Function Parameters
Parameters  Description  
r1
 

RAW to "or" with r2.  

 
r2
 

RAW to "or" with r1.  

Returns

Table 59-39 BIT_OR Function Returns
Return  Description  
RAW
 

Containing the "or" of r1 and r2.  

 
NULL
 

Either r1 or r2 input parameter was NULL.  

Errors

None.

BIT_XOR function

This function performs bitwise logical "exclusive or" of the values in RAW r1 with RAW r2 and returns the "xor'd" result RAW.

If r1 and r2 differ in length, then the "xor" operation is terminated after the last byte of the shorter of the two RAWs, and the unprocessed portion of the longer RAW is appended to the partial result.The result length equals the longer of the two input RAWs.

Syntax

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

Pragmas

pragma restrict_references(bit_xor, WNDS, RNDS, WNPS, RNPS);

Parameters

Table 59-40 BIT_XOR Function Parameters
Parameter  Description  
r1
 

RAW to "xor" with r2.  

 
r2
 

RAW to "xor" with r1 .  

Returns

Table 59-41 BIT_XOR Function Returns
Return  Description  
RAW
 

Containing the "xor" of r1 and r2.  

 
NULL
 

If either r1 or r2 input parameter was NULL.  

Errors

None.

BIT_COMPLEMENT function

This function performs bitwise logical "complement" of the values in RAW r and returns the "complement'ed" result RAW. The result length equals the input RAW r length.

Syntax

UTL_RAW.BIT_COMPLEMENT (
   r IN RAW) 
  RETURN RAW;

Pragmas 

pragma restrict_references(bit_complement, WNDS, RNDS, WNPS, RNPS);

Parameters

Table 59-42 BIT_COMPLEMENT Function Parameters
Parameter  Description  
r
 

RAW to perform "complement" operation.  

Returns

Table 59-43 BIT_COMPLEMENT Function Returns
Return  Description  
RAW
 

The "complement" of r1.  

 
NULL
 

If r input parameter was NULL.  

Errors

None.


Prev
Next
Oracle
Copyright © 1999 Oracle Corporation.
All Rights Reserved.

Library
Product
Contents
Index