What encoding should i use...

NikitaR

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

NikitaR

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

Suresh Sangaran-Oracle

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

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

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

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