Oracle UTL_RAW
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Interested in Oracle GoldenGate? Check out the IGGOUG,
Purpose Utilities for working with the RAW data Type
AUTHID DEFINER
Constants
Name Data Type Value
big_endian PLS_INTEGER 1
little_endian PLS_INTEGER 2
machine_endian PLS_INTEGER 3
Dependencies SELECT name FROM dba_dependencies WHERE referenced_name = 'UTL_RAW'
UNION
SELECT referenced_name FROM dba_dependencies WHERE name = 'UTL_RAW';

-- returns 227 rows
Documented Yes
Exceptions
Error Code Reason
ORA-6502 VALUE_ERROR: An arithmetic, conversion, truncation, or size-constraint error. Usually raised by trying to cram a 6 character string into a VARCHAR2(5).
First Available 7.3.4
Security Model Owned by SYS with EXECUTE granted to PUBLIC and the APEX_040200 roles
Source {ORACLE_HOME}/rdbms/admin/utlraw.sql
Subprograms
 
BIT_AND
Perform bitwise logical "and" of the values in raw r1 with raw r2 and return the "anded" result raw utl_raw.bit_and(r1 IN RAW, r2 IN RAW) RETURN RAW;
SELECT utl_raw.bit_and('0102F3', 'F30201')
FROM dual;
 
BIT_COMPLEMENT
Perform bitwise logical "complement" of the values in raw and return the "complement'ed" result raw utl_raw.bit_complement(r IN RAW) RETURN RAW;
SELECT utl_raw.bit_complement('0102F3')
FROM dual;
 
BIT_OR
Perform bitwise logical "or" of the values in raw r1 with raw r2 and return the "or'd" result raw utl_raw.bit_or(r1 IN RAW, r2 IN RAW) RETURN RAW;
SELECT utl_raw.bit_or('0102F3', 'F30201')
FROM dual;
 
BIT_XOR
Perform bitwise logical "exclusive or" of the values in raw r1 with raw r2 and return the "xor'd" result raw utl_raw.bit_xor(r1 IN RAW, r2 IN RAW) RETURN RAW;
SELECT utl_raw.bit_xor('0102F3', 'F30201')
FROM dual;
 
CAST_FROM_BINARY_DOUBLE
Return the RAW representation of a binary_double value utl_raw.cast_from_binary_double(
n         IN BINARY_DOUBLE,
endianess IN PLS_INTEGER DEFAULT 1)
RETURN RAW;
SELECT utl_raw.cast_from_binary_double(123.45)
FROM dual;
 
CAST_FROM_BINARY_FLOAT
Return the RAW representation of a binary_float value utl_raw.cast_from_binary_float(
n         IN BINARY_FLOAT,
endianess IN PLS_INTEGER DEFAULT 1)
RETURN RAW;
SELECT utl_raw.cast_from_binary_float(123.45)
FROM dual;
 
CAST_FROM_BINARY_INTEGER
Return the RAW representation of a binary_integer value utl_raw.cast_from_binary_integer(
n         IN BINARY_INTEGER,
endianess IN PLS_INTEGER DEFAULT 1)
RETURN RAW;
SELECT utl_raw.cast_from_binary_integer(100)
FROM dual;
 
CAST_FROM_NUMBER
Returns the binary representation of a NUMBER in RAW utl_raw.cast_from_number(n IN NUMBER) RETURN RAW;
SELECT utl_raw.cast_from_number(100)
FROM dual;
 
CAST_TO_BINARY_DOUBLE
Perform a casting of the binary representation of the raw into a binary_double cast_to_binary_double(
r         IN RAW,
endianess IN PLS_INTEGER DEFAULT 1)
RETURN BINARY_DOUBLE;
SELECT utl_raw.cast_to_binary_double('405EDCCCCCCCCCCD')
FROM dual;
 
CAST_TO_BINARY_FLOAT
Perform a casting of the binary representation of the raw into a binary_float utl_raw.cast_to_binary_float(
n         IN BINARY_FLOAT,
endianess IN PLS_INTEGER DEFAULT 1)
RETURN RAW;
SELECT utl_raw.cast_to_binary_float('42F6E666')
FROM dual;
 
CAST_TO_BINARY_INTEGER
Perform a casting of the binary representation of the raw into a binary integer utl_raw.cast_to_binary_integer(
r         IN RAW,
endianess IN PLS_INTEGER DEFAULT 1)
RETURN BINARY_INTEGER;
SELECT utl_raw.cast_to_binary_integer('00000064')
FROM dual;
 
CAST_TO_NUMBER
Perform a casting of the binary representation of the number (in RAW) into a NUMBER utl_raw.cast_to_number(r IN RAW) RETURN NUMBER;
SELECT utl_raw.cast_to_number('C202')
FROM dual;
 
CAST_TO_NVARCHAR2
Converts a RAW represented using n data bytes into NVARCHAR2 with n data bytes utl_raw.cast_to_nvarchar2(r IN RAW) RETURN NVARCHAR2;
set serveroutput on

BEGIN
  FOR i IN 100..200 LOOP
    dbms_output.put_line(utl_raw.cast_to_nvarchar2(TO_CHAR(i)));
  END LOOP;
END;
/
 
CAST_TO_RAW
Converts a VARCHAR2 represented using n data bytes into a RAW with n data bytes utl_raw.cast_to_raw(r IN VARCHAR2) RETURN RAW;
SELECT utl_raw.cast_to_raw('ABC')
FROM dual;
 
CAST_TO_VARCHAR2
To extract a substring from a BLOB using a PL/SQL program use dbms_lob.substr(). The problem is that it returns a string in hexadecimal characters. CAST_TO_VARCHAR2 turns the hexadecimal string into readable ascii format. utl_raw.cast_to_nvarchar2(r IN RAW) RETURN VARCHAR2;
set serveroutput on

BEGIN
  FOR i IN 100..200 LOOP
    dbms_output.put_line(utl_raw.cast_to_varchar2(TO_CHAR(i)));
  END LOOP;
END;
/
Demo 2 SELECT rawtohex('!4!4!4!4!4!4') FROM dual;

SELECT utl_raw.cast_to_varchar2('213421342134213421342134') FROM dual;
 
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 utl_raw.compare(r1 IN RAW, r2 IN RAW, pad IN RAW DEFAULT NULL) RETURN NUMBER;
SELECT utl_raw.compare(utl_raw.cast_to_raw('ABC'), utl_raw.cast_to_raw('ACC'))
FROM dual;
 
CONCAT
Concatenate a set of 12 raws into a single raw (up to 32K) 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;
SELECT utl_raw.concat('A','41','B','42')
FROM dual;
 
CONVERT
Convert raw from one character to a different character set and return the resulting raw utl_raw.convert(
r            IN RAW,
to_charset   IN VARCHAR2,
from_charset IN VARCHAR2) RETURN RAW;
DECLARE
 fr_charset CONSTANT VARCHAR2(30) :=
   SUBSTR(SYS_CONTEXT('USERENV', 'LANGUAGE'),
  INSTR(SYS_CONTEXT('USERENV', 'LANGUAGE'),'.')+1);

 to_charset VARCHAR2(30) := 'TR8EBCDIC1026S';
 rawvar     RAW(100);
BEGIN
  dbms_output.put_line(fr_charset);
  dbms_output.put_line(to_charset);

  rawvar := utl_raw.convert(UTL_RAW.CAST_TO_RAW('Morgan'),
    'AMERICAN_AMERICA.'||to_charset,
    'AMERICAN_AMERICA.'||fr_charset);

  dbms_output.put_line(rawvar);
END;
/
 
COPIES
Return n copies of r concatenated together utl_raw.copies(r IN RAW, n IN NUMBER) RETURN RAW;
SELECT utl_raw.copies('A', 6)
FROM dual;
 
LENGTH
Return the length in bytes of a raw utl_raw.length(r IN RAW) RETURN NUMBER;
SELECT utl_raw.length('ABC')
FROM dual;
 
OVERLAY
Overlay the specified portion of target raw with overlay raw, starting from byte position pos of target and proceeding for len bytes 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;
SELECT utl_raw.overlay('1', 'AAABBBCCC', 4)
FROM dual;
 
REVERSE
Reverse a byte sequence in raw r from end to end utl_raw.reverse(r IN RAW) RETURN RAW;
SELECT utl_raw.reverse('123')
FROM dual;
col x format a12

WITH q AS (
SELECT HEXTORAW('93D90428') r FROM dual
UNION ALL
SELECT HEXTORAW('93D90428') r from dual)
SELECT r, '0x' || rawtohex(utl_raw.reverse(r)) x
FROM q;
 
SUBSTR
Return a substring portion of raw r beginning at pos for len bytes utl_raw.substr(r IN RAW,
pos IN BINARY_INTEGER,
len IN BINARY_INTEGER DEFAULT NULL)
RETURN RAW;
set serveroutput on

DECLARE
 tmp  VARCHAR2(250) := '';
 vraw RAW(200) := utl_raw.cast_to_raw('ABCDEABCDEABCDE');
BEGIN
  FOR i IN 1 .. 15 LOOP
    tmp := tmp|| '0x' || utl_raw.substr(vraw, i, 1) || ' ';
    dbms_output.put_line(tmp);
  END LOOP;
END;
/
 
TRANSLATE
Translate the bytes in the input r raw according to the bytes in the translation raws, from_set and to_set utl_raw.translate(
r        IN RAW,
from_set IN RAW,
to_set   IN RAW)
RETURN RAW;
CREATE OR REPLACE FUNCTION trans_demo(pin IN VARCHAR2)
RETURN VARCHAR2 IS
 r_in  RAW(2000);
 r_out RAW(2000);
 r_ul  RAW(64);
 r_lu  RAW(64);
BEGIN
  r_in := utl_raw.cast_to_raw(pin);
  r_ul := utl_raw.cast_to_raw('ABCDEFabcdef');
  r_lu := utl_raw.cast_to_raw('abcdefABCDEF');

  r_out := utl_raw.translate(r_in , r_ul, r_lu);

  return(utl_raw.cast_to_varchar2(r_out));
END trans_demo;
/

SELECT trans_demo('FaDe') FROM dual;
SELECT trans_demo('FAde') FROM dual;
 
TRANSLITERATE
Transliterate the bytes in the input r raw according to the bytes in the transliteration raws, from_set and to_set 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;
CREATE OR REPLACE FUNCTION tl_demo(pin IN VARCHAR2)
RETURN VARCHAR2 AUTHID DEFINER IS
 r_in  RAW(2000);
 r_out RAW(2000);
 r_up  RAW(64);
 r_lo  RAW(64);
 r_un  RAW(64) := utl_raw.cast_to_raw('_');
BEGIN
  r_in := utl_raw.cast_to_raw(pin);
  r_up := utl_raw.cast_to_raw('ABCDEF. ');
  r_lo := utl_raw.cast_to_raw('abcdef');

  r_out := utl_raw.transliterate(r_in , r_lo, r_up, r_un);

  return(utl_raw.cast_to_varchar2(r_out));
END tl_demo;
/

SELECT tl_demo('AB C.D') FROM dual;
 
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 utl_raw.xrange(
start_byte IN RAW DEFAULT NULL,
end_byte   IN RAW DEFAULT NULL)
RETURN RAW;
SELECT utl_raw.xrange(utl_raw.cast_to_raw('A'), utl_raw.cast_to_raw('Z'))
FROM dual;

SELECT utl_raw.xrange(utl_raw.cast_to_raw('0A'), utl_raw.cast_to_raw('Z'))
FROM dual;

Related Topics
Conversion Functions
DBMS_LOB
Packages

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved