0

I have c# program that fills me database with values, but when I try to export them to import.sql and then run import.sql script it always fill database with special characters and not czech letters.

Via example

I have this table

CREATE TABLE Document
(
    "Id" NUMBER(19,0) GENERATED BY DEFAULT ON NULL AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE  NOKEEP  NOSCALE , 
    "name" NVARCHAR2(200), 
    "fullname" NVARCHAR2(300)
) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;

and when I export data for example 1, "Velmi přívětivý dokument", "Dokument který byl velmi přívětivý do doby než SQL přeházelo písmenka"

and insert inside import.sql looks like this

CREATE TABLE Document
(
    "Id" NUMBER(19,0) GENERATED BY DEFAULT ON NULL AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE  NOKEEP  NOSCALE , 
    "name" NVARCHAR2(200), 
    "fullname" NVARCHAR2(300)
) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;

INSERT INTO Document ("Id","name","fullname") values (1, "Velmi přívětivý dokument", "Dokument který byl velmi přívětivý do doby než SQL přeházelo písmenka")

and run script (Using dockerd to host local db) I get special characters like this 'p��v�iv�' instead 'Přívětivý'
but when I run that INSERT inside SQL Developer it insert all czech characters inside db without any problem. I am not really sure where the problem is.

MT0
  • 143,790
  • 11
  • 59
  • 117
Justyn
  • 73
  • 7
  • How do you run the sqlfile? How do you export it? What is the character set of your database? – Wernfried Domscheit Nov 04 '21 at 13:24
  • I tried to export it as UTF - 8, CP 1250, CP 1252 but it aint doing anything. Database is AL32UTF8 and I am running sqlfile by this command "docker run -p port:port -e ORACLE_PASSWORD=password-v /home/pc/init_scripts:/container-entrypoint-initdb.d gvenzl/oracle-xe" – Justyn Nov 04 '21 at 14:39
  • Did you set `NLS_LANG` environment variable in your docker? If not set the Oracle defaults it to `NLS_LANG=AMERICAN_AMERICA.US7ASCII`, see https://stackoverflow.com/questions/33783902/odbcconnection-returning-chinese-characters-as/33790600#33790600 – Wernfried Domscheit Nov 04 '21 at 17:01
  • before I run that command in my previous comment I set NLS_LANG=EE8PC852 (Czech Lang) and still same issue . – Justyn Nov 05 '21 at 11:50
  • You must set it to the format of your export, i.e. `NLS_LANG=.AL32UTF8` (don't miss the leading dot), respective `NLS_LANG=.EE8MSWIN1250` or `NLS_LANG=.WE8MSWIN1252`, depending on your export. – Wernfried Domscheit Nov 05 '21 at 13:23

1 Answers1

0

You must set encoding, a.k.a. "code page" of your terminal (I assume cmd.exe, but I don't know docker) to the encoding of your export file. The default is neither UTF-8, nor CP1250, nor CP1252.

Most like it is CP437, CP850 or CP852 depending on your Windows language, see https://web.archive.org/web/20170916200715/http://www.microsoft.com/resources/msdn/goglobal/default.mspx

You can interrogate and change it with command chcp

Code Page Identifiers you can find here

Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110
  • I use docker with bullseye (linux) – Justyn Nov 04 '21 at 15:27
  • Then interrogate with `locale charmap` or echo `$LANG`. But I don't know how to set it. Otherwise do it the other way around and export the table in the character set of the terminal (provided it actually supports Czech characters) – Wernfried Domscheit Nov 04 '21 at 15:33
  • `echo $LANG` -> en_US.UTF-8 `locale charmap` ->UTF-8 I exported table in UTF-8 and then run the script and I get again [] instead Czech characters. It's weird since exported utf-8 is saved with Czech letters but when I use script it mess up. – Justyn Nov 04 '21 at 15:48
  • Yes, strange. Sorry, I don't know docker at all, so I cannot help you. However, the principle of my answer applies for any platform. – Wernfried Domscheit Nov 04 '21 at 15:50