No question at this time
DBA Top 10
1 M. Cadot 13000
2 B. Vroman 11900
3 A. Kavsek 11200
4 J. Schnackenberg 5600
4 M. Hidayathullah ... 5600
6 T. Boles 5400
7 P. Wisse 3200
8 T. P 700
9 A. Rai 600
10 R. Wauben 500
10 H. Steijntjes 500
About
DBA-Village
Download PLATO
The free tool for auditing and tuning your database
Version 55 now available
Sep 02, 2016
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48147
Total active users1697
Act. users last 24h6
Act. users last hour1
Registered user hits last week261
Registered user hits last month1385
Go up

sqlldr ORA-01722: invalid number
Next thread: Matrix Statistics Report
Prev thread: Query Output Format

Message Score Author Date
Hi masters, I am trying to upload data from the...... javed akhtar Nov 14, 2017, 10:03
Hello, the <I>string of characters</I> 'NULL' i...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts Bruno Vroman Nov 14, 2017, 10:41
Hello Bruno, but when i am inserting data from ...... javed akhtar Nov 14, 2017, 10:44
Hi, <pre> load data infile '/home/oracle/mcar/...... Score: 700 PtsScore: 700 PtsScore: 700 PtsScore: 700 PtsScore: 700 Pts Ales Kavsek Nov 14, 2017, 10:47
Hello Javed, try the following:<pre>SELECT TO_N...... Bruno Vroman Nov 14, 2017, 10:56
BTW, if you look at the "NEWSATE" column: I assum...... Bruno Vroman Nov 14, 2017, 11:49

Follow up by mail Click here


Subject: sqlldr ORA-01722: invalid number
Author: javed akhtar, India
Date: Nov 14, 2017, 10:03, 99 days ago
Os info: linux
Oracle info: 11g
Message: Hi masters,

I am trying to upload data from the flat file but I am getting mentioned error.

CONTROL FILE.
load data infile '/home/oracle/mcar/CAMPAIGN_EVENT.csv' insert into table event fields terminated by ","
TRAILING NULLCOLS
(EVENTID,
ID,
INTERFACE char,
EVENTTYPE char,
CMID,
NEWSTATE,
NEWPRIORITY,
EVENTTIME DATE "YYYY-MM-DD HH24:MI:SS",
USERNAME char,
STATUS char)


FILE.

1,1706,SMS,SUSPEND_CM,1706,NULL,NULL,2016-10-06 00:00:00,clm,done


TABLE.
SQL> desc event
Name Null? Type
----------------------------------------- -------- ----------------------------
EVENTID NOT NULL NUMBER(20)
ID NOT NULL NUMBER(11)
INTERFACE VARCHAR2(20)
EVENTTYPE NOT NULL VARCHAR2(20)
CMID NUMBER(11)
NEWSTATE VARCHAR2(20)
NEWPRIORITY NUMBER(11)
EVENTTIME DATE
USERNAME NOT NULL VARCHAR2(100)
STATUS NOT NULL VARCHAR2(100)

EORROR

Record 1: Rejected - Error on table EVENT, column NEWPRIORITY.
ORA-01722: invalid number

Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: sqlldr ORA-01722: invalid number
Author: Bruno Vroman, Belgium
Date: Nov 14, 2017, 10:41, 99 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message: Hello,

the string of characters 'NULL' is not a valid number...

The "NULL" is misleading... It is not NULL like "unknown value", it is the string 'NULL' like it could be the string 'BULL'...

Best regards,

Bruno Vroman.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: sqlldr ORA-01722: invalid number
Author: javed akhtar, India
Date: Nov 14, 2017, 10:44, 99 days ago
Message: Hello Bruno,

but when i am inserting data from sqlplus it is working fine.

SQL> insert into event values (1,1706,'SMS','SUSPEND_CM',1706,NULL,NULL,'06-OCT-2017','CLM','DONE');

1 row created.

kindly suggest how i can handle the null from sqlldr
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: sqlldr ORA-01722: invalid number
Author: Ales Kavsek, Slovenia
Date: Nov 14, 2017, 10:47, 99 days ago
Score:   Score: 700 PtsScore: 700 PtsScore: 700 PtsScore: 700 PtsScore: 700 Pts
Message: Hi,


load data infile '/home/oracle/mcar/CAMPAIGN_EVENT.csv' insert into table event fields terminated by ","
TRAILING NULLCOLS
(EVENTID,
ID,
INTERFACE char,
EVENTTYPE char,
CMID,
NEWSTATE,
NEWPRIORITY "DECODE(:newpriority,'NULL',null,:newpriority)",
EVENTTIME DATE "YYYY-MM-DD HH24:MI:SS",
USERNAME char,
STATUS char)

Regards,
Ales
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: sqlldr ORA-01722: invalid number
Author: Bruno Vroman, Belgium
Date: Nov 14, 2017, 10:56, 99 days ago
Message: Hello Javed,

try the following:
SELECT TO_NUMBER( '' ) FROM dual;

SELECT TO_NUMBER( NULL ) FROM dual;
SELECT TO_NUMBER( 'NULL' ) FROM dual;
Do you see the difference between second and third line? The second is the NULL meaning "unknown value", but the third is a string with letters N, U, L and L...

In your file you should not have ...,NULL,... but simply ...,,...

Note that in your insert you enclose SMS in parenthesis, as well as SUSPEND_CM, ... but not the strings NULL; why? because you add a "human mistake" by changing the string into what you want to mean...

Best regards,

Bruno
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: sqlldr ORA-01722: invalid number
Author: Bruno Vroman, Belgium
Date: Nov 14, 2017, 11:49, 99 days ago
Message: BTW,
if you look at the "NEWSATE" column: I assume that you expect it to contain nothing (or '' or NULL)... But it contains a string of 4 characters starting with 'N' and ending with 'L'...
;-)
Bruno
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here