Forum Stats

  • 3,852,816 Users
  • 2,264,140 Discussions
  • 7,905,151 Comments

Discussions

What encoding should i use...

NikitaR
NikitaR Member Posts: 16 Blue Ribbon

Good day,

I am on win10, rel 1805, i am spooling 11g we8iso8859 data to txt files, as insert statements.

I use powershell to alter the data so i can load to oracle19c, encoded as alt32utf8, for use with apex.

For the life of me, i cannot get cdn french chars to load properly.

Can someone tell me what encoding i should use to load the data?


Pls n thnx

Nikita

Answers

  • Suresh Sangaran-Oracle
    Suresh Sangaran-Oracle Member, Moderator Posts: 325 Employee

    we8iso8859? it has ISO 8859-1 to ISO 8859-15. So which character set is used on the source database?

    ISO 8859-1, ISO 8859-9 and ISO 8859-15 support French characters. Choose the character set appropriate during spool.

    Ref: https://docs.oracle.com/en/database/oracle/oracle-database/19/nlspg/choosing-character-set.html#GUID-CD1C1A9A-9DC7-41CE-A5F9-33559F4AAEC9

    Thanks, Suresh

  • NikitaR
    NikitaR Member Posts: 16 Blue Ribbon

    Hi,

    Ooops - apologies. My db is reporting NLS_Characterset = WE8ISO8859P1

    Sorry about that. I will investigate choosing the character set at spool time.

    Thnx,

    N

  • NikitaR
    NikitaR Member Posts: 16 Blue Ribbon

    Good Day,

    My source db is reporting SHOW NLS as:

    DB_TIMEZONE -04:00

    NLS_CALENDAR GREGORIAN

    NLS_CHARACTERSET WE8ISO8859P1

    NLS_COMP BINARY

    NLS_CURRENCY $

    NLS_DATE_FORMAT RR-MM-DD

    NLS_DATE_LANGUAGE AMERICAN

    NLS_DUAL_CURRENCY $

    NLS_ISO_CURRENCY CANADA

    NLS_LANGUAGE AMERICAN

    NLS_LENGTH_SEMANTICS BYTE

    NLS_NCHAR_CONV_EXCP FALSE

    NLS_NUMERIC_CHARACTERS ,

    NLS_SORT BINARY

    NLS_TERRITORY CANADA

    NLS_TIMESTAMP_FORMAT RR-MM-DD HH24:MI:SSXFF

    NLS_TIMESTAMP_TZ_FORMAT RR-MM-DD HH24:MI:SSXFF TZR

    NLS_TIME_FORMAT HH24:MI:SSXFF

    NLS_TIME_TZ_FORMAT HH24:MI:SSXFF TZR

    SESSION_TIMEZONE America/New_York

    SESSION_TIMEZONE_OFFSET -05:00


    My Destination db is reporting:


    DB_TIMEZONE +00:00

    NLS_CALENDAR GREGORIAN

    NLS_CHARACTERSET AL32UTF8

    NLS_COMP BINARY

    NLS_CURRENCY $

    NLS_DATE_FORMAT RR-MM-DD

    NLS_DATE_LANGUAGE AMERICAN

    NLS_DUAL_CURRENCY $

    NLS_ISO_CURRENCY CANADA

    NLS_LANGUAGE AMERICAN

    NLS_LENGTH_SEMANTICS BYTE

    NLS_NCHAR_CONV_EXCP FALSE

    NLS_NUMERIC_CHARACTERS ,

    NLS_SORT BINARY

    NLS_TERRITORY CANADA

    NLS_TIMESTAMP_FORMAT RR-MM-DD HH24:MI:SSXFF

    NLS_TIMESTAMP_TZ_FORMAT RR-MM-DD HH24:MI:SSXFF TZR

    NLS_TIME_FORMAT HH24:MI:SSXFF

    NLS_TIME_TZ_FORMAT HH24:MI:SSXFF TZR

    SESSION_TIMEZONE America/New_York

    SESSION_TIMEZONE_OFFSET -04:00


    We have been experimenting with running sql.exe on Win10 rel 18.0x...

    We have also tried leaving the default chcp at 437, and tried with 65001 trying to force a UTF-8 dumping of data.

    Still, when I try to insert into the dest db, I get the wingdings. Oddly enough, if I load the sql file with the insert statements directly into SQL dev 21.4.1.349, the sql file runs perfectly.

    Can anyone tell :

    • why the difference,
    • and what can we do to perform the proper encoding.

    Please n Thnx,

    Nikita

  • NikitaR
    NikitaR Member Posts: 16 Blue Ribbon

    UPDATE - SOLVED

    Good day all,

    I figured this out . I used sqlcl to unload a table, and then load to my dest db.

    In my case, the tbl structures were the same, so need for mapping. This un/load business worked out really great.

    Thnx to all that helped.

    Nikita