Home » SQL & PL/SQL » SQL & PL/SQL » ora-01722 error when using to_number
ora-01722 error when using to_number [message #213143] Tue, 09 January 2007 11:29 Go to next message
sandudana
Messages: 48
Registered: March 2006
Member
Hello everybody,

I am working on a 10.2.0.2 Oracle database.
I have the table DWT_ENTERPRISE_APP with the following structure:
centro_costo varchar2(30 byte),
conto varchar2(20 byte),
value varchar2(20 byte),
data_insert date,
provenienza varchar2(50 byte),
anno  varchar2(4 byte)


I have loaded with sql loader the following data:
0237360   111112   33 sysdate conteggio 2005
0237402   111111   7  sysdate conteggio 2005
0237401   111111  191 sysdate conteggio 2005 


I need to convert the VALUE field to a number.

When I execute

select to_number(value)  from DWT_ENTERPRISE_APP


I receive teh ora-01722 invalid number error and I don't understand why.

The table was loaded from a txt file with the following control file:


LOAD DATA 
INFILE 'C:\file.txt'
BADFILE 'C:\file.bad'
DISCARDFILE 'C:\file.dsc'

INTO TABLE  DWT_ENTERPRISE_APP
TRUNCATE
FILEDS TERMINATED BY ';'
TRAILING NULLCOLS
(
CENTRO_COSTO CHAR "UPPER(:CENTRO_COSTO)",
CONTO CHAR "UPPER(:CONTO)",
VALUE CHAR,
DATA_INSERT SYSDATE,
PROVENIENZA CONSTANT "file.txt",
ANNO CONSTANT "2005"
)



I need to work with column VALUE as a number not as a text.
Any help will be very appreciate.

Daniela S
Re: ora-01722 error when using to_number [message #213144 is a reply to message #213143] Tue, 09 January 2007 11:35 Go to previous messageGo to next message
BlackSwan
Messages: 25040
Registered: January 2009
Location: SoCal
Senior Member
>I receive teh ora-01722 invalid number error and I don't understand why.
Embedded space characters?
Re: ora-01722 error when using to_number [message #213145 is a reply to message #213144] Tue, 09 January 2007 11:41 Go to previous messageGo to next message
sandudana
Messages: 48
Registered: March 2006
Member
No, no embedded spaces.
Re: ora-01722 error when using to_number [message #213154 is a reply to message #213145] Tue, 09 January 2007 12:20 Go to previous messageGo to next message
Littlefoot
Messages: 20895
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Could you do such a test? Loop through values and print invalid ones; perhaps it'll give you some ideas.
SET SERVEROUTPUT ON;

DECLARE
  l_val NUMBER;
BEGIN
  FOR cur_r IN (SELECT value FROM DWT_ENTERPRISE_APP)
  LOOP
    BEGIN
      SELECT TO_NUMBER(cur_r.value)
        INTO l_val
        FROM dual;
    EXCEPTION
      WHEN OTHERS THEN
        dbms_output.put_line(cur_r.value);
    END;
  END LOOP;
END;
/ 
If there are many records (and dbms_output is about to fail), either limit number of output values or insert them into some kind of an "error log" table.
Re: ora-01722 error when using to_number [message #213160 is a reply to message #213154] Tue, 09 January 2007 12:38 Go to previous messageGo to next message
sandudana
Messages: 48
Registered: March 2006
Member
Hello,

I've just done the test.
It does print something, but they are all numbers: 33, 7, 191.

I don't know, perhaps there are some 'special/invisible' characters in the column VALUE.
I don't know what else to try.

Daniela S
Re: ora-01722 error when using to_number [message #213187 is a reply to message #213143] Tue, 09 January 2007 14:17 Go to previous messageGo to next message
BlackSwan
Messages: 25040
Registered: January 2009
Location: SoCal
Senior Member
use LENGTH function?
Re: ora-01722 error when using to_number [message #213189 is a reply to message #213187] Tue, 09 January 2007 14:22 Go to previous messageGo to next message
Littlefoot
Messages: 20895
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Or even try to SELECT TO_NUMBER(TRIM(value)) FROM your_table?
Re: ora-01722 error when using to_number [message #213258 is a reply to message #213189] Wed, 10 January 2007 01:37 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Sorry, but if this is an exact copy-paste from your query-results:
0237360   111112   33 sysdate conteggio 2005
0237402   111111   7  sysdate conteggio 2005
0237401   111111  191 sysdate conteggio 2005 

then you do have spaces in your value column. Look how the 7 in the second line is not right-aligned.

Execute this:
select '#'||value||'#' from DWT_ENTERPRISE_APP;

This will show every space in the column

[Updated on: Wed, 10 January 2007 01:37]

Report message to a moderator

Re: ora-01722 error when using to_number [message #213359 is a reply to message #213258] Wed, 10 January 2007 07:34 Go to previous messageGo to next message
sandudana
Messages: 48
Registered: March 2006
Member
Hello everybody,


1. I've tried

SELECT TO_NUMBER(TRIM(value)) FROM DWT_ENTERPRISE_APP; 

I still receive ora-01722. It seems that TRIM, LTRIM, RTRIM don't do anything

2. I've also tried

select '#'||value||'#' from DWT_ENTERPRISE_APP;
and
select '#'||trim(value)||'#' from DWT_ENTERPRISE_APP;


The result I receive in both cases is :
[code]
# 33 #
# 7 #
# 191 #
[code]

The results to 1) and 2) make me belive that the characters between '#' and my numbers are not spaces. What else could they be?

Thank you very much for your feedback.
Daniela S.
Re: ora-01722 error when using to_number [message #213362 is a reply to message #213359] Wed, 10 January 2007 07:40 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Use DUMP to get an Ascii dump of a varchar column
SQL> SELECT DUMP('ABC D') FROM dual;

DUMP('ABCD')
----------------------------
Typ=96 Len=5: 65,66,67,32,68
Re: ora-01722 error when using to_number [message #213859 is a reply to message #213362] Fri, 12 January 2007 09:41 Go to previous message
sandudana
Messages: 48
Registered: March 2006
Member
Problem solved! There were some TABs in my column.

Thank you all!
Daniela S
Previous Topic: Oracle Trigger trim problem
Next Topic: Want to select date from timestamp
Goto Forum:
  


Current Time: Tue Dec 06 00:19:31 CST 2016

Total time taken to generate the page: 0.07577 seconds