sql loader problem 2004-07-22 - By Ian Cary
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
(type oracle_loader
default directory in_dir
access parameters (records delimited by newline
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)
)
location ( '1244.csv ')
)
reject limit unlimited;
where in_dir is a pointer to the directory containing file created using
the CREATE DIRECTORY command.
Cheers,
Ian
|-- ------+-- ---- ---- ---- ---- ------ >
| | davewendelken@(protected)|
| | hlink.net |
| | Sent by: |
| | oracle-l-bounce@(protected)|
| | eelists.org |
| | |
| | |
| | 22/07/2004 15:10 |
| | Please respond to |
| | oracle-l |
| | |
|-- ------+-- ---- ---- ---- ---- ------ >
>-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---|
| |
| To: oracle-l@(protected) |
| 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 (See ORA-01460.ora-code.com): 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
)
Here 's the results I get from the log:
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 (See ORA-01460.ora-code.com): unimplemented or unreasonable conversion requested
Record 2: Rejected - Error on table LTEST.
ORA-01460 (See ORA-01460.ora-code.com): unimplemented or unreasonable conversion requested
Record 3: Rejected - Error on table LTEST.
ORA-01460 (See ORA-01460.ora-code.com): unimplemented or unreasonable conversion requested
Record 4: Rejected - Error on table LTEST.
ORA-01460 (See ORA-01460.ora-code.com): unimplemented or unreasonable conversion requested
Record 5: Rejected - Error on table LTEST.
ORA-01460 (See ORA-01460.ora-code.com): unimplemented or unreasonable conversion requested
Record 6: Rejected - Error on table LTEST.
ORA-01460 (See ORA-01460.ora-code.com): unimplemented or unreasonable conversion requested
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
To unsubscribe send email to: oracle-l-request@(protected)
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
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
__ ____ ____ ____ ____ ____ ____ ____ ____ ____ ____ ____ ____ ____ __
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@(protected)
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
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
|
|
