Home » RDBMS Server » Server Utilities » ORA-02374/ORA-01722/ORA-02372 while importing a 11.2.0.4-Dump into 12.1.0.2 (Oracle 12.1.0.2, Windows 2008 R2)
ORA-02374/ORA-01722/ORA-02372 while importing a 11.2.0.4-Dump into 12.1.0.2 [message #653764] Mon, 18 July 2016 08:03 Go to next message
opm_yann
Messages: 2
Registered: July 2016
Location: Switzerland
Junior Member
Hello!

We want to migrate our databases (first only our test db's) vom 11g to 12c. Well, I do an expdp from 11.2.0.4 without any problem, then I try to impdp the dump I got into the new database.

Unfortunately I get thousands of errors, always the same structure, like this:

ORA-02374: Konvertierungsfehler beim Laden von Tabelle "BELVIS2002"."TSD_WNMIN195"
ORA-01722: Ungültige Zahl
ORA-02372: Daten für Zeile: VALUE1 : '-~'

This message appears so often, that impdp crashes.

I do apologize for the german error messages, but I think everyone knows the errors. I found them very often via Google. But never with the problem I have in this case, the '-~'.

To explain it: The table "TSD_WNMIN195" is a very big table, as it contains timeseries-data, so there may be easily millions of lines with each 96 float values (fields VALUE1-VALUE96).

I know, that the main problem is because of negative values. But what exactly does '-~' mean? I couldn't find any informations on the internet.

The only workaround I have so far, is to - before expdp - rewrite all negative values to 0 (=zero), then expdp and impdp works really fine, without any problem. I can do that with our test databases, but not with customer databases, it would destroy very important data.

Can anyone help me with that problem?

Best regards
Yann
Re: ORA-02374/ORA-01722/ORA-02372 while importing a 11.2.0.4-Dump into 12.1.0.2 [message #653765 is a reply to message #653764] Mon, 18 July 2016 08:17 Go to previous messageGo to next message
BlackSwan
Messages: 25720
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

[oracle@vbgeneric ~]$ oerr ora 2374
02374, 00000, "conversion error loading table %*.*s.%*.*s"
// *Cause: A row could not be loaded into the table because there was a
//         conversion error for one or more columns in a row.
// *Action: See the message that follows for more information about the row
//          that could not be loaded.  To avoid this error, make sure the
//          definition of the table being imported matches the definition of
//          the table being exported.

[oracle@vbgeneric ~]$ oerr ora 1722
01722, 00000, "invalid number"
// *Cause: The specified number was invalid.
// *Action: Specify a valid number.

[oracle@vbgeneric ~]$ oerr ora 2372
02372, 00000, "data for row: %*.*s \n"
// *Cause: A conversion error occurred while loading data into a table.  The
//         message shows values for the field in the row that had 
//         the conversion error.
// *Action: None.  This is only an informational message.

I suspect some difference in character set or NLS parameter
Re: ORA-02374/ORA-01722/ORA-02372 while importing a 11.2.0.4-Dump into 12.1.0.2 [message #654077 is a reply to message #653765] Wed, 27 July 2016 00:35 Go to previous messageGo to next message
Frank Naude
Messages: 4531
Registered: April 1998
Senior Member
I suspect that the target db's character set requires more space for storing negative numbers. This can happen when you export/import from a single byte to a multibyte characterset.

To confirm, please run the below query on both databases and post the output:
select value from NLS_DATABASE_PARAMETERS where parameter='NLS_CHARACTERSET';
Re: ORA-02374/ORA-01722/ORA-02372 while importing a 11.2.0.4-Dump into 12.1.0.2 [message #654091 is a reply to message #653764] Wed, 27 July 2016 01:19 Go to previous messageGo to next message
Michel Cadot
Messages: 65208
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
But what exactly does '-~' mean? I


This means minus infinitive, this a number which is negative and lower (that is very big) than any number Oracle accepts. Generally, this is a number that has been inserted in binary format from an external application (such as an OCI one). If you tell Oracle you insert a binary data, it didn't check it but when you try to then select it you get this kind of value (or an error).
It may also come from another RDBMS, for instance SQL Server accepts bigger numbers than Oracle.

Re: ORA-02374/ORA-01722/ORA-02372 while importing a 11.2.0.4-Dump into 12.1.0.2 [message #654270 is a reply to message #654091] Fri, 29 July 2016 01:57 Go to previous messageGo to next message
opm_yann
Messages: 2
Registered: July 2016
Location: Switzerland
Junior Member
Salut Michel

Sorry for late reply, I was an holiday Smile

I just checked again on database, and yes, I found now several "-INFINITY" entries via SQL-Developer. But how can I select them via SQL? As the field is float, "WHERE VALUE1 = '-INFINITY'" doesn't work of course. I suppose, the text-message I got, comes from SQLDeveloper.

Kind regards
Yann
Re: ORA-02374/ORA-01722/ORA-02372 while importing a 11.2.0.4-Dump into 12.1.0.2 [message #654271 is a reply to message #654270] Fri, 29 July 2016 02:26 Go to previous messageGo to next message
Michel Cadot
Messages: 65208
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
But how can I select them via SQL?


Not really possible.
What does return
select min(VALUE1) from "BELVIS2002"."TSD_WNMIN195"
(assuming these are column and table names)

Re: ORA-02374/ORA-01722/ORA-02372 while importing a 11.2.0.4-Dump into 12.1.0.2 [message #654299 is a reply to message #654271] Fri, 29 July 2016 09:01 Go to previous messageGo to next message
ThomasG
Messages: 3205
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
I have found that this seems to work in SQL*Plus:

SQL> WITH DATA AS(
1   SELECT -1/0F COL FROM DUAL)
2    SELECT * FROM data WHERE col = -binary_double_infinity
3    /

 
       COL
----------
      -Inf

(Version 11.0.3.0 here)

[Updated on: Fri, 29 July 2016 09:01]

Report message to a moderator

Re: ORA-02374/ORA-01722/ORA-02372 while importing a 11.2.0.4-Dump into 12.1.0.2 [message #654305 is a reply to message #654299] Fri, 29 July 2016 11:21 Go to previous message
Michel Cadot
Messages: 65208
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

These are binary float numbers (single and double) not the same than NUMBER number(!):
SQL> WITH DATA AS(SELECT -1/0F COL FROM DUAL)
  2  select dump(col), dump(1/0F), dump(-binary_double_infinity) from data;
DUMP(COL)                    DUMP(1/0F)                 DUMP(-BINARY_DOUBLE_INFINITY)
---------------------------- -------------------------- -------------------------------------------
Typ=100 Len=4: 0,127,255,255 Typ=100 Len=4: 255,128,0,0 Typ=101 Len=8: 0,15,255,255,255,255,255,255
Minus infinite (-~) is a NUMBER number (type 2) with internal value a single byte to 0. I don't know if Oracle sees them as equal but it is worth to try it.
(Note the strange thing that happens to the internal representation of 1/0F.)

Previous Topic: Sql Loader
Next Topic: Field in data file exceeds maximum length - SQLLDR
Goto Forum:
  


Current Time: Sat Nov 25 03:14:21 CST 2017

Total time taken to generate the page: 0.01143 seconds