Skip to content
Home » Oracle Database » Garbled Text in PL/SQL Developer

Garbled Text in PL/SQL Developer

Garbled Text

We saw unreadable result in PL/SQL Developer after we INSERT INTO 3 rows and SELECT the table.

Garbled data returned in PL/SQL Developer
Garbled data returned in PL/SQL Developer

We checked the language of this session by querying USERENV('LANGUAGE').

USERENV('LANGUAGE')
USERENV('LANGUAGE')

It's AMERICAN_AMERICA.AL32UTF8 and seems no problem.

Solution

This is because the default language in your OS adopted by the tool is incompatible with multi-byte character sets, therefore unreadable text was inserted into rows.

To override the character set of default language in order to handle multi-byte text properly, we set NLS_LANG environment variable in Windows for Oracle to notify the tool to use the right character set.

Environment Variable - NLS_LANG - AL32UTF8 only
Environment Variable - NLS_LANG - AL32UTF8 only

Here we set only character set in order to correctly encode multi-byte strings as recognized ones.

.AL32UTF8

We re-open PL/SQL developer program, initiate a new session and then INSERT INTO another 3 rows.

Let's check the result.

Correct encoded data returned in PL/SQL Developer
Correct encoded data returned in PL/SQL Developer

The last 3 rows are correctly displayed and readable.

As for the first 3 messed-up rows, you may consider to correct them into readable text, or delete them.

Leave a Reply

Your email address will not be published. Required fields are marked *