oracle导出的时间格式,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;

PARAMETERVALUE

---------------------------------------------------------------- ---------------------------------

NLS_LANGUAGEAMERICAN

NLS_TERRITORYAMERICA

NLS_CURRENCY$

NLS_ISO_CURRENCYAMERICA

NLS_NUMERIC_CHARACTERS.,

NLS_CALENDARGREGORIAN

NLS_DATE_FORMATDD-MON-RR

NLS_DATE_LANGUAGEAMERICAN

NLS_CHARACTERSETZHS16GBK

NLS_SORTBINARY

NLS_TIME_FORMATHH.MI.SSXFF AM

PARAMETERVALUE

---------------------------------------------------------------- ---------------------------------

NLS_TIMESTAMP_FORMATDD-MON-RR HH.MI.SSXFF AM

NLS_TIME_TZ_FORMATHH.MI.SSXFF AM TZR

NLS_TIMESTAMP_TZ_FORMATDD-MON-RR HH.MI.SSXFF AM TZR

NLS_DUAL_CURRENCY$

NLS_NCHAR_CHARACTERSETAL16UTF16

NLS_COMPBINARY

NLS_LENGTH_SEMANTICSBYTE

NLS_NCHAR_CONV_EXCPFALSE

19 rows selected.

SQL> @c:\customers.txt

我的结论：(导入数据的格式与数据库当前相应格式不符，导致数据不能导入的解决办法)根据要导入数据的格式将当前session的相应参数作修改，之后数据即可导入。由于对session的修改只对当前会话起作用，其它会话要查看导入的数据，数据库仍会按数据库的格式显示数据。