Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to modify NLS_DATE_LANGUAGE in oracle, which is very detailed and has certain reference value. Friends who are interested must finish reading it!

1. NLS_DATE_LANGUAGE

The following is a temporary modification of the nls_date_language parameter to Chinese to facilitate the current session to import data such as'08-December-13 03.16.19.119000 afternoon'.

Error reporting ORA-01843: not a valid month solution

1. View NLS_TIMESTAMP_FORMAT and NLS_DATE_LANGUAGE parameters and SYSTIMESTAMP data formats

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

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 > SELECT SYSTIMESTAMP FROM DUAL

SYSTIMESTAMP

11-FEB-18 09.59.08.147761 AM + 08:00

2. Determine the NLS_TIMESTAMP_FORMAT format

If the parameter NLS_TIMESTAMP_FORMAT format is not DD-MON-RR HH.MI.SSXFF AM, you must first adjust the parameter.

Command to adjust this parameter: alter session set NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH.MI.SS.FF8 AM'

Note: this command is only useful for the current session.

3. Determine the NLS_DATE_LANGUAGE format

Check whether NLS_DATE_LANGUAGE is SIMPLIFIED CHINESE or AMERICA. If it is AMERICA, you need to change it to SIMPLIFIED CHINESE.

In order to display the Chinese morning, afternoon and month, the current session modification method

SQL > ALTER SESSION SET nls_date_language='SIMPLIFIED CHINESE'

Note: this command is only useful for the current session.

4. Modified parameters

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 SIMPLIFIED CHINESE

NLS_CHARACTERSET ZHS16GBK

NLS_SORT BINARY

NLS_TIME_FORMAT HH.MI.SSXFF AM

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

5. View type:

SQL > select systimestamp from dual

/

SYSTIMESTAMP

November-February-18 09.38.29.190818 am + 08:00

6. Import data:

SQL > @

Importing table SYS_AREA...

The above is all the contents of the article "how to modify NLS_DATE_LANGUAGE in oracle". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.