ORA-1722 When Importing a Table With Column Default Values set With NLS_NUMERIC_CHARACTERS=',.' (Doc ID 470776.1)

Last updated on NOVEMBER 28, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 10.1.0.2 to 10.2.0.3 [Release 10.1 to 10.2]
Information in this document applies to any platform.
***Checked for relevance on 21-Nov-2013***

Symptoms

You performed the following steps:

- set NLS_NUMERIC_CHARACTERS=',.'  (<comma><period>) or ', ' (<comma><space>) at OS level
- created a table with default value using this NLS_NUMERIC_CHARACTERS format:

SQL> create table tab001 (col001 number);
SQL> alter table tab001 modify (col001 default '0,0');


- export the table

#> exp scott/passwd files=tab001.dmp tables=tab001


- drop and re-import the table

SQL> drop table tab001;

#> imp scott/passwd file=tab001.dmp tables=tab001


This breaks with errors:

IMP-00017: following statement failed with ORACLE error 1722:
" ALTER TABLE "TAB001" MODIFY ("COL001" DEFAULT '0,0')"
IMP-00003: ORACLE error 1722 encountered
ORA-01722: invalid number
Import terminated successfully with warnings.

and the table is not imported.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms