2 Replies Latest reply: Apr 19, 2013 6:28 AM by DK2010 RSS

    spooling flat files in unicode database

    jymarkusg
      Hi!

      My environment is like the following:
      - oracle 11.2.0.2 EE
      - CHARACTERSET: AL32UTF8

      TESTCASE PREPARE:

      CREATE TABLE THU
      (
      C1 NUMBER,
      C2 VARCHAR2(5 CHAR),
      C3 VARCHAR2(10 CHAR),
      C4 NUMBER
      );

      REM INSERTING into THU
      SET DEFINE OFF;
      Insert into THU (C1,C2,C3,C4) values ('1','qq','qq test qq','1');
      Insert into THU (C1,C2,C3,C4) values ('2','éáő','qq éáő qq','2');
      Insert into THU (C1,C2,C3,C4) values ('3','őúó','qq ÓÚŰÁ qq','3');
      Insert into THU (C1,C2,C3,C4) values ('4','őóüö','qq ŐÚŰ qq','4');
      Insert into THU (C1,C2,C3,C4) values ('5','ÜÖü','qq ŰŰŰŰ qq','5');
      commit;


      TESTCASE 1:
      ========

      export NLS_LANG=HUNGARIAN_HUNGARY.EE8ISO8859P2

      sqlplus hu/hu

      set linesize 2000

      select * from thu;

      SQL> r
      1* select * from thu

      C1 C2 C3 C4
      ---------- ----- ---------- ----------
      1 qq qq test qq 1
      2 éáő qq éáő qq 2
      3 őúó qq ÓÚŰÁ qq 3
      4 őóüö qq ŐÚŰ qq 4
      5 ÜÖü qq ŰŰŰŰ qq 5


      TESTCASE 2:
      ========

      export NLS_LANG=HUNGARIAN_HUNGARY.AL32UTF8

      sqlplus hu/hu

      set linesize 2000

      select * from thu;

      SQL> select * from thu;

      C1 C2 C3 C4
      ---------- -------------------- ---------------------------------------- ----------
      1 qq qq test qq 1
      2 éáő qq éáő qq 2
      3 őúó qq ÓÚŰÁ qq 3
      4 őóüö qq ŐÚŰ qq 4
      5 ÜÖü qq ŰŰŰŰ qq 5


      As you can see sqlplus fills up each character with 4 bytes in second testcase. This is a problem with spooled flat files which get processed for example with sql loader (fixed positioned). So is the only way for dealing with flat files to set NLS_LANG to an one byte setting? Generally we've the recommendation to use always AL32UTF8 for NLS...

      Has anyone any input for my problem?

      Thanks
      Markus