Oracle向中文字符集数据库导入英文日期格式数据出现问题的解决
在程序中导入脚本文件,执行结束后,没有出现错误,但是在查询customers表时,发现只有一行数据,仔细观察发现:
1.
CREATE TABLE customers (
customer_id INTEGER
CONSTRAINT customers_pk PRIMARY KEY,
first_name VARCHAR2(10) NOT NULL,
last_name VARCHAR2(10) NOT NULL,
dob DATE,
phone VARCHAR2(12)
);
customers表中dob列类型为Date;
2.脚本文件中插入数据的脚本如下:
-- insert sample data into customers table
INSERT INTO customers (
customer_id, first_name, last_name, dob, phone
) VALUES (
1, 'John', 'Brown', '01-JAN-1965', '800-555-1211'
);
INSERT INTO customers (
customer_id, first_name, last_name, dob, phone
) VALUES (
2, 'Cynthia', 'Green', '05-FEB-1968', '800-555-1212'
);
INSERT INTO customers (
customer_id, first_name, last_name, dob, phone
) VALUES (
3, 'Steve', 'White', '16-MAR-1971', '800-555-1213'
);
INSERT INTO customers (
customer_id, first_name, last_name, dob, phone
) VALUES (
4, 'Gail', 'Black', NULL, '800-555-1214'
);
INSERT INTO customers (
customer_id, first_name, last_name, dob, phone
) VALUES (
5, 'Doreen', 'Blue', '20-MAY-1970', NULL
);
插入数据中,日期格式的月份使用的是英文,其中第四行值为NULL,而表的唯一插入的数据即为第四行.
由此可见,数据与日期的格式不符.
我先用一种笨办法解决:
把插入的数据格式改为当前数据库允许格式:
INSERT INTO customers (
customer_id, first_name, last_name, dob, phone
) VALUES (
1, 'John', 'Brown', '01-1月-1965', '800-555-1211'
);
INSERT INTO customers (
customer_id, first_name, last_name, dob, phone
) VALUES (
2, 'Cynthia', 'Green', '05-2月-1968', '800-555-1212'
);
INSERT INTO customers (
customer_id, first_name, last_name, dob, phone
) VALUES (
3, 'Steve', 'White', '16-3月-1971', '800-555-1213'
);
INSERT INTO customers (
customer_id, first_name, last_name, dob, phone
) VALUES (
4, 'Gail', 'Black', NULL, '800-555-1214'
);
INSERT INTO customers (
customer_id, first_name, last_name, dob, phone
) VALUES (
5, 'Doreen', 'Blue', '20-5月-1970', NULL
);
然后SQL>@c:\customers.txt重新导入.
第二种方法:
修改当前会话语言:
SQL> alter session set nls_language='AMERICAN' ;——仅修改该参数也可以完成数据插入
Session altered.
SQL> alter session set nls_territory='AMERICA';
Session altered.
SQL> select * from v$nls_parameters;
PARAMETER VALUE
---------------------------------------------------------------- ---------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_CHARACTERSET ZHS16GBK
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
PARAMETER VALUE
---------------------------------------------------------------- ---------------------------------
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
19 rows selected.
SQL> @c:\customers.txt
我的结论：（导入数据的格式与数据库当前相应格式不符，导致数据不能导入的解决办法）根据要导入数据的格式将当前session的相应参数作修改，之后数据即可导入。由于对session的修改只对当前会话起作用，其它会话要查看导入的数据，数据库仍会按数据库的格式显示数据。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/24812612/viewspace-676637/ ，如需转载，请注明出处，否则将追究法律责任。
