Re: Need sql*loader help

The problem are your "'". SQL-Loader is interpreting them as extra characters, so the first two fields are not long enought in the database.
If you don't use the string delimitters, all should work.

Ryan Richards wrote:
>
> FYI: I am using Oracle 8.0.4 on Solaris 2.6 as well as the SQL*Loader 8.0.4 and SQLPlus 8.0.4.
>
> I made a simple table called PROJECT: here is the table definition:
>
> Name Null? Type
> ------------------------------- -------- ----
> PROJECTNUMBER VARCHAR2(9)
> PROJECTTASK VARCHAR2(4)
> PROJECTNAME VARCHAR2(150)
>
> I am trying to load (using SQL*Loader) ONE record. (I am new to loader). Here is my control file 'project.ctl':
>
> ----------------------------------------------------------------
> LOAD DATA
> INFILE *
> INTO TABLE PROJECT
> FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
> TRAILING NULLCOLS
> ( projectnumber,
> projecttask,
> projectname
> )
> BEGINDATA
> '894001000','0000','Airport project'
> -----------------------------------------------------------------
>
> when I do 'sqlldr myid/mypw control=project.ctl (the above file) I get this:
>
> ------------------------------------------------------------------
> SQL*Loader: Release 8.0.4.0.0 - Production on Wed Dec 16 16:48:14 1998
>
> (c) Copyright 1997 Oracle Corporation. All rights reserved.
>
> Commit point reached - logical record count 1
> --------------------------------------------------------------------
>
> I go to SQLPlus and try to see if the record is there via a select*from project; and get a 'no rows selected'. What is the problem? I dont understand why I cant get any records I just loaded. I also tried a 'commit' before selecting and still nothing.
>
> Here is the contents of the project.log file and YES the project.bad does contain this one record. ;)
>
> snipped....
> Column Name Position Len Term Encl Datatype
> ------------------------------ ---------- ----- ---- ---- ---------------------
> PROJECTNUMBER FIRST * , O(") CHARACTER
> PROJECTTASK NEXT * , O(") CHARACTER
> PROJECTNAME NEXT * , O(") CHARACTER
>
> Record 1: Rejected - Error on table PROJECT.
> ORA-01401: inserted value too large for column
>
> Table PROJECT:
> 0 Rows successfully loaded.
> 1 Row 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.
> snipped....
> -------------------------------------------------------------------------
>
> I figured since it is varchar and I used ' why is it trying to get the value? I tried number(9) but it kept saying incorrect number.
>
> Any help would be aprpeciated.
>
> Ryan
Received on Thu Dec 17 1998 - 13:15:55 CET