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博客 ” ，链接：http://blog.itpub.net/23122232/viewspace-625865/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23122232/viewspace-625865/