| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: sql loader problem
The answer is not to try and force the type in the control file;
LOAD DATA
INFILE '1244.csv'
BADFILE '1244.bad'
DISCARDFILE '1244.dsc'
APPEND
INTO TABLE ltest REPLACE
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(DATACALL,
QUESTIONID ,
ORGID ,
ANSWER01 ,
ANSWER02
will do the trick. I only tend to specify formats (e.g. DATE 'DD/MM/YYYY') in the fields area.
One other thing is that I'd probably use external tables these days e.g
create table ltest_ext(DATACALL NUMBER(2) QUESTIONID NUMBER(10) ORGID NUMBER(10) ANSWER01 VARCHAR2(4000) ANSWER02 VARCHAR2(4000)organization external
badfile in_dir:'1244.bad'
logfile in_dir:'1244.log'
fields terminated by ',' optionally enclosed by '"'
lrtrim
missing field values are null
(DATACALL,
QUESTIONID,
ORGID,
ANSWER01,
ANSWER02)
)
where in_dir is a pointer to the directory containing file created using the CREATE DIRECTORY command.
Cheers,
Ian
|---------+----------------------------->
| | davewendelken_at_eart|
| | hlink.net |
| | Sent by: |
| | oracle-l-bounce_at_fr|
| | eelists.org |
| | |
| | |
| | 22/07/2004 15:10 |
| | Please respond to |
| | oracle-l |
| | |
|---------+-----------------------------> >------------------------------------------------------------------------------------------------------------------------------| | | | To: oracle-l_at_freelists.org | | cc: | | Subject: sql loader problem | >------------------------------------------------------------------------------------------------------------------------------|
I don't use sqlldr very often and I'm having a problem I don't understand.
I'm getting this error: ORA-01460: unimplemented or unreasonable conversion
requested
I've slogged thru the online sqlldr manual and the error manual, but I'm
not seeing why I'm getting this problem at all. I've stripped out all the
fancy stuff I was trying to do, just to get the raw basics to work. But no
luck!
Oracle9i Enterprise Edition Release 9.2.0.5.0
Here's a sample from the simple comma-delimited file I'm trying to load:
3,1244,14,R4806W,17 3,1244,15,R6606,26 3,1244,16,R2901E,59 3,1244,17,R4501H,112 3,1244,19,R2516,0 3,1244,20,R6312,75
Here's the test table to load into:
SQL> descr ltest
Name Type --------------------------- --------------- DATACALL NUMBER(2) QUESTIONID NUMBER(10) ORGID NUMBER(10) ANSWER01 VARCHAR2(4000) ANSWER02 VARCHAR2(4000)
Control file I'm using:
LOAD DATA
INFILE '1244.csv'
BADFILE '1244.bad'
DISCARDFILE '1244.dsc'
APPEND
INTO TABLE ltest REPLACE
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(DATACALL INTEGER(2)
,QUESTIONID INTEGER(10)
,ORGID INTEGER(10)
,ANSWER01 VARCHAR
,ANSWER02 VARCHAR
Table LTEST, loaded from every logical record. Insert option in effect for this table: REPLACE TRAILING NULLCOLS option in effect
Column Name Position Len Term Encl Datatype ------------------------------ ---------- ----- ---- ---- --------------------- DATACALL FIRST 2 INTEGER QUESTIONID NEXT 10 INTEGER ORGID NEXT 10 INTEGER ANSWER01 NEXT * VARCHAR ANSWER02 NEXT * VARCHAR
Record 1: Rejected - Error on table LTEST. ORA-01460: unimplemented or unreasonable conversion requested
Record 2: Rejected - Error on table LTEST. ORA-01460: unimplemented or unreasonable conversion requested
Record 3: Rejected - Error on table LTEST. ORA-01460: unimplemented or unreasonable conversion requested
Record 4: Rejected - Error on table LTEST. ORA-01460: unimplemented or unreasonable conversion requested
Record 5: Rejected - Error on table LTEST. ORA-01460: unimplemented or unreasonable conversion requested
Record 6: Rejected - Error on table LTEST. ORA-01460: unimplemented or unreasonable conversion requested
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ______________________________________________________________________ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email ______________________________________________________________________ For the latest data on the economy and society consult National Statistics at http://www.statistics.gov.uk ********************************************************************** Please Note: Incoming and outgoing email messages are routinely monitored for compliance with our policy on the use of electronic communications ********************************************************************** Legal Disclaimer : Any views expressed by the sender of this message are not necessarily those of the Office for National Statistics ********************************************************************** ______________________________________________________________________ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email ______________________________________________________________________ ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Thu Jul 22 2004 - 09:57:18 CDT
 |
 |