PLSQL将NVARCHAR2从BASE64解码为UTF-8 [英] PLSQL decode NVARCHAR2 from BASE64 to UTF-8

问题描述

我有一个数据库，该数据库目前仅以英文存储用户名.

I have a database which stores usernames only in English at the moment.

我想加入 BASE64 & UTF-8 ，以便也以其他语言存储；我想将其存储在NVARCHAR2类型的列中.

I would like to incorporate BASE64 & UTF-8 in order to store in other languages as well; I want to store it in a column of type NVARCHAR2.

数据库过程在BASE64中接收名称，我正在通过UTL_ENCODE.BASE64_DECODE&使用UTL_RAW.CAST_TO_VARCHAR2将字符串转换为VARCHAR2.但是我得到的却是胡言乱语，而不是真实的单词.

The database procedure receives the name in BASE64, I'm decoding it via UTL_ENCODE.BASE64_DECODE & converting the string to VARCHAR2 using UTL_RAW.CAST_TO_VARCHAR2. But I get gibberish back and not the actual word.

例如，我在BASE64中得到'алекс'作为名称.我可以对其进行解码，但是对VARCHAR2/NVARCHAR2的强制转换不会返回该值:我只会变得乱七八糟.

For example I get 'алекс' as the name in BASE64. I'm able to decode it but the cast to VARCHAR2/NVARCHAR2 does not return the value: I get only gibberish.

我正在使用 NLS_CHARACTERSET WE8ISO8859P1

这是我用来解码的代码:

Here is the code I use to decode:

DECLARE 
  lv_OrgUserName VARCHAR2(2000);
  lv_encodedUserName VARCHAR2(2000);
  lv_UserName    VARCHAR2(2000);
BEGIN 

lv_OrgUserName := 'алекс';
lv_encodedUserName := UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(UTL_RAW.CAST_TO_RAW(lv_OrgUserName)));
DBMS_OUTPUT.PUT_LINE (lv_encodedUserName);
lv_UserName := UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_DECODE(UTL_RAW.CAST_TO_RAW (lv_encodedUserName)));
DBMS_OUTPUT.PUT_LINE (lv_UserName);

END;

我该如何克服?

推荐答案

第一个也是最重要的WE8ISO8859P1(西欧8位ISO 8859第1部分，或-ISO8859第1部分)不支持cyryllic字符:
看到此链接: https://en.wikipedia.org/wiki/ISO/IEC_8859-1

因此，如果尝试将类似алекс的字符串存储到 VARCHAR2 变量/列中，则始终会得到a????作为结果.

可能在数据库安装过程中，有人没有考虑cyryllic字符，并选择了错误的代码页.
更好的选择是ISO/IEC 8859-5(第5部分)，请参见以下链接: https: //en.wikipedia.org/wiki/ISO/IEC_8859-5

一种选择是更改此编码-但这并不容易，而且绕过了这个问题.

First and foremost WE8ISO8859P1 (Western European 8-bit ISO 8859 Part 1, or - ISO8859 Part 1) does not support cyryllic characters:
see this link: https://en.wikipedia.org/wiki/ISO/IEC_8859-1

Therefore if you try to store a string like алекс into VARCHAR2 variable/column, you will always get a???? as an outcome.

Probably during the database installation someone has not considered cyryllic characters and has choosen a bad codepage.
A better option is ISO/IEC 8859-5 (part 5), see this link: https://en.wikipedia.org/wiki/ISO/IEC_8859-5

One option is to change this encoding - but this is not easy and it is beyound of this question.

您可以做的是在应用程序中必须支持西里尔字符的所有位置严格使用 NVARCHAR2 数据类型，而不是 VARCHAR2 数据类型.

尽管您需要注意一些问题，但仍然存在一些陷阱:

What you can do is to strictly use NVARCHAR2 datatype instead of VARCHAR2 datatype in all places of your application that must support cyrillic characters.

There are still some pitfalls though you need to be aware of:

• 您不能使用DBMS_OUTPUT包调试代码，因为此包仅支持VARCHAR2数据类型，不支持NVARCHAR
• 您必须在所有文字中使用N'some string'文字(带有N前缀)-> 'алекс'为VARCHAR2数据类型，并且在编码中始终自动转换为'a????'，而n'алекс'为NVARCHAR2数据类型并且不会发生这种转换.

• You cannot use DBMS_OUTPUT package to debug your code, because this package support only VARCHAR2 datatype, it doesn't support NVARCHAR
• you must use N'some string' literals (with N prefix) in all literals --> 'алекс' is of VARCHAR2 datatype and it is always automatically converted to 'a????' in your encoding, while n'алекс' is of NVARCHAR2 datatype and such conversion doesn't occur.

以下代码在版本12c上进行了测试，我使用的是EE8MSWIN1250代码页(它也不支持西里尔字母):

The below code is tested on version 12c, I am using EE8MSWIN1250 code page (it also desn't support cyrillic characters):

select * from nls_database_parameters
where parameter like '%CHARACTERSET%';

PARAMETER                VALUE
-----------------------  ------------
NLS_NCHAR_CHARACTERSET   AL16UTF16
NLS_CHARACTERSET         EE8MSWIN1250

请尝试一下:

CREATE OR REPLACE PACKAGE my_base64 AS
   FUNCTION BASE64_ENCODE( str nvarchar2 ) RETURN varchar2;
   FUNCTION BASE64_DECODE( str varchar2  ) RETURN nvarchar2;
END;
/

CREATE OR REPLACE PACKAGE BODY my_base64 AS

   FUNCTION BASE64_ENCODE( str nvarchar2 ) RETURN varchar2
   IS 
     lv_encodedUserName VARCHAR2(2000);
   BEGIN
    lv_encodedUserName := UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(UTL_RAW.CAST_TO_RAW(str)));
    RETURN lv_encodedUserName;
   END;


   FUNCTION BASE64_DECODE( str  varchar2  ) RETURN nvarchar2
   IS
     lv_UserName    nVARCHAR2(2000);
   BEGIN
      lv_UserName := UTL_RAW.CAST_TO_nVARCHAR2(UTL_ENCODE.BASE64_DECODE(UTL_RAW.CAST_TO_RAW (str)));
      RETURN lv_UserName;
   END;

END;
/

一些例子:

select 'aлекс' As A, n'aлекс' As B from dual;

A     B   
----- -----
a???? aлекс

---

select my_base64.BASE64_ENCODE( n'аaaлекс' ) As aleks from dual;

ALEKS                                                                          
--------------------------------------------------------------------------------
BDAAYQBhBDsENQQ6BEE= 

---

select my_base64.BASE64_DECODE( 'BDAAYQBhBDsENQQ6BEE=' ) as aleks from dual;

ALEKS                                                                          
--------------------------------------------------------------------------------
аaaлекс   

---

select my_base64.BASE64_DECODE( my_base64.BASE64_ENCODE( n'аaaлекс' ) ) as Aleks from dual;

ALEKS                                                                          
--------------------------------------------------------------------------------
аaaлекс  

这篇关于PLSQL将NVARCHAR2从BASE64解码为UTF-8的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！