Problem while loading data using sqlldr

• • Reply

  16 years ago

  by sambadamerla

  sambadamerla

  , United States

  Joined 16 years ago

  Hi all,
  

  
  I am using a Sqlloader for loading the data from a dat file to a table Customer_tmp. I have using the perl scirpt and doing this job from last few week. its working fine my env is oracle 8i and OS is Linux.  Recently we created a oracle 9i on another linux box where this sqlloader is not working. i tested the same againg in Oracle 8i box its working fine.
  

  
  the data in my dat files is as follows
  1249922{,|[email protected]{,|!09/05/00{,|!LE{,|!marc{,|!ANÉ{,|! {,|!N{,|!0{,|!228{,|!0{,|!INT{,|!0{,|!0{,|!1000001{,|!BLSTH
  

  
  where {,|! is the Delimiter
  

  
  and my .ctl file contains as follows
  

  
  LOAD DATA
    INFILE 'test3.dat'
  TRUNCATE INTO TABLE customertmp
  FIELDS TERMINATED BY "{,|!"
    (OID, EMAIL, REGDATE DATE 'mm/dd/yy', REGTYPE, FNAME, LNAME, GENDER,
    UPLOADED, DOLLERS, WEEKSSINCEREGISTRATION, AMOUNTSPENT,
    REGPATH, FREEDOLLERS, DOLLERSEXPIRATION,CATINID,
    CATIN_NAME )
  

  
  i am getting the following error
  Column not found before end of logical record (use TRAILING NULLCOLS)
  

  
  i can use trailing nullcols after Terminated By "{,|!" because some of the fields in my data are ' '.  Even if used that i will giving that in valid  number for AmountSpent.
  

  
  and from perl iam using the following command to execute sqlldr
  

  
  if (system("sqlldr userid=$dbuser/$dbpass\@$dbname control=$ctlfile log=$logfile bad=$badfile direct=true") != 0) {
     die "Running sqlldr failed!\n";
  }
  

  
  

  
  Please can any one help me..........................
  

  
  Thanks in Advance
  

  
  sambadamerla
  

  
  

  
  

  
  

  
  

  Report abuse
• • Reply

  15 years ago

  by Sekar Sai

  Sekar Sai

  , United States

  Joined 15 years ago

  Hi
  Simple-way
  1. Check the number of columns in the table.
  2. Check the flat file for number of values in one record
  3. Make sure number of values in one record match with all record in the flat file.
  4. Make sure 1 & 2 match, if so, no need to use TRAILING NULLCOLS
  

  
  Hard-way
  1. upload all in to a single column varchae2(4000) temp table.
  2. Verify the temp table data and process through a pl/sql block to upload to the original table.
  

  Report abuse