Forum Stats

  • 3,874,171 Users
  • 2,266,675 Discussions
  • 7,911,750 Comments

Discussions

How to compile a UTF-8 .sql file that contains Unicode characters

ToddBarry
ToddBarry Member Posts: 18 Blue Ribbon

We have a UTF-8 file containing the code for a PL/SQL package, and the code contains some diacritical Unicode characters.

When compiling with SQLcl, the package compiles successfully but those characters get corrupted within the data dictionary (and the code throws an exception when run because the corruption makes the size of a variable too large).

How can this file compile correctly (no error, no corruption) with SQLcl?

Here's an example of the code in the .sql file:

CSF_A constant varchar2(6) := 'ÀÁÂÃÄÅ';

Answers

  • User_3ABCE
    User_3ABCE Member Posts: 192 Silver Badge

    If the file in UTF8 is set to NLS_LANG client in AL32UTF8, the database must have a compatible charset that supports the characters you use.

    In windows cmd.exe set code page chcp 65001. In SQLcl set encoding utf-8. The file must not contain a BOM.

    [email protected]:~> echo "select 'ÀÁÂÃÄÅ' dia from dual;" > script.utf8
    
    [email protected]:~> od -t x1 script.utf8
    0000000 73 65 6c 65 63 74 20 27 c3 80 c3 81 c3 82 c3 83
    0000020 c3 84 c3 85 27 20 64 69 61 20 66 72 6f 6d 20 64
    0000040 75 61 6c 3b 0a
    0000045
    
    [email protected]:~> sql /
    SQLcl: Release 21.2 Production on Fri Nov 11 10:39:37 2022
    Copyright (c) 1982, 2022, Oracle.  All rights reserved.
    
    SQL> @script.utf8
          DIA
    _________
    ÀÁÂÃÄÅ    
    
    SQL> c/'ÀÁÂÃÄÅ'/dump('ÀÁÂÃÄÅ',1016)/
      1* select dump('ÀÁÂÃÄÅ',1016) dia from dual;
    SQL> /
    
                                                                            DIA
    ___________________________________________________________________________
    Typ=96 Len=12 CharacterSet=AL32UTF8: c3,80,c3,81,c3,82,c3,83,c3,84,c3,85