13 UTL_RAW
The UTL_RAW
package provides SQL functions for manipulating RAW
data types.
This chapter contains the following topics:
-
-
Overview
-
Operational notes
-
Using UTL_RAW
Overview
This package is necessary because SQL functions do not operate on RAW
values and PL/SQL does not allow overloading between a RAW
and a CHAR
data type.
UTL_RAW
is not specific to the database environment and may be used in other environments. For this reason, the prefix UTL
has been given to the package, instead of DBMS
.
Operational Notes
UTL_RAW
allows a RAW
record to be composed of many elements. When the RAW
data type is used, character set conversion is not performed, keeping the RAW
value in its original format when being transferred through remote procedure calls.
With the RAW
functions, you can manipulate binary data that was previously limited to the hextoraw
and rawtohex
SQL functions.
Functions returning RAW
values do so in hexadecimal encoding.
UTL_RAW Subprograms
Table 13-1 summarizes the UTL_RAW
subprograms, followed by a full description of each subprogram.
Table 13-1 UTL_RAW Package Subprograms
Subprogram | Description |
---|---|
Performs bitwise logical |
|
Performs bitwise logical |
|
Performs bitwise logical |
|
Performs bitwise logical |
|
Returns the |
|
Returns the |
|
Returns the |
|
Returns the |
|
Casts the |
|
Casts the |
|
Casts the |
|
Casts the |
|
Casts a |
|
Casts a |
|
Casts a |
|
Compares two |
|
Concatenates up to 12 |
|
Converts a |
|
Copies a |
|
Returns the length in bytes of a |
|
Overlays the specified portion of a target |
|
Reverses a byte-sequence in a |
|
Returns a substring of a |
|
Translates the specified bytes from an input |
|
Converts the specified bytes from an input |
|
Returns a |
Note:
-
The
PLS_INTEGER
andBINARY_INTEGER
data types are identical. This document usesBINARY_INTEGER
to indicate data types in reference information (such as for table types, record types, subprogram parameters, or subprogram return values), but may use either in discussion and examples. -
The
INTEGER
andNUMBER(38)
data types are also identical. This document usesINTEGER
throughout.
BIT_AND Function
This function performs bitwise logical AND
of two supplied RAW
values and returns the resulting RAW
.
Syntax
UTL_RAW.BIT_AND (
r1 IN RAW,
r2 IN RAW)
RETURN RAW;
Parameters
Table 13-2 BIT_AND Function Parameters
Parameter | Description |
---|---|
|
First |
|
Second |
Return Value
Result of the AND
operation, or NULL
if either input value is NULL
Usage Notes
If r1
and r2
differ in length, the operation is terminated after the last byte of the shorter of the two RAW
values, and the unprocessed portion of the longer RAW
value is appended to the partial result. The resulting length equals that of the longer of the two input values.
BIT_COMPLEMENT Function
This function performs bitwise logical COMPLEMENT
of the supplied RAW
value and returns the resulting RAW
. The result length equals the input RAW
length.
Syntax
UTL_RAW.BIT_COMPLEMENT (
r IN RAW)
RETURN RAW;
Parameters
Table 13-3 BIT_COMPLEMENT Function Parameters
Parameter | Description |
---|---|
|
|
Return Value
Result of the COMPLEMENT
operation, or NULL
if the input value is NULL
BIT_OR Function
This function performs bitwise logical OR
of two supplied RAW
values and returns the resulting RAW
.
Syntax
UTL_RAW.BIT_OR (
r1 IN RAW,
r2 IN RAW)
RETURN RAW;
Parameters
Table 13-4 BIT_OR Function Parameters
Parameters | Description |
---|---|
|
First |
|
Second |
Return Value
Result of the OR
operation, or NULL
if either input value is NULL
Usage Notes
If r1
and r2
differ in length, the operation is terminated after the last byte of the shorter of the two RAW
values, and the unprocessed portion of the longer RAW
value is appended to the partial result. The resulting length equals that of the longer of the two input values.
BIT_XOR Function
This function performs bitwise logical XOR
("exclusive or") of two supplied RAW
values and returns the resulting RAW
.
Syntax
UTL_RAW.BIT_XOR (
r1 IN RAW,
r2 IN RAW)
RETURN RAW;
Parameters
Table 13-5 BIT_XOR Function Parameters
Parameter | Description |
---|---|
|
First |
|
Second |
Return Value
Result of the XOR
operation, or NULL
if either input value is NULL
Usage Notes
If r1
and r2
differ in length, the operation is terminated after the last byte of the shorter of the two RAW
values, and the unprocessed portion of the longer RAW
value is appended to the partial result. The resulting length equals that of the longer of the two input values.
CAST_FROM_BINARY_DOUBLE Function
This function returns the RAW
binary representation of a BINARY_DOUBLE
value.
Syntax
UTL_RAW.CAST_FROM_BINARY_DOUBLE(
n IN BINARY_DOUBLE,
endianess IN BINARY_INTEGER DEFAULT 1)
RETURN RAW;
Parameters
Table 13-6 CAST_FROM_BINARY_DOUBLE Function Parameters
Parameter | Description |
---|---|
|
The |
|
The function recognizes the defined constants |
Return Value
RAW
binary representation of the BINARY_DOUBLE
value, or NULL
if the input is NULL
Usage Notes
-
An eight-byte
BINARY_DOUBLE
value maps to the IEEE 754 double-precision format as follows:byte 0: bit 63 ~ bit 56 byte 1: bit 55 ~ bit 48 byte 2: bit 47 ~ bit 40 byte 3: bit 39 ~ bit 32 byte 4: bit 31 ~ bit 24 byte 5: bit 23 ~ bit 16 byte 6: bit 15 ~ bit 8 byte 7: bit 7 ~ bit 0
-
Parameter
endianess
specifies how the bytes of theBINARY_DOUBLE
value are mapped to the bytes of theRAW
value. In the following matrix,rb0
torb7
refer to the bytes of theRAW
anddb0
todb7
refer to the bytes of theBINARY_DOUBLE
.Endianess rb0 rb1 rb2 rb3 rb4 rb5 rb6 rb7 big_endian
db0
db1
db2
db3
db4
db5
db6
db7
little_endian
db7
db6
db5
db4
db3
db2
db1
db0
-
When
machine_endian
is specified, the eight bytes of theBINARY_DOUBLE
argument are copied straight across into theRAW
return value. The effect is the same as if the user specifiedbig_endian
on a big-endian system orlittle_endian
on a little-endian system.
CAST_FROM_BINARY_FLOAT Function
This function returns the RAW
binary representation of a BINARY_FLOAT
value.
Syntax
UTL_RAW.CAST_FROM_BINARY_FLOAT(
n IN BINARY_FLOAT,
endianess IN BINARY_INTEGER DEFAULT 1)
RETURN RAW;
Parameters
Table 13-7 CAST_FROM_BINARY_FLOAT Function Parameters
Parameter | Description |
---|---|
|
The |
|
The function recognizes the defined constants |
Return Value
RAW
binary representation of the BINARY_FLOAT
value, or NULL
if the input is NULL
Usage Notes
-
A four-byte
BINARY_FLOAT
value maps to the IEEE 754 single-precision format as follows:byte 0: bit 31 ~ bit 24 byte 1: bit 23 ~ bit 16 byte 2: bit 15 ~ bit 8 byte 3: bit 7 ~ bit 0
-
The parameter
endianess
specifies how the bytes of theBINARY_FLOAT
value are mapped to the bytes of theRAW
value. In the following matrix,rb0
torb3
refer to the bytes of theRAW
andfb0
tofb3
refer to the bytes of theBINARY_FLOAT
.Endianess rb0 rb1 rb2 rb3 big_endian
fbo
fb1
fb2
fb3
little_endian
fb3
fb2
fb1
fb0
-
When
machine_endian
is specified, the four bytes of theBINARY_FLOAT
argument are copied straight across into theRAW
return value. The effect is the same as if the user specifiedbig_endian
on a big-endian system orlittle_endian
on a little-endian system.
CAST_FROM_BINARY_INTEGER Function
This function returns the RAW
binary representation of a BINARY_INTEGER
value.
Syntax
UTL_RAW.CAST_FROM_BINARY_INTEGER ( n IN BINARY_INTEGER endianess IN BINARY_INTEGER DEFAULT 1) RETURN RAW;
Parameters
Table 13-8 CAST_FROM_BINARY_INTEGER Function Parameters
Parameter | Description |
---|---|
|
The |
|
The function recognizes the defined constants |
Return Value
RAW
binary representation of the BINARY_INTEGER
value, or NULL
if the input is NULL
CAST_FROM_NUMBER Function
This function returns the RAW
binary representation of a NUMBER
value.
Syntax
UTL_RAW.CAST_FROM_NUMBER (
n IN NUMBER)
RETURN RAW;
Parameters
Table 13-9 CAST_FROM_NUMBER Function Parameters
Parameter | Description |
---|---|
|
The |
Return Value
RAW
binary representation of the NUMBER
value, or NULL
if the input is NULL
CAST_TO_BINARY_DOUBLE Function
This function casts the RAW
binary representation of a BINARY_DOUBLE
value into a BINARY_DOUBLE
value.
Syntax
UTL_RAW.CAST_TO_BINARY_DOUBLE (
r IN RAW
endianess IN BINARY_INTEGER DEFAULT 1)
RETURN BINARY_DOUBLE;
Parameters
Table 13-10 CAST_TO_BINARY_DOUBLE Function Parameters
Parameter | Description |
---|---|
|
|
|
The function recognizes the defined constants |
Return Value
The BINARY_DOUBLE
value, or NULL
if the input is NULL
Usage Notes
-
If the
RAW
argument is more than eight bytes, only the first eight bytes are used and the rest of the bytes are ignored. If the result is -0, +0 is returned. If the result isNaN
, the valueBINARY_DOUBLE_NAN
is returned. -
An eight-byte
BINARY_DOUBLE
value maps to the IEEE 754 double-precision format as follows:byte 0: bit 63 ~ bit 56 byte 1: bit 55 ~ bit 48 byte 2: bit 47 ~ bit 40 byte 3: bit 39 ~ bit 32 byte 4: bit 31 ~ bit 24 byte 5: bit 23 ~ bit 16 byte 6: bit 15 ~ bit 8 byte 7: bit 7 ~ bit 0
-
The parameter
endianess
specifies how the bytes of theBINARY_DOUBLE
value are mapped to the bytes of theRAW
value. In the following matrix,rb0
torb7
refer to the bytes inRAW
anddb0
todb7
refer to the bytes inBINARY_DOUBLE
.Endianess rb0 rb1 rb2 rb3 rb4 rb5 rb6 rb7 big_endian
db0
db1
db2
db3
db4
db5
db6
db7
little_endian
db7
db6
db5
db4
db3
db2
db1
db0
-
When
machine_endian
is specified, the eight bytes of theRAW
argument are copied straight across into theBINARY_DOUBLE
return value. The effect is the same as if the user specifiedbig_endian
on a big-endian system orlittle_endian
on a little-endian system.
Exceptions
If the RAW
argument is less than eight bytes, a VALUE_ERROR
exception is raised.
CAST_TO_BINARY_FLOAT Function
This function casts the RAW
binary representation of a BINARY_FLOAT
value into a BINARY_FLOAT
value.
Syntax
UTL_RAW.CAST_TO_BINARY_FLOAT (
r IN RAW
endianess IN BINARY_INTEGER DEFAULT 1)
RETURN BINARY_FLOAT;
Parameters
Table 13-11 CAST_TO_BINARY_FLOAT Function Parameters
Parameter | Description |
---|---|
|
|
|
The function recognizes the defined constants |
Return Value
The BINARY_FLOAT
value, or NULL
if the input is NULL
Usage Notes
-
If the
RAW
argument is more than four bytes, only the first four bytes are used and the rest of the bytes are ignored. If the result is -0, +0 is returned. If the result isNaN
, the valueBINARY_FLOAT_NAN
is returned. -
A four-byte
BINARY_FLOAT
value maps to the IEEE 754 single-precision format as follows:byte 0: bit 31 ~ bit 24 byte 1: bit 23 ~ bit 16 byte 2: bit 15 ~ bit 8 byte 3: bit 7 ~ bit 0
-
The parameter
endianess
specifies how the bytes of theBINARY_FLOAT
value are mapped to the bytes of theRAW
value. In the following matrix,rb0
torb3
refer to the bytes inRAW
andfb0
tofb3
refer to the bytes inBINARY_FLOAT
.Endianess rb0 rb1 rb2 rb3 big_endian
fbo
fb1
fb2
fb3
little_endian
fb3
fb2
fb1
fb0
-
When
machine_endian
is specified, the four bytes of theRAW
argument are copied straight across into theBINARY_FLOAT
return value. The effect is the same as if the user specifiedbig_endian
on a big-endian system orlittle_endian
on a little-endian system.
Exceptions
If the RAW
argument is less than four bytes, a VALUE_ERROR
exception is raised.
CAST_TO_BINARY_INTEGER Function
This function casts the RAW
binary representation of a BINARY_INTEGER
value into a BINARY_INTEGER
value.
Syntax
UTL_RAW.CAST_TO_BINARY_INTEGER (
r IN RAW
endianess IN BINARY_INTEGER DEFAULT 1)
RETURN BINARY_INTEGER;
Parameters
Table 13-12 CAST_TO_BINARY_INTEGER Function Parameters
Parameter | Description |
---|---|
|
|
|
The function recognizes the defined constants |
Return Value
The BINARY_INTEGER
value, or NULL
if the input is NULL
CAST_TO_NUMBER Function
This function casts the RAW
binary representation of a NUMBER
value into a NUMBER
value.
Syntax
UTL_RAW.CAST_TO_NUMBER (
r IN RAW)
RETURN NUMBER;
Parameters
Table 13-13 CAST_TO_NUMBER Function Parameters
Parameter | Description |
---|---|
|
|
Return Value
The NUMBER
value, or NULL
if the input is NULL
CAST_TO_NVARCHAR2 Function
This function casts a RAW
value represented using some number of data bytes into an NVARCHAR2
value with that number of data bytes.
Note:
When casting to NVARCHAR2
, the current Globalization Support character set is used for the characters within that NVARCHAR2
value.
Syntax
UTL_RAW.CAST_TO_NVARCHAR2 (
r IN RAW)
RETURN NVARCHAR2;
Parameters
Table 13-14 CAST_TO_NVARCHAR2 Function Parameters
Parameter | Description |
---|---|
|
|
Return Value
Data converted from the input RAW
value, or NULL
if the input is NULL
CAST_TO_RAW Function
This function casts a VARCHAR2
value represented using some number of data bytes into a RAW
value with that number of data bytes. The data itself is not modified in any way, but its data type is recast to a RAW
data type.
Syntax
UTL_RAW.CAST_TO_RAW (
c IN VARCHAR2)
RETURN RAW;
Parameters
Table 13-15 CAST_TO_RAW Function Parameters
Parameter | Description |
---|---|
|
|
Return Values
Data converted from the input VARCHAR2
value, with the same byte-length as the input value but without a leading length field, or NULL
if the input is NULL
CAST_TO_VARCHAR2 Function
This function casts a RAW value represented using some number of data bytes into a VARCHAR2 value with that number of data bytes.
Note:
When casting to VARCHAR2
, the current Globalization Support character set is used for the characters within that VARCHAR2
value.
Syntax
UTL_RAW.CAST_TO_VARCHAR2 (
r IN RAW)
RETURN VARCHAR2;
Parameters
Table 13-16 CAST_TO_VARCHAR2 Function Parameters
Parameter | Description |
---|---|
|
|
Return Value
Data converted from the input RAW
value, or NULL
if the input is NULL
COMPARE Function
This function compares two RAW values. If they differ in length, then the shorter is extended on the right according to the optional pad parameter.
Syntax
UTL_RAW.COMPARE (
r1 IN RAW,
r2 IN RAW
[,pad IN RAW DEFAULT NULL])
RETURN NUMBER;
Parameters
Table 13-17 COMPARE Function Parameters
Parameter | Description |
---|---|
|
First Note: The value can be |
|
Second Note: The value can be |
|
Byte to extend whichever of the input values is shorter (default |
Return Value
A NUMBER
value that equals the position number (numbered from 1) of the first mismatched byte when comparing the two input values, or 0 if the input values are identical or both NULL
CONCAT Function
This function concatenates up to 12 RAW
values into a single RAW
value. If the concatenated size exceeds 32 KB, 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;
Parameters
Items r1...r12
are the RAW
items to concatenate.
Return Value
RAW
value consisting of the concatenated input values
Exceptions
There is an error if the sum of the lengths of the inputs exceeds the maximum allowable length for a RAW
value, which is 32767 bytes.
CONVERT Function
This function converts a RAW
value from one character set to another and returns the resulting RAW
value.
Both character sets must be supported character sets defined to the database.
Syntax
UTL_RAW.CONVERT (
r IN RAW,
to_charset IN VARCHAR2,
from_charset IN VARCHAR2)
RETURN RAW;
Parameters
Table 13-18 CONVERT Function Parameters
Parameter | Description |
---|---|
|
|
|
Name of Globalization Support character set to which the input value is converted |
|
Name of Globalization Support character set from which the input value is converted |
Return Value
Converted byte-string according to the specified character set
Exceptions
VALUE_ERROR
occurs under any of the following circumstances:
-
The input byte-string is missing,
NULL
, or zero-length. -
The
from_charset
orto_charset
parameter is missing,NULL
, or zero-length. -
The
from_charset
orto_charset
parameter is invalid or unsupported.
COPIES Function
This function returns a specified number of copies of a specified RAW
value, concatenated.
Syntax
UTL_RAW.COPIES (
r IN RAW,
n IN NUMBER)
RETURN RAW;
Parameters
Table 13-19 COPIES Function Parameters
Parameters | Description |
---|---|
|
|
|
Number of times to copy the Note: This must be a positive value. |
Return Value
RAW
value copied the specified number of times and concatenated
Exceptions
VALUE_ERROR
occurs under any of the following circumstances:
-
The value to be copied is missing,
NULL
, or zero-length. -
The number of times to copy the value is less than or equal to 0.
-
The length of the result exceeds the maximum allowable length for a
RAW
value, which is 32767 bytes.
LENGTH Function
This function returns the length in bytes of a RAW
value.
Syntax
UTL_RAW.LENGTH (
r IN RAW)
RETURN NUMBER;
Parameters
Table 13-20 LENGTH Function Parameters
Parameter | Description |
---|---|
|
|
Return Value
NUMBER
value indicating the length of the RAW
value, in bytes
OVERLAY Function
This function overlays the specified portion of a target RAW
value with an overlay RAW
, starting from a specified byte position and proceeding for a specified number of 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;
Parameters
Table 13-21 OVERLAY Function Parameters
Parameters | Description |
---|---|
|
Byte-string used to overlay target |
|
Target byte-string to be overlaid |
|
Byte position in target at which to start overlay, numbered from 1 (default 1) |
|
Number of bytes to overlay (default: length of |
|
Pad byte used when |
Return Value
RAW
target byte value overlaid as specified
Usage Notes
If overlay_str
has less than len
bytes, then it is extended to len
bytes using the pad
byte. If overlay_str
exceeds len
bytes, then the extra bytes in overlay_str
are ignored. If len
bytes beginning at position pos
of target
exceed the length of target
, then target
is extended to contain the entire length of overlay_str
.
If
len
is specified, it must be greater than or equal to 0. If pos
is specified, it 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 target
is further extended with overlay_str
bytes.
Exceptions
VALUE_ERROR
occurs under any of the following circumstances:
-
The
overlay_str
isNULL
or zero-length. -
The
target
is missing or undefined. -
The length of
target
exceeds the maximum length for aRAW
value, 32767 bytes. -
The
len
is less than 0. -
The
pos
is less than or equal to 0.
REVERSE Function
This function reverses a RAW
byte-sequence from end to end.
For example, x'0102F3'
would be reversed to x'F30201'
, and 'xyz'
would be reversed to 'zyx'
. The result length is the same as the input length.
Syntax
UTL_RAW.REVERSE (
r IN RAW)
RETURN RAW;
Parameters
Table 13-22 REVERSE Function Parameters
Parameter | Description |
---|---|
|
|
Return Value
RAW
value that is the reverse of the input value
Exceptions
VALUE_ERROR
occurs if the input value is NULL
or zero-length.
SUBSTR Function
This function returns a substring of a RAW
value for a specified number of bytes and starting position.
Syntax
UTL_RAW.SUBSTR (
r IN RAW,
pos IN BINARY_INTEGER
[,len IN BINARY_INTEGER DEFAULT NULL])
RETURN RAW;
Parameters
Table 13-23 SUBSTR Function Parameters
Parameter | Description |
---|---|
|
|
|
Byte position at which to begin extraction, either counting forward from the beginning of the input byte-string (positive value) or backward from the end (negative value) |
|
Number of bytes, beginning at |
Return Value
RAW
substring beginning at position pos
for len
bytes, or NULL
if the input is NULL
Usage Notes
If pos
is positive, SUBSTR
counts from the beginning of the RAW
byte-string to find the first byte. If pos
is negative, SUBSTR
counts backward from the end of the RAW
byte-string. The value of pos
cannot equal 0.
A specified value of len
must be positive. If len
is omitted, SUBSTR
returns all bytes to the end of the RAW
byte-string.
Exceptions
VALUE_ERROR
occurs under any of the following circumstances:
-
The
pos
equals 0 or is greater than the length ofr
. -
The
len
is less than or equal to 0. -
The
len
is greater than (length ofr
) minus (pos
-1).
Examples
Example 1: This example, run in ttIsql
, counts backward 15 bytes from the end of the input RAW
value for its starting position, then takes a substring of five bytes starting at that point.
declare
sr raw(32767);
r raw(32767);
begin
sr := hextoraw('1236567812125612344434341234567890ABAA1234');
r := UTL_RAW.SUBSTR(sr, -15, 5);
dbms_output.put_line('source raw: ' || sr);
dbms_output.put_line('return raw: ' || r);
end;
/
The result is as follows:
source raw: 1236567812125612344434341234567890ABAA1234
return raw: 5612344434
PL/SQL procedure successfully completed.
Here the input and output are presented, for purposes of this discussion, in a way that gives a clearer indication of the functionality:
source raw: 12 36 56 78 12 12 56 12 34 44 34 34 12 34 56 78 90 AB AA 12 34
return raw: 56 12 34 44 34
The substring starts at the 15th byte from the end.
Example 2: This example, run in ttIsql
, has the same input RAW
value and starting point as the preceding example, but because len
is not specified the substring is taken from the starting point to the end of the input.
declare
sr raw(32767);
r raw(32767);
begin
sr := hextoraw('1236567812125612344434341234567890ABAA1234');
r := UTL_RAW.SUBSTR(sr, -15);
dbms_output.put_line('source raw: ' || sr);
dbms_output.put_line('return raw: ' || r);
end;
/
Here is the result:
source raw: 1236567812125612344434341234567890ABAA1234
return raw: 5612344434341234567890ABAA1234
Here the input and output are presented, for purposes of this discussion, in a way that gives a clearer indication of the functionality:
source raw: 12 36 56 78 12 12 56 12 34 44 34 34 12 34 56 78 90 AB AA 12 34
return raw: 56 12 34 44 34 34 12 34 56 78 90 AB AA 12 34
TRANSLATE Function
This function performs a byte-by-byte translation of a RAW
value, given an input set of bytes, a set of bytes to search for and translate from in the input bytes, and a set of corresponding bytes to translate to.
Whenever a byte in the specified from_set
is found in the input RAW
value, it is translated to the corresponding byte in the to_set
for the output RAW
value, or it is simply not included in the output RAW
value if there is no corresponding byte in to_set
. Any bytes in the input RAW
value that do not appear in from_set
are simply copied as-is to the output RAW
value.
Syntax
UTL_RAW.TRANSLATE (
r IN RAW,
from_set IN RAW,
to_set IN RAW)
RETURN RAW;
Note:
Be aware that to_set
and from_set
are reversed in the calling sequence compared to TRANSLITERATE
.
Parameters
Table 13-24 TRANSLATE Function Parameters
Parameter | Description |
---|---|
|
|
|
Where found, they are translated in the result. |
|
Where a |
Return Value
RAW
value with the translated byte-string
Usage Notes
-
If
to_set
is shorter thanfrom_set
, the extrafrom_set
bytes have no corresponding translation bytes. Bytes from the inputRAW
value that match any suchfrom_set
bytes are not translated or included in the result. They are effectively translated toNULL
. -
If
to_set
is longer thanfrom_set
, the extrato_set
bytes are ignored. -
If a byte value is repeated in
from_set
, the repeated occurrence is ignored.
Note:
Differences from TRANSLITERATE
:
-
The
from_set
parameter comes before theto_set
parameter in the calling sequence. -
Bytes from the source byte-string that appear in
from_set
but have no corresponding values into_set
are not translated or included in the result. -
The resulting
RAW
value may be shorter than the inputRAW
value.
Note that TRANSLATE
and TRANSLITERATE
only differ in functionality when to_set
has fewer bytes than from_set
.
Exceptions
VALUE_ERROR
occurs if the source byte string, from_set
, or to_set
is NULL
or zero-length.
Examples
Example 1: In this example, run in ttIsql
, from_set
is x'12AA34'
and to_set
is x'CD'
. Wherever '12'
appears in the input RAW
value it is replaced by 'CD'
in the result. Wherever 'AA'
or '34'
appears in the input RAW
value, because there are no corresponding bytes in to_set
, those bytes are not included in the result (effectively translated to NULL
).
You can compare this to Examples in the TRANSLITERATE
section to see how the functions differ.
declare
sr raw(32767);
from_set raw(32767);
to_set raw(32767);
r raw(32767);
begin
sr := hextoraw('1236567812125612344434341234567890ABAA1234');
from_set := hextoraw('12AA34');
to_set := hextoraw('CD');
dbms_output.put_line('from_set: ' || from_set);
dbms_output.put_line('to_set: ' || to_set);
r := UTL_RAW.TRANSLATE(sr, from_set, to_set);
dbms_output.put_line('source raw: ' || sr);
dbms_output.put_line('return raw: ' || r);
end;
/
The result is as follows:
from_set: 12AA34
to_set: CD
source raw: 1236567812125612344434341234567890ABAA1234
return raw: CD365678CDCD56CD44CD567890ABCD
PL/SQL procedure successfully completed.
The inputs and output are presented in the following, for purposes of this discussion, in a way that gives a clearer indication of the functionality.
from_set: 12 AA 34
to_set: CD
source raw: 12 365678 12 12 56 12 34 44 34 34 12 34 567890AB AA 12 34
return raw: CD 365678 CD CD 56 CD 44 CD 567890AB CD
Example 2: In this example, run in ttIsql
, the from_set
is x'12AA12'
and the to_set
is x'CDABEF'
. Wherever '12'
appears in the input RAW it is replaced by 'CD'
in the result. Wherever 'AA'
appears in the input it is replaced by 'AB'
in the result. The second '12'
in from_set
is ignored, and therefore the corresponding byte in to_set
is ignored as well.
declare
sr raw(32767);
from_set raw(32767);
to_set raw(32767);
r raw(32767);
begin
sr := hextoraw('1236567812125612344434341234567890ABAA1234');
from_set := hextoraw('12AA12');
to_set := hextoraw('CDABEF');
dbms_output.put_line('from_set: ' || from_set);
dbms_output.put_line('to_set: ' || to_set);
r := UTL_RAW.TRANSLATE(sr, from_set, to_set);
dbms_output.put_line('source raw: ' || sr);
dbms_output.put_line('return raw: ' || r);
end;
/
The result is as follows. Note this is the same behavior as for TRANSLITERATE
with the same input RAW
, from_set
, and to_set
, as shown in Examples in the TRANSLITERATE
section.
from_set: 12AA12
to_set: CDABEF
source raw: 1236567812125612344434341234567890ABAA1234
return raw: CD365678CDCD56CD34443434CD34567890ABABCD34
PL/SQL procedure successfully completed.
TRANSLITERATE Function
This function performs a byte-by-byte transliteration of a RAW
value, given an input set of bytes, a set of bytes to search for and convert from in the input bytes, and a set of corresponding bytes to convert to.
Whenever a byte in the specified from_set
is found in the input RAW
value, it is converted to the corresponding byte in the to_set
for the output RAW
value, or it is converted to the specified "padding" byte if there is no corresponding byte in to_set
. Any bytes in the input RAW
value that do not appear in from_set
are copied as-is to the output RAW
value.
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;
Note:
Be aware that to_set
and from_set
are reversed in the calling sequence compared to TRANSLATE
.
Parameters
Table 13-25 TRANSLITERATE Function Parameters
Parameter | Description |
---|---|
|
|
|
Where a |
|
Where found, they are converted in the result. The default is |
|
A "padding" byte used as the conversion value for any byte in the source byte-string for which there is a matching byte in |
Return Value
RAW
value with the converted byte-string
Usage Notes
-
If
to_set
is shorter thanfrom_set
, the extrafrom_set
bytes have no corresponding conversion bytes. Bytes from the inputRAW
value that match any suchfrom_set
bytes are converted in the result to thepad
byte instead. -
If
to_set
is longer thanfrom_set
, the extrato_set
bytes are ignored. -
If a byte value is repeated in
from_set
, the repeated occurrence is ignored.
Note:
Differences from TRANSLATE
:
-
The
to_set
parameter comes before thefrom_set
parameter in the calling sequence. -
Bytes from the source byte-string that appear in
from_set
but have no corresponding values into_set
are replaced bypad
in the result. -
The resulting
RAW
value always has the same length as the inputRAW
value.
Note that TRANSLATE
and TRANSLITERATE
differ in functionality only when to_set
has fewer bytes than from_set
.
Exceptions
VALUE_ERROR
occurs if the source byte-string is NULL
or zero-length.
Examples
Example 1: In this example, run in ttIsql
, the from_set
is x'12AA34'
and the to_set
is x'CD'
. Wherever '12'
appears in the input RAW
value it is replaced by 'CD'
in the result. Wherever 'AA'
or '34'
appears in the input RAW
value, because there are no corresponding bytes in to_set
, those bytes are replaced by the pad
byte, which is not specified and therefore defaults to x'00'
.
You can compare this to Examples in the TRANSLATE
section to see how the functions differ.
declare
sr raw(32767);
from_set raw(32767);
to_set raw(32767);
r raw(32767);
begin
sr := hextoraw('1236567812125612344434341234567890ABAA1234');
from_set := hextoraw('12AA34');
to_set := hextoraw('CD');
dbms_output.put_line('from_set: ' || from_set);
dbms_output.put_line('to_set: ' || to_set);
r := UTL_RAW.TRANSLITERATE(sr, to_set, from_set);
dbms_output.put_line('source raw: ' || sr);
dbms_output.put_line('return raw: ' || r);
end;
/
The result is as follows.
from_set: 12AA34
to_set: CD
source raw: 1236567812125612344434341234567890ABAA1234
return raw: CD365678CDCD56CD00440000CD00567890AB00CD00
PL/SQL procedure successfully completed.
The inputs and output are presented in the following, for purposes of this discussion, in a way that gives a clearer indication of the functionality.
from_set: 12 AA 34
to_set: CD
source raw: 12 365678 12 12 56 12 34 44 34 34 12 34 567890AB AA 12 34
return raw: CD 365678 CD CD 56 CD 00 44 00 00 CD 00 567890AB 00 CD 00
Example 2: This example, run in ttIsql
, is the same as the preceding example, except pad
is specified to be x'FF'
.
declare
sr raw(32767);
from_set raw(32767);
to_set raw(32767);
pad raw(32767);
r raw(32767);
begin
sr := hextoraw('1236567812125612344434341234567890ABAA1234');
from_set := hextoraw('12AA34');
to_set := hextoraw('CD');
pad := hextoraw('FF');
dbms_output.put_line('from_set: ' || from_set);
dbms_output.put_line('to_set: ' || to_set);
r := UTL_RAW.TRANSLITERATE(sr, to_set, from_set, pad);
dbms_output.put_line('source raw: ' || sr);
dbms_output.put_line('return raw: ' || r);
end;
/
The result is as follows. 'AA'
and '34'
are replaced by 'FF'
instead of '00'
.
from_set: 12AA34
to_set: CD
source raw: 1236567812125612344434341234567890ABAA1234
return raw: CD365678CDCD56CDFF44FFFFCDFF567890ABFFCDFF
PL/SQL procedure successfully completed.
Example 3: In this example, run in ttIsql
, the from_set
is x'12AA12'
and the to_set
is x'CDABEF'
. Wherever '12'
appears in the input RAW
value it is replaced by 'CD'
in the result. Wherever 'AA'
appears in the input it is replaced by 'AB'
in the result. The second '12'
in from_set
is ignored, and therefore the corresponding byte in to_set
is ignored as well.
declare
sr raw(32767);
from_set raw(32767);
to_set raw(32767);
r raw(32767);
begin
sr := hextoraw('1236567812125612344434341234567890ABAA1234');
from_set := hextoraw('12AA12');
to_set := hextoraw('CDABEF');
dbms_output.put_line('from_set: ' || from_set);
dbms_output.put_line('to_set: ' || to_set);
r := UTL_RAW.TRANSLITERATE(sr, to_set, from_set);
dbms_output.put_line('source raw: ' || sr);
dbms_output.put_line('return raw: ' || r);
end;
/
The result is as follows. Note this is the same behavior as for TRANSLATE
with the same input RAW
, from_set
, and to_set
, as shown in Examples in the TRANSLATE
section.
from_set: 12AA12
to_set: CDABEF
source raw: 1236567812125612344434341234567890ABAA1234
return raw: CD365678CDCD56CD34443434CD34567890ABABCD34
PL/SQL procedure successfully completed.
Example 4: In this example, run in ttIsql
, from_set
and to_set
are not specified.
declare
sr raw(32767);
r raw(32767);
begin
sr := hextoraw('1236567812125612344434341234567890ABAA1234');
r := UTL_RAW.TRANSLITERATE(sr);
dbms_output.put_line('source raw: ' || sr);
dbms_output.put_line('return raw: ' || r);
end;
/
The result is as follows. According to the from_set
and to_set
defaults, all bytes are replaced by x'00'
.
source raw: 1236567812125612344434341234567890ABAA1234
return raw: 000000000000000000000000000000000000000000
PL/SQL procedure successfully completed.
XRANGE Function
This function returns a RAW
value containing the succession of one-byte encodings beginning and ending with the specified byte-codes. The specified byte-codes must be single-byte RAW
values.
If the start_byte
value is greater than the end_byte
value, the succession of resulting bytes begins with start_byte
, wraps through x'FF'
back to x'00'
, then ends at end_byte
.
Syntax
UTL_RAW.XRANGE (
start_byte IN RAW DEFAULT NULL,
end_byte IN RAW DEFAULT NULL)
RETURN RAW;
Parameters
Table 13-26 XRANGE Function Parameters
Parameters | Description |
---|---|
|
Beginning byte-code value for resulting sequence (default |
|
Ending byte-code value for resulting sequence (default |
Return Value
RAW
value containing the succession of one-byte encodings
Examples
The following three examples, run in ttIsql
, show the results where start_byte
is less than end_byte
, start_byte
is greater than end_byte
, and default values are used.
Command> declare
r raw(32767);
s raw(32767);
e raw(32767);
begin
s := hextoraw('1');
e := hextoraw('A');
r := utl_raw.xrange(s,e);
dbms_output.put_line(r);
end;
/
0102030405060708090A
PL/SQL procedure successfully completed.
Command> declare
r raw(32767);
s raw(32767);
e raw(32767);
begin
s := hextoraw('EE');
e := hextoraw('A');
r := utl_raw.xrange(s,e);
dbms_output.put_line(r);
end;
/
EEEFF0F1F2F3F4F5F6F7F8F9FAFBFCFDFEFF000102030405060708090A
PL/SQL procedure successfully completed.
Command> declare
r raw(32767);
begin
r := utl_raw.xrange();
dbms_output.put_line(r);
end;
/
000102030405060708090A0B0C0D0E0F101112131415161718191A1B1C1D1E1F2021222324252627
28292A2B2C2D2E2F303132333435363738393A3B3C3D3E3F404142434445464748494A4B4C4D4E4F
505152535455565758595A5B5C5D5E5F606162636465666768696A6B6C6D6E6F7071727374757677
78797A7B7C7D7E7F808182838485868788898A8B8C8D8E8F909192939495969798999A9B9C9D9E9F
A0A1A2A3A4A5A6A7A8A9AAABACADAEAFB0B1B2B3B4B5B6B7B8B9BABBBCBDBEBFC0C1C2C3C4C5C6C7
C8C9CACBCCCDCECFD0D1D2D3D4D5D6D7D8D9DADBDCDDDEDFE0E1E2E3E4E5E6E7E8E9EAEBECEDEEEF
F0F1F2F3F4F5F6F7F8F9FAFBFCFDFEFF
PL/SQL procedure successfully completed.