Hi,
I would like to introduce the ability to send user-friendly error messages to the client from my PL/SQL API exceptions in the local language of the user. For this I plan to use the standard routine "raise_application_error". When I try this, the Unicode message that gets passed into the "raise_application_error" call does not always get displayed properly in the client. I have tested this with SQL*Plus and SQL Developer on a Windows 10 machine and it appears that there is some conversion taking place.
Environment information is as follows:
The database character set is AL32UTF8.
The national character set is AL16UTF16.
The NLS_LANG environment variable of the Windows client is AL32UTF8.
The database version is 19.6.0.0.0 on Linux x86-64.
The code I am using to test is as follows:
DECLARE
h varchar2(100 char) := 'Proszę mi pomóc rozwiązać ten problem Unicode';
BEGIN
SYS.DBMS_OUTPUT.PUT_LINE(h);
SYS.DBMS_STANDARD.RAISE_APPLICATION_ERROR(-20000, h);
END;
/
Upon execution of the above code, I observe the following behaviour:
1) The output message from "raise_application_error" is being converted incorrectly within SQL developer and SQL*Plus.
2) The call to "dbms_output" does display a Unicode string correctly within SQL developer but not SQL*Plus.
3) Using the national data types makes no difference.
Can you help?
Many thanks,
Mark.
There is nothing inherently wrong in the raise_application_error routine that stops this. For example:
But that worked only because
a) I ran "chcp 65001" in this MS-DOS command before I started SQL Plus
b) I saved the file as UTF8 and ran that (simply cut/paste doesn't work because its not UTF encoding by default on my machine)
Bottom line - there's a lot of moving parts that you have to get right here
- the database has to support it (my db is running the default UTF8 characterset)
- the client NLS settings must be correct
- the client OS settings must be alignment
My guess its the codepage you'll need to change