首页 » ORACLE 9i-23ai, PostgreSQL/GaussDB » Migrate oracle to openGauss: cast_to_raw/cast_to_varchar2 & base64_encode/base64_decode functions
Migrate oracle to openGauss: cast_to_raw/cast_to_varchar2 & base64_encode/base64_decode functions
我和我的团队最近在迁移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了吗?
— enjoy —
目前这篇文章还没有评论(Rss)