The whpg-utl-raw extension extends orafce with additional Oracle compatibility features for WarehousePG (WHPG). It implements the UTL_RAW package and the RAW data type.
Downloading, installing, and loading the extension
Refer to Downloading and installing an extension for installation and setup instructions. The package name is edb-whpg7-utl-raw.
Once installed, enable orafce and whpg_utl_raw in each database where you want to use the UTL_RAW functions:
CREATE EXTENSION orafce; CREATE EXTENSION whpg_utl_raw;
Creating the extension also creates the raw domain (CREATE DOMAIN public.raw AS bytea;) that the UTL_RAW functions use.
Using the UTL_RAW functions
Use the UTL_RAW functions to store and manipulate binary data with the RAW data type. All UTL_RAW functions live in the utl_raw schema.
| Function | Returns | Description |
|---|---|---|
utl_raw.cast_to_raw(c TEXT) | RAW | Reinterpret a string's bytes as RAW |
utl_raw.cast_to_varchar2(r RAW) | TEXT | Reinterpret RAW bytes as text |
utl_raw.length(r RAW) | INTEGER | Byte length of a RAW value |
utl_raw.substr(r RAW, pos INTEGER [, len INTEGER]) | RAW | Byte substring of a RAW value |
utl_raw.concat(r1 RAW [, r2 RAW, ...]) | RAW | Concatenate RAW values |
utl_raw.convert(r RAW, to_charset TEXT, from_charset TEXT) | RAW | Re-encode RAW data between character sets |
Converting between VARCHAR2 and RAW
Convert a VARCHAR2 value to RAW with cast_to_raw:
SELECT utl_raw.cast_to_raw('hello'); cast_to_raw -------------- \x68656c6c6f (1 row)
Convert a RAW value back to text with cast_to_varchar2:
SELECT utl_raw.cast_to_varchar2(utl_raw.cast_to_raw('hello')); cast_to_varchar2 ------------------ hello (1 row)
Concatenating RAW values
Combine multiple RAW values into one with concat.
SELECT utl_raw.concat('\x61'::raw, '\x62'::raw, '\x63'::raw); concat ---------- \x616263 (1 row)
Converting between character sets
Convert a RAW value from one character set to another with convert.
SELECT utl_raw.convert('abc'::raw, 'GBK', 'UTF-8'); convert ---------- \x616263 (1 row)
abc is plain ASCII, so its byte representation is identical in both character sets and the output doesn't change. Converting non-ASCII text produces a different byte sequence.
Getting the length of a RAW value
Get the length, in bytes, of a RAW value with length.
SELECT utl_raw.length('abc'::raw); length -------- 3 (1 row)
Extracting a substring from a RAW value
Extract a substring of a RAW value with substr. A positive pos counts from the start, and a negative pos counts backward from the end. Omit len to return everything from pos to the end.
SELECT utl_raw.cast_to_varchar2(utl_raw.substr(utl_raw.cast_to_raw('Accounts'), 3, 5)); cast_to_varchar2 ------------------ count (1 row) SELECT utl_raw.cast_to_varchar2(utl_raw.substr(utl_raw.cast_to_raw('Accounts'), -5, 3)); cast_to_varchar2 ------------------ oun (1 row)
These results match Oracle's UTL_RAW.SUBSTR behavior for the same inputs.