Skip to content

PL/SQL character encoding configuration

PL/SQL character encoding configuration is primarily managed by the NLS_LANG environment variable^[600-developer__database__oracle__oracle-record.md]. This parameter defines the language, territory, and character set used by the client application, ensuring that data is correctly interpreted and displayed^[600-developer__database__oracle__oracle-record.md].

Configuration

To prevent garbled text (mojibake) in PL/SQL Developer, the NLS_LANG variable must be configured to match the database encoding before launching the application^[600-developer__database__oracle__oracle-record.md]. For example, setting the variable to AMERICAN_AMERICA.AL32UTF8 supports UTF-8 encoded data^[600-developer__database__oracle__oracle-record.md].

set NLS_LANG=AMERICAN_AMERICA.AL32UTF8
START plsqldev.exe

Verification

To verify the current character encoding settings within the Oracle environment, users can query the NLS parameters^[600-developer__database__oracle__oracle-record.md].

  • Check NLS Parameters: Query v$nls_parameters to see a comprehensive list of NLS settings^[600-developer__database__oracle__oracle-record.md].
  • Check Session Language: Retrieve the specific language setting using userenv('language')^[600-developer__database__oracle__oracle-record.md].
select * from v$nls_parameters;
select userenv('language') from dual;

Date Format Configuration

Character encoding issues often extend to date formats, where display errors can occur^[600-developer__database__oracle__oracle-record.md]. This can be resolved by explicitly setting the date and timestamp formats for the session^[600-developer__database__oracle__oracle-record.md].

alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
alter session set NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SS:FF6';
  • [[Oracle JDBC]]
  • [[NLS Configuration]]
  • Maven

Sources

^[600-developer__database__oracle__oracle-record.md]