ORA-1722 Invalid Number - Import PDE
- Thread starter fasn
- Start date
Did any of the column data types change? Specifically a character column changed to a number column?
First of all because this is my first post - Hi to all
Now i've got an issue with import/export PDE feature mentioned some time ago in the topic.
PL/SQL Developer
Version 8.0.4.1514
After exporting table to PDE compressed file, importing it back gives errors ORA-01722 for each line with decimal separator. Only rows without a fractional value are imported. This issue happens in the same session with, NLS_NUMERIC_CHARACTERS for session is set to ', ' for database '.,'. I've tried to change session settings but without successful import.
What is interesting, export of the same table done with PL/SQL Developer version 7 , with the same session parameters and on the same database imports back without any errors. Also export from v8 and import with v7 resulted in ORA-01722 errors.
Also occasionally appears in separate info window information "Canvas not allow drawing".
Can you help with this ?
Jarek
Now i've got an issue with import/export PDE feature mentioned some time ago in the topic.
PL/SQL Developer
Version 8.0.4.1514
After exporting table to PDE compressed file, importing it back gives errors ORA-01722 for each line with decimal separator. Only rows without a fractional value are imported. This issue happens in the same session with, NLS_NUMERIC_CHARACTERS for session is set to ', ' for database '.,'. I've tried to change session settings but without successful import.
What is interesting, export of the same table done with PL/SQL Developer version 7 , with the same session parameters and on the same database imports back without any errors. Also export from v8 and import with v7 resulted in ORA-01722 errors.
Also occasionally appears in separate info window information "Canvas not allow drawing".
Can you help with this ?
Jarek
Last edited:
Can you ensure that the client-side NLS_NUMERIC_CHARACTERS (or the overall NLS_LANG) is the same on the import environment compared to the export environment. This will fix it.
Thanks for reply Marco,
Ok - export environment is the same that import environment nothing changes at all.
NLS_LANG = POLISH_POLAND.EE8MSWIN1250 (same in database and session)
Database NLS_NUMERIC_CHARACTERS ='.,'
Session NLS_NUMERIC_CHARACTERS =', '
After change session NLS_NUMERIC_CHARACTERS to '.,' issue also appears.
Like I mentioned before - PSDv8 behavior is slightly different that previous P\SDv7. File exported by v7 can be easily imported back to the same database with P\SDv7 or PSDv8 without changing any NLS_NUMERIC_CHARACTERS or looking to NLS_LANG but export done with P\SDv8 report errors while importing back with P\SDv8. Once again import environment is the same that export environment.
So what are the other options for that issue ?
Ok - export environment is the same that import environment nothing changes at all.
NLS_LANG = POLISH_POLAND.EE8MSWIN1250 (same in database and session)
Database NLS_NUMERIC_CHARACTERS ='.,'
Session NLS_NUMERIC_CHARACTERS =', '
After change session NLS_NUMERIC_CHARACTERS to '.,' issue also appears.
Like I mentioned before - PSDv8 behavior is slightly different that previous P\SDv7. File exported by v7 can be easily imported back to the same database with P\SDv7 or PSDv8 without changing any NLS_NUMERIC_CHARACTERS or looking to NLS_LANG but export done with P\SDv8 report errors while importing back with P\SDv8. Once again import environment is the same that export environment.
So what are the other options for that issue ?
An invalid number error can only be caused by NLS differences, or if you have changed a column data type from character in the export database to number in the import database.
Marko,
Already tried to change all nls parameters to the same values but without success. After investigation it looks like this issue is system/workstation related not server. We already reinstalled client software from 10.2 to 11.2 and also removed and installed Developer software (all with registry cleaning to be sure nothing is left)
It affects Export operation so in the PDE file (exported without compression so i can view it contents) every number value with fractal i separated by dot instead of colon. In the same version of Plsql Developer installed on another workstation there is no problem at all - numeric data are separated by colon. This issue appears 2 from 12 workstations that we checked.
Could you tell me from where exactly decimal separator in PDE export is taken, I mean sequence of registry checks or configuration files where developer looks for separator in case when it is not present. This helps us resolve this annoying issue (maybe it is different windows libraries related problem).
Already tried to change all nls parameters to the same values but without success. After investigation it looks like this issue is system/workstation related not server. We already reinstalled client software from 10.2 to 11.2 and also removed and installed Developer software (all with registry cleaning to be sure nothing is left)
It affects Export operation so in the PDE file (exported without compression so i can view it contents) every number value with fractal i separated by dot instead of colon. In the same version of Plsql Developer installed on another workstation there is no problem at all - numeric data are separated by colon. This issue appears 2 from 12 workstations that we checked.
Could you tell me from where exactly decimal separator in PDE export is taken, I mean sequence of registry checks or configuration files where developer looks for separator in case when it is not present. This helps us resolve this annoying issue (maybe it is different windows libraries related problem).
PL/SQL Developer just reads the numeric data from Oracle during export, and writes it again during import. Only the Oracle NLS settings apply. PL/SQL Developer does not explicitly use any other settings than those implicitly used by the Oracle Client.
Okay, thanks for letting me know.
Back again with this issue on v9. Symptoms like above. Re installation or fresh installation do not resolve issue (only downgrade to v802).
Like previous - data export file (pde) in versions over v802 of PL\SQL Developer contains dot '.' as separator in number values. In v802 and lower - separator is set to comma ',' (that is my NLS_NUMERIC_CHARACTERS decimal separator).
This investigation is based on saved uncompressed pde files in v802, v803 and v902.
User NLS parameters differ from database but this is not issue in v802 and beyond.
Any help appreciated.
Like previous - data export file (pde) in versions over v802 of PL\SQL Developer contains dot '.' as separator in number values. In v802 and lower - separator is set to comma ',' (that is my NLS_NUMERIC_CHARACTERS decimal separator).
This investigation is based on saved uncompressed pde files in v802, v803 and v902.
User NLS parameters differ from database but this is not issue in v802 and beyond.
Any help appreciated.
Last edited:
We cannot reproduce this. Numbers are always exported with the NLS_NUMERIC_CHARACTERS decimal separator. Can you send me a table creation DDL for one of the tables where this fails?
Ok this is my NLS parameters From database and User. Like said before NLS_NUMERIC_CHARACTERS differ as well as other. But in the versions below 802 it does not matter - over it yes.
User - NLS Session Parameters
Parameter Value
NLS_CALENDAR GREGORIAN
NLS_COMP BINARY
NLS_CURRENCY zł
NLS_DATE_FORMAT RR/MM/DD
NLS_DATE_LANGUAGE POLISH
NLS_DUAL_CURRENCY zł
NLS_ISO_CURRENCY POLAND
NLS_LANGUAGE POLISH
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NUMERIC_CHARACTERS ,
NLS_SORT POLISH
NLS_TERRITORY POLAND
NLS_TIME_FORMAT HH24:MI:SSXFF
NLS_TIMESTAMP_FORMAT RR/MM/DD HH24:MI:SSXFF
NLS_TIMESTAMP_TZ_FORMAT RR/MM/DD HH24:MI:SSXFF TZR
NLS_TIME_TZ_FORMAT HH24:MI:SSXFF TZR
NLS Database Parameters
Parameter Value
NLS_CALENDAR GREGORIAN
NLS_CHARACTERSET EE8MSWIN1250
NLS_COMP BINARY
NLS_CURRENCY $
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_DUAL_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_LANGUAGE AMERICAN
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_NCHAR_CONV_EXCP FALSE
NLS_NUMERIC_CHARACTERS .,
NLS_RDBMS_VERSION 11.1.0.6.0
NLS_SORT BINARY
NLS_TERRITORY AMERICA
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
Table:
create table TEST_NUMBER
(
id NUMBER
)
insert into TEST_NUMBER (id)
values (1);
insert into TEST_NUMBER (id)
values (2);
insert into TEST_NUMBER (id)
values (2.5);
insert into TEST_NUMBER (id)
values (4.55);
commit;
Nothing unusual here, but there are two pde files
http://dl.dropbox.com/u/48516697/test_number_802.pdehttp://dl.dropbox.com/u/48516697/test_number_902.pde
Both created for the same table TEST_NUMBER. First in v802 (not upgraded) and second in 902(demo). Files are uncompressed so looking inside you can see that decimal separator is different. Both versions of developer installed on the same operating system and connected to the same database (the same NLS settings).
For test_number_802.pde import there is no error at all (in v802 and in v902).
But for second file (test_number_902.pde), created with v902, regardless of which of two version of Developer I use it fails to load.
Import table TEST_NUMBER
Error inserting record into TEST_NUMBER:
ORA-01722: niepoprawna liczba
Error inserting record into TEST_NUMBER:
ORA-01722: niepoprawna liczba
Error inserting record into TEST_NUMBER:
ORA-01722: niepoprawna liczba
2 Records loaded, 3 errors
Of course - changing NUMBER column to varchar2 insert is done but for test_number_902.pde separator is '.' and for test_number_802.pde it is ','.
I hope I wrote enough detail and the files will explain the rest.
-------------
I think I know where error is:
v802 use nls_session_parametersfor both import and export .
v902 use nls_database_parameters for export BUT for import nls_session_parameters is used.
If your NLS_NUMERIC_CHARACTERS for database and for session are the same there is no error.
To see difference - change v802 to 'single session' and alter session NLS_NUMERIC_CHARACTERS first to ', ' and export to pde second to ',.' and also export to pde - there is difference between files.
If you do this in v803 - v902 change of session NLS_NUMERIC_CHARACTERS does not affect the appearance of the exported file (separator is taken from database).
User - NLS Session Parameters
Parameter Value
NLS_CALENDAR GREGORIAN
NLS_COMP BINARY
NLS_CURRENCY zł
NLS_DATE_FORMAT RR/MM/DD
NLS_DATE_LANGUAGE POLISH
NLS_DUAL_CURRENCY zł
NLS_ISO_CURRENCY POLAND
NLS_LANGUAGE POLISH
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NUMERIC_CHARACTERS ,
NLS_SORT POLISH
NLS_TERRITORY POLAND
NLS_TIME_FORMAT HH24:MI:SSXFF
NLS_TIMESTAMP_FORMAT RR/MM/DD HH24:MI:SSXFF
NLS_TIMESTAMP_TZ_FORMAT RR/MM/DD HH24:MI:SSXFF TZR
NLS_TIME_TZ_FORMAT HH24:MI:SSXFF TZR
NLS Database Parameters
Parameter Value
NLS_CALENDAR GREGORIAN
NLS_CHARACTERSET EE8MSWIN1250
NLS_COMP BINARY
NLS_CURRENCY $
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_DUAL_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_LANGUAGE AMERICAN
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_NCHAR_CONV_EXCP FALSE
NLS_NUMERIC_CHARACTERS .,
NLS_RDBMS_VERSION 11.1.0.6.0
NLS_SORT BINARY
NLS_TERRITORY AMERICA
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
Table:
create table TEST_NUMBER
(
id NUMBER
)
insert into TEST_NUMBER (id)
values (1);
insert into TEST_NUMBER (id)
values (2);
insert into TEST_NUMBER (id)
values (2.5);
insert into TEST_NUMBER (id)
values (4.55);
commit;
Nothing unusual here, but there are two pde files
http://dl.dropbox.com/u/48516697/test_number_802.pdehttp://dl.dropbox.com/u/48516697/test_number_902.pde
Both created for the same table TEST_NUMBER. First in v802 (not upgraded) and second in 902(demo). Files are uncompressed so looking inside you can see that decimal separator is different. Both versions of developer installed on the same operating system and connected to the same database (the same NLS settings).
For test_number_802.pde import there is no error at all (in v802 and in v902).
But for second file (test_number_902.pde), created with v902, regardless of which of two version of Developer I use it fails to load.
Import table TEST_NUMBER
Error inserting record into TEST_NUMBER:
ORA-01722: niepoprawna liczba
Error inserting record into TEST_NUMBER:
ORA-01722: niepoprawna liczba
Error inserting record into TEST_NUMBER:
ORA-01722: niepoprawna liczba
2 Records loaded, 3 errors
Of course - changing NUMBER column to varchar2 insert is done but for test_number_902.pde separator is '.' and for test_number_802.pde it is ','.
I hope I wrote enough detail and the files will explain the rest.
-------------
I think I know where error is:
v802 use nls_session_parametersfor both import and export .
v902 use nls_database_parameters for export BUT for import nls_session_parameters is used.
If your NLS_NUMERIC_CHARACTERS for database and for session are the same there is no error.
To see difference - change v802 to 'single session' and alter session NLS_NUMERIC_CHARACTERS first to ', ' and export to pde second to ',.' and also export to pde - there is difference between files.
If you do this in v803 - v902 change of session NLS_NUMERIC_CHARACTERS does not affect the appearance of the exported file (separator is taken from database).
Last edited:
We have a fix for PL/SQL Developer 9.0 available. It can be downloaded here as a pre-release and will be included in the upcoming 9.0.3 release in one or two weeks.
... it will take some time before I can benefit from this fix. So can you make exception and do the fix to the highest version of PL\SQL Developer 8.0 too ?
I'm not sure if that is possible. As a workaround you can set the decimal separator to a . through the NLS_NUMERIC_CHARACTERS session property. You can either do this in the registry or in the AfterConnect.sql script in the PL/SQL Developer installation directory.
