我和我的团队最近在迁移oracle到openGauss（postgresql）时, 发现有一些存储过程中使用了加密函数，其中有一些涉及到编码的package 如utl_i18、utl_raw、utl_encode，对一些明文数值进行raw或base64编码，这里记录一下oracle到opengauss后对应的函数实现, 基本也适用于postgresql，下一篇会记录加密函数。
cast_to_raw cast_to_varchar2
oracle
FUNCTION CAST_TO_RAW RETURNS RAW Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- C VARCHAR2 IN FUNCTION CAST_TO_VARCHAR2 RETURNS VARCHAR2 Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- R RAW IN --转换RAW SQL> select utl_raw.cast_to_raw('abc'); UTL_RAW.CAST_TO_RAW('ABC') -------------------------------------------------------------------- 616263 -- 逆转RAW SQL> select utl_raw.cast_to_varchar2('616263'); UTL_RAW.CAST_TO_VARCHAR2('616263') ---------------------------------------------------------------------- abc
opengauss
--转换RAW openGauss=# \df encode List of functions Schema | Name | Result data type | Argument data types | Type | fencedmode | propackage | prokind ------------+--------+------------------+---------------------+--------+------------+------------+--------- pg_catalog | encode | text | bytea, text | normal | f | f | f (1 row) openGauss=# select encode('abc','hex'); encode -------- 616263 (1 row) openGauss=# \df rawtohex List of functions Schema | Name | Result data type | Argument data types | Type | fencedmode | propackage | prokind ------------+----------+------------------+---------------------+--------+------------+------------+--------- pg_catalog | rawtohex | text | raw | normal | f | f | f pg_catalog | rawtohex | text | text | normal | f | f | f (2 rows) openGauss=# select rawtohex('abc'); rawtohex ---------- 616263 (1 row) openGauss=# select rawtohex('abc')::raw; rawtohex ---------- 616263 -- 逆转RAW openGauss=# \df rawsend List of functions Schema | Name | Result data type | Argument data types | Type | fencedmode | propackage | prokind ------------+---------+------------------+---------------------+--------+------------+------------+--------- pg_catalog | rawsend | bytea | raw | normal | f | f | f (1 row) openGauss=# \df convert_from List of functions Schema | Name | Result data type | Argument data types | Type | fencedmode | propackage | prokind ------------+--------------+------------------+---------------------+--------+------------+------------+--------- pg_catalog | convert_from | text | bytea, name | normal | f | f | f (1 row) openGauss=# \df pg_encoding_to_char List of functions Schema | Name | Result data type | Argument data types | Type | fencedmode | propackage | prokind ------------+---------------------+------------------+---------------------+--------+------------+------------+--------- pg_catalog | pg_encoding_to_char | name | integer | normal | f | f | f (1 row) openGauss=# select encoding from pg_database where datname=current_database(); encoding ---------- 7 (1 row) openGauss=# select encoding , pg_encoding_to_char(encoding) as encoding from pg_database where datname=current_database(); encoding | encoding ----------+---------- 7 | UTF8 (1 row) openGauss=# SELECT convert_from(rawsend('616263'),'UTF8') ; convert_from -------------- abc (1 row)
Note：
在opengauss 的A for oracle兼容模式的decode不是postgresql中的解码，而是雷同oracle中的decode,case wen then函数。
utl_encode.base64_encode utl_encode.base64_decode
oracle
---编码 SQL> desc utl_encode FUNCTION BASE64_DECODE RETURNS RAW Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- R RAW IN FUNCTION BASE64_ENCODE RETURNS RAW Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- R RAW IN SQL> @printtab "select utl_raw.cast_to_raw(''今天访问www.anbob.com了吗?'') ev1, utl_encode.base64_encode(utl_raw.cast_to_raw(''今天访问www.anbob.com了吗?'')) ev2, utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(''今天访问www.anbob.com了吗?''))) ev3 , utl_i18n.STRING_TO_RAW(''今天访问www.anbob.com了吗?'',''ZHS16GBK'') ev4 from dual" EV1 : E4BB8AE5A4A9E8AEBFE997AE7777772E616E626F622E636F6DE4BA86E590973F EV2 : 354C754B35615370364B362F365A6575643364334C6D4675596D39694C6D4E7662655336687557516C7A383D EV3 : 5LuK5aSp6K6/6Zeud3d3LmFuYm9iLmNvbeS6huWQlz8= EV4 : E4BB8AE5A4A9E8AEBFE997AE7777772E616E626F622E636F6DE4BA86E590973F ----------------- PL/SQL procedure successfully completed. --解码 SQL> @printtab "select utl_raw.cast_to_raw(''5LuK5aSp6K6/6Zeud3d3LmFuYm9iLmNvbeS6huWQlz8='') v1, utl_encode.base64_decode(utl_raw.cast_to_raw(''5LuK5aSp6K6/6Zeud3d3LmFuYm9iLmNvbeS6huWQlz8='')) v2, utl_raw.cast_to_varchar2(utl_encode.base64_decode(utl_raw.cast_to_raw(''5LuK5aSp6K6/6Zeud3d3LmFuYm9iLmNvbeS6huWQlz8=''))) v3 from dual"; V1 : 354C754B35615370364B362F365A6575643364334C6D4675596D39694C6D4E7662655336687557516C7A383D V2 : E4BB8AE5A4A9E8AEBFE997AE7777772E616E626F622E636F6DE4BA86E590973F V3 : 今天访问www.anbob.com了吗? ----------------- PL/SQL procedure successfully completed.
openGauss
---编码 openGauss=# \x Expanded display is on. openGauss=# SELECT rawtohex('今天访问www.anbob.com了吗?')::raw, encode(rawsend(rawtohex('今天访问www.anbob.com了吗?')::raw),'base64'), rawtohex(encode(rawsend(rawtohex('今天访问www.anbob.com了吗?')::raw),'base64'))::RAW, convert_from(rawsend(rawtohex(encode(rawsend(rawtohex('今天访问www.anbob.com了吗?')::raw),'base64'))::RAW),'UTF8') ; -[ RECORD 1 ]+----------------------------------------------------------------------------------------- rawtohex | E4BB8AE5A4A9E8AEBFE997AE7777772E616E626F622E636F6DE4BA86E590973F encode | 5LuK5aSp6K6/6Zeud3d3LmFuYm9iLmNvbeS6huWQlz8= rawtohex | 354C754B35615370364B362F365A6575643364334C6D4675596D39694C6D4E7662655336687557516C7A383D convert_from | 5LuK5aSp6K6/6Zeud3d3LmFuYm9iLmNvbeS6huWQlz8= --解码 openGauss=# SELECT rawtohex('5LuK5aSp6K6/6Zeud3d3LmFuYm9iLmNvbeS6huWQlz8='), encode(rawsend(rawtohex('5LuK5aSp6K6/6Zeud3d3LmFuYm9iLmNvbeS6huWQlz8=')::RAW),'escape'), decode('5LuK5aSp6K6/6Zeud3d3LmFuYm9iLmNvbeS6huWQlz8=','base64')::BYTEA, rawout(decode('5LuK5aSp6K6/6Zeud3d3LmFuYm9iLmNvbeS6huWQlz8=','base64')::BYTEA)::TEXT::RAW, convert_from(rawsend(rawout(decode('5LuK5aSp6K6/6Zeud3d3LmFuYm9iLmNvbeS6huWQlz8=','base64')::BYTEA)::TEXT::RAW),'UTF8'); -[ RECORD 1 ]+----------------------------------------------------------------------------------------- rawtohex | 354c754b35615370364b362f365a6575643364334c6d4675596d39694c6d4e7662655336687557516c7a383d encode | 5LuK5aSp6K6/6Zeud3d3LmFuYm9iLmNvbeS6huWQlz8= decode | \xe4bb8ae5a4a9e8aebfe997ae7777772e616e626f622e636f6de4ba86e590973f rawout | E4BB8AE5A4A9E8AEBFE997AE7777772E616E626F622E636F6DE4BA86E590973F convert_from | 今天访问www.anbob.com了吗?
— update 20260528 —
HighgoDB (oracle mode)
瀚高数据库是基于postgresql的，但是做了oracle兼容，如raw，在postgresql中并没有，而是用bytea类型，在highgo中可以创建raw类型，这里简单测试
highgo=# \c test
hgdb-client-V9.0.5
You are now connected to database "test" as user "highgo".
test=# create table traw(val raw(100));
CREATE TABLE
test=# \d traw
Table "public.traw"
Column | Type | Collation | Nullable | Default
--------+----------+-----------+----------+---------
val | raw(100) | | |
test=# \df *raw*
List of functions
Schema | Name | Result data type | Argument data types | Type
------------+-----------------------------+------------------+-------------------------+------
pg_catalog | raw_array_subscript_handler | internal | internal | func
sys | hextoraw | bytea | text | func
sys | orabytea_to_raw_with_typmod | bytea | bytea, integer, boolean | func
sys | orarawtypmodin | integer | cstring[] | func
sys | orarawtypmodout | cstring | integer | func
(5 rows)
test=# select encode('anbob','hex') ;
encode
------------
616e626f62
(1 row)
test=# insert into traw values( hextoraw('616E626F62'));
INSERT 0 1
test=# select * from traw;
val
--------------
\x616e626f62
(1 row)
test=# show bytea_output ;
bytea_output
--------------
hex
(1 row)
test=# set bytea_output='escape';
SET
test=# select * from traw;
val
-------
anbob
(1 row)
test=# select encoding , pg_encoding_to_char(encoding) as encoding from pg_database where datname=current_database();
encoding | encoding
----------+----------
6 | UTF8
(1 row)
test=# select convert_from(val,'utf8') from traw;
convert_from
--------------
anbob
(1 row)
test=# select encode(val,'hex') from traw;
encode
------------
616e626f62
(1 row)
test=# select encode(val,'escape') from traw;
encode
--------
anbob
(1 row)
test=# select * from traw;
val
--------------
\x616e626f62
(1 row)
test=# update traw set val='616E626F62';
UPDATE 1
test=# select * from traw;
val
--------------
\x616e626f62
(1 row)
-- 二进制并不存在大小写区别
test=# select encode(val,'escape') from traw;
encode
--------
anbob
(1 row)
— enjoy —