Home » RDBMS Server » Server Utilities » SQL*LOADER Error - ORA-01722 invalid number
| SQL*LOADER Error - ORA-01722 invalid number [message #73796] |
Tue, 13 July 2004 11:21  |
mitra fatolahi
Messages: 38
Registered: October 2002
|
Member |
|
|
Hello All,
I am using the "Load Data From" link in the Maintenance link
of the Oracle 10g web-base EM to load data into a table.
I am trying to load the data from a csv file with comma delimiter.
The table already exist in my Oracle 10g database.
The DDL for the table is something like:
CREATE Parameter(
ID_PARAMETER NUMBER(10,0) CONSTRAINT PK_PARAMETER PRIMARY KEY NOT NULL,
CANVIEWCONTENTS NUMBER(1,0) DEFAULT 0 NOT NULL,
CANCOPY NUMBER(1,0) DEFAULT 0 NOT NULL,
CANSHARE NUMBER(1,0) DEFAULT 0 NOT NULL,
CANOVERWRITE NUMBER(1,0) DEFAULT 0 NOT NULL,
DAYSTOEXPIRATION NUMBER(10,0) DEFAULT -1 NOT NULL,
CANMODIFYEXPIRATION NUMBER(1,0) DEFAULT 0 NOT NULL
)
My load.CTL file looks like:
LOAD DATA
INFILE 'C:ParameterParameter.csv'
INTO TABLE Parameter
FIELDS TERMINATED BY ","
(ID_PARAMETER,
CANVIEWCONTENTS,
CANCOPY,
CANSHARE,
CANOVERWRITE,
DAYSTOEXPIRATION,
CANMODIFYEXPIRATION
)
The csv file includes the column names in the first row.
Below is a sample of the data. Please note, here I am separating each column with a tab and I am not showing the column names:
0 FALSE FALSE FALSE FALSE 0 FALSE
1 FALSE FALSE FALSE FALSE 0 TRUE
2 FALSE FALSE FALSE TRUE 0 TRUE
3 FALSE FALSE TRUE TRUE 0 TRUE
4 FALSE TRUE TRUE TRUE 0 TRUE
5 TRUE TRUE TRUE TRUE 0 TrUE
SQL*LOADER logs in the LOAD.LOG:
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
ID_PARAMETER FIRST * , CHARACTER
CANVIEWCONTENTS NEXT * , CHARACTER
CANCOPY NEXT * , CHARACTER
CANSHARE NEXT * , CHARACTER
CANOVERWRITE NEXT * , CHARACTER
DAYSTOEXPIRATION NEXT * , CHARACTER
CANMODIFYEXPIRATION NEXT * , CHARACTER
Record 1: Rejected - Error on table SECUR_PARMS, column CANVIEWCONTENTS.
ORA-01722: invalid number.
The above error message was repeated so many times in the load.log file.
At the end of the Load.log file there was a summary of the errors:
MAXIMUM ERROR COUNT EXCEEDED - Above statistics reflect partial run.
Table SECUR_PARMS:
0 Rows successfully loaded.
51 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
I was able to import the same data from the same csv file into
the same table sturcture sitting on a MSSQLServer database. I used
SQLServer DTS, Data import wizard.
In my SQLServer database the columns with the datatype NUMBER(1,0)
are defined as "bit" and the columns with datatype Number(10,0)
are defined as integer(INT).
After the import i opened the table in the SQLServer noticed all the "FALSE" values
are stored as "0" and all "True" values are stored as "1".
I am not sure how SQLServer DTS handled that would SQL*LOADER
be able to do the same thing? If not, what do i need to do to have
the SQL*LOADER import the data successfully? Do I need to change
"FALSE" to "0" and "TRUE" TO "1" in the csv file?
PLEASE HELP! I MUST BE ABLE TO LOAD THE DATA INTO MY TABLE.
PLEASE BE SPECIFIC IN YOUR INSTRUCTION. I AM NEW TO ORACLE!
Thank you!
Mitra
|
|
|
|
| Re: SQL*LOADER Error - ORA-01722 invalid number [message #73801 is a reply to message #73796] |
Wed, 14 July 2004 04:16  |
 |
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
please try this.
bolded words are comments...remove them
LOAD DATA
INFILE 'parameter.csv'
INTO TABLE Parameter
FIELDS TERMINATED BY X"09" [i][b]-- indicates tab delimited data [/i][/b]
trailing nullcols
(
ID_PARAMETER,
CANVIEWCONTENTS "decode(:CANVIEWCONTENTS,'FALSE','0','TRUE','1')",
CANCOPY "decode(:CANCOPY,'FALSE','0','TRUE','1')", [b][i]-- decode is oracle builtin function [/i][/b]
CANSHARE "decode(:CANSHARE,'FALSE','0','TRUE','1')",
CANOVERWRITE "decode(:CANOVERWRITE,'FALSE','0','TRUE','1')",
DAYSTOEXPIRATION ,
CANMODIFYEXPIRATION "decode(:CANMODIFYEXPIRATION,'FALSE','0','TRUE','1')"
)
|
|
|
|
| Re: SQL*LOADER Error - ORA-01722 invalid number [message #74084 is a reply to message #73801] |
Sat, 04 September 2004 01:52 |
Simanta
Messages: 1
Registered: September 2004
|
Junior Member |
|
|
What causes this error?
This problem occurs when an attempt is made to convert a character string into a number, and the string cannot be converted into a valid number. Valid numbers contain the digits '0' thru '9', with possibly one decimal point, a sign (+ or -) at the beginning or end of the string, or an 'E' or 'e' (if it is a floating point number in scientific notation). All other characters are forbidden.
There are numerous situations where this conversion may occur. A numeric column may be the object of an INSERT or an UPDATE statement. Or, a numeric column may appear as part of a WHERE clause. It is even possible for this error to appear when there are no numeric columns appearing explicitly in the statement! Here are some examples:
SQL> select to_number('3434,3333.000') from dual;
ERROR:
ORA-01722: invalid number
no rows selected
The above statement throws the error message, because it has found a character, in this case, a comma and the default format for TO_NUMBER does not contain a comma.
The same error can occur when you use arithmetic functions on strings.
SQL> select 'abc' - 124 from dual;
ERROR:
ORA-01722: invalid number
no rows selected
The error can occur when you add dates with string values.
SQL> select '01-JUN-01' - 'abc' from dual;
ERROR:
ORA-01722: invalid number
no rows selected
Back to top of file
|
|
|
|
| Help hor ctl file [message #74414 is a reply to message #73801] |
Tue, 07 December 2004 03:46 |
Selva
Messages: 15
Registered: June 2001
|
Junior Member |
|
|
i have got this err while i use sql ldr functinality,
SQL*Loader-704: Internal error: Maximum record length must be <= [[10000000]]
what are the options should set for both ctl file and sqlldr parameters..
1.i am using the ctl file as
LOAD DATA
INFILE '/appl/noa/abinitio/sample.csv'
INSERT INTO TABLE TBLSAP_HISTORY
FIELDS TERMINATED BY ','
(id,sap,css_database,cost_centre,installation_name,vp_account,telno,billing_name,installation_address,billing_address,last_action,order_issuer,customer_type,order_no,activity,mas,mai,mfr,taig,mdra,mai2,ftp,f_indicator,order_received,custid,required_by,siteid,order_type,batch_no,duty_reference,bill_group,cust_classn,trawled,old_date,old_mdra,old_taig,multi_batch,xsource,multi_count,onebill,ebpp,taig_errdate,ord_issuer_ouc,vat_status)
#############
2.my sqlldr parameters are
sqlldr userid=$USERPASS control=$CTL_DIR/$ctl log=$SQLLDR_LOG_FILE bad=$SQLLDR_BAD_FILE silent=header,feedback,errors=0 rows=25000 bindsize=10240000 readsize=15360000
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Apr 26 12:26:59 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
