RE: sql loader problem

  • From: david wendelken <davewendelken@xxxxxxxxxxxxx>
  • To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 22 Jul 2004 10:46:45 -0400 (GMT-04:00)

Thomas's idea worked.  I had just found it when his very nice post showed up.


-----Original Message-----
From: "Mercadante, Thomas F" <thomas.mercadante@xxxxxxxxxxxxxxxxx>
Sent: Jul 22, 2004 10:22 AM
To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
Cc: "'davewendelken@xxxxxxxxxxxxx'" <davewendelken@xxxxxxxxxxxxx>
Subject: RE: sql loader problem

David,

Try changing it as follows:

    (DATACALL        terminated by "," ,
    ,QUESTIONID     terminated by "," ,
    ,ORGID          terminated by "," ,
    ,ANSWER01       terminated by "," ,
    ,ANSWER02       terminated by "," 
    )                            


SqlLdr gets weirded out when you start using things like INTEGER.  If you
just tell it how to get the data, it will convert it to the correct data
type.

Hope this helps.


Tom Mercadante
Oracle Certified Professional


-----Original Message-----
From: david wendelken [mailto:davewendelken@xxxxxxxxxxxxx] 
Sent: Thursday, July 22, 2004 10:11 AM
To: oracle-l@xxxxxxxxxxxxx
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
    )                            
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: 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
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx put
'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: