This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 30th, when you will be able to use this site as normal.

    Forum Stats

  • 3,890,096 Users
  • 2,269,775 Discussions
  • 7,916,823 Comments

Discussions

What encoding should i use...

NikitaR
NikitaR Member Posts: 17 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

Best Answer

  • NikitaR
    NikitaR Member Posts: 17 Blue Ribbon
    Answer ✓

    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

Answers

  • Suresh Sangaran-Oracle
    Suresh Sangaran-Oracle Member, Moderator Posts: 352 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: 17 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: 17 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: 17 Blue Ribbon
    Answer ✓

    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