首页 » 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)

我要评论

上一篇：

下一篇：