Oracle import data error handling

Error 1: ORA-01756: quotedstringnotproperlyterminated character set encoding inconsistent Chinese content cannot be inserted query character encoding settings for the current database: SQLselect * fromv $ nls_parametersTWHEREt.PARAMETERNLS_LANGUAGEort.PARAMETERNLS_CHARACTERSET; or SQL

Error 1: ORA-01756: quoted string not properly terminated character set encoding inconsistent Chinese content cannot be inserted query character encoding settings for the current database: SQL select * from v $ nls_parameters T WHERE t. PARAMETER = NLS_LANGUAGE or t. PARAMETER = NLS_CHARACTERSET; or SQL

Error 1: ORA-01756: quoted string not properly terminated
Chinese content cannot be inserted due to inconsistent character set encoding
Query the character encoding settings of the current database:
SQL> select * from v $ nls_parameters T WHERE t. PARAMETER = 'nls _ language' or t. PARAMETER = 'nls _ CHARACTERSET ';
Or
SQL> select userenv ('language') from dual;
Query client character set encoding
In linux:
Echo $ NLS_LANG
Windows:
Echo % NLS_LANG %
Set the client character set encoding:
In LINUX:
# Common unicode character sets
Export NLS_LANG = american_america.AL32UTF8
# Common Chinese character sets
You can edit the bash_profile file for permanent settings.
Vi. bash_profile
Export NLS_LANG = "SIMPLIFIED CHINESE_CHINA.ZHS16GBK"
Or export NLS_LANG = "Simplified Chinese_china". ZHS16GBK
# Make the bash_profile settings take effect
Source. bash_profile
Windows:
# Common Chinese character sets
Set NLS_LANG = SIMPLIFIED CHINESE_CHINA.ZHS16GBK
# Common unicode character sets
Set NLS_LANG = american_america.AL32UTF8
You can change the registry key value to set permanently.
HKEY_LOCAL_MACHINE \ SOFTWARE \ ORACLE \ HOMExx \ NLS_LANG

Modify the server character set:
SQL> conn/as sysdba;
If the database server has been started, run the shutdown immediate command to shut down the database server, and then run the following command:

SQL> shutdown immediate;
SQL> STARTUP MOUNT;
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES = 0;
SQL> ALTER SYSTEM SET AQ_TM_PROCESSES = 0;
SQL> ALTER DATABASE OPEN;
SQL> ALTER DATABASE CHARACTER SET ZHS16GBK;
ORA-12712: new character set must be a superset of old character set
Prompt our character set: the new character set must be the superset of the old character set. In this case, we can skip the superset check and make changes: INTERNAL_USE is required to skip the superset check.
SQL> ALTER DATABASE CHARACTER SET INTERNAL_USE ZHS16GBK;

ERROR at line 1:
ORA-12721: operation cannot execute when other sessions are active

If the preceding error is reported, select sid, serial #, username, machine from v $ session;
Alter system kill session 'sid' serial # 'immediate; then
Alter system kill session '192, 7' immediate; as shown in the following figure:
SQL> ALTER DATABASE CHARACTER SET INTERNAL_USE ZHS16GBK;
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;

Error 2: The inserted field contains the amute symbol.
Method 1:
SQL> show define;
Define "&" (hex 26)
SQL> set define off;
SQL> show define;
Define OFF
This setting is used in Oracle to identify User-Defined variables and should be disabled under SQL * PLUS.
NOTE: If it is executed in TOAD, we recommend that you disable define in the first line of each script to be imported, otherwise, an error occurs when you import the second script containing special characters.
If it is executed in SQL * PLUS, you only need to set define OFF once, and then you can import it continuously. Until you reset define ON.
Method 2:
Replace '&' with chr (38) in an SQL statement because chr (38) is the ASCII code '&'.
SQL> Select 'Tom '| chr (38) | 'Jerry' from dual;
Method 3:
Split the original string
SQL> Select 'Tom '|' & '| 'Jerry' from dual;

Error 3: The inserted field contains the '(single quotation mark) symbol.
Method 1: Use escape characters
SQL> Select 'test' | "from dual;
Note: What are the meanings of the "" four single quotes? First, the first and last are character strings in Oracle, and there is no objection. So what does the second and third sign mean? The second 'is an escape character.
The third is our real content.
Method 2:
Escape characters are also used, but in different ways.
SQL> Select 'test "'from dual;
Note: The second and third are the escape characters and real content mentioned in method 1 above.
Method 3:
Replace 'with chr (39) in SQL, because chr (39) is 'ascii code
SQL> Select 'it' | chr (39) | 'fine 'from dual;

Error 4: ORA-00001: unique constraint violated
The primary key has a unique constraint and cannot insert the same value repeatedly.

Original article address: Error Handling for oracle import data. Thank you for sharing it with me.