If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Oracle > Data loading error using sqlldr

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Apr 2007
Posts: 26
Data loading error using sqlldr

Hi all,
I am getting an error when trying to load a table in Oracle XE using sqlldr.When i invoke the sqlldr, it closes with an error("Sqlldr needs to close") and am unable to pin point the error. My ctl file is as below:

LOAD DATA
infile 'D:\oracle-files\data\newmoney.txt'
INTO TABLE newmoney
FIELDS TERMINATED BY ','
(SSN_ID,BRANCH_NUM,JOB_CDE,EMPL_FIRST_NAME,EMPL_LA ST_NAME);

I typed in the following from the command line
sqlldr scott/tiger control=D:\oracle-files\data\newmoney.ctl

What am i not doing correctly?
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Nov 2003
Posts: 2,806
Quote:
Originally Posted by sueamus
Hi all,
I am getting an error when trying to load a table
What is the exact error message?
Do you have a logfile?
If not you could try

sqlldr scott/tiger control=D:\oracle-files\data\newmoney.ctl log=loader.log

and check the log file afterwards
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Jan 2007
Posts: 5
You may use one of those utilities
http://www.sqlmanager.net/en/products/oracle
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Apr 2007
Posts: 26
Shammat, i checked the log file and got the following error "error on table empl, column SERVICE_DT.ORA-00984: column not allowed here."
In my ctl file, i formatted the columns by giving them start and end positions and for the dates, gave the date format in the data file.I did not paste the whole table definition.Here is the ctl file with the definitions:

LOAD DATA
infile 'D:\oracle-files\data\newmoney.txt'
INTO TABLE newmoney
TRAILING NULLCOLS
(SSN_ID position(1:12) ,
BRANCH_NUM position(13:14) ,
JOB_CDE position(15:16) ,
HIRE_REHIRE_DT position(17:26) "YYYY-MM-DD",
EMPL_FIRST_NAME position(28:29) ,
LAST_NAME position(30:33) ,
DEPT_NAME position(34:44) ,
DEPT_NAME position(45:75) ,
REVIEW_DT position(76:85) "YYYY-MM-DD",
NEXT_REVIEW_DT position(87:96) "YYYY-MM-DD",
SERVICE_DT position(98:107) "YYYY-MM-DD")

Thanks.
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Nov 2003
Posts: 2,806
Your first example indicated that the input file is comma-separated, why are you using absolute positions in the second control file?

Additionally the error message does not match the given control file. The error messages says "table empl" but the control file says "INTO TABLE newmoney". Are you sure you are using the correct control file and are connecting the correct database?

I have made the experience that for DATE (or TIMESTAMP) columns it is better to explicitely define them as DATE in the control file, but let SQL*Loader determine the data type for all others.
This is an example of one of my control files:
Code:
LOAD DATA CHARACTERSET UTF8
TRUNCATE
INTO TABLE PARTNER_PROFILE
FIELDS TERMINATED BY '|'
    TRAILING NULLCOLS
(
    partner_id,
    created_date DATE "DD-MN-YYYY",
    created_login,
    last_upd_date DATE "DD-MN-YYYY",
    name,
    partner_flag,
    site,
    status,
    stage
)
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Apr 2007
Posts: 26
Shammat, do not worry about the confusion above of the control file and the error message.Thank you so much, that just worked fine with the date.

However, i am still having problems with timestamp,it brings an error " ORA-01830: date format picture ends before converting entire input string" when i replace date with timestamp in the field in question. What do i do in this case?
The format for the timestamp in the data file is "YYYY-MM-DD-HH24.MM.SS.SSSSSS" ,but my format in the ctl file is YYYY-MM-DD-HH24.MM.SS.SSSSS"
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Nov 2003
Posts: 2,806
Quote:
Originally Posted by sueamus
The format for the timestamp in the data file is "YYYY-MM-DD-HH24.MM.SS.SSSSSS" ,but my format in the ctl file is YYYY-MM-DD-HH24.MM.SS.SSSSS"
Are you sure this format is correct? SSSSS is (according to the manual) "Seconds past midnight". But I guess you probably have milliseconds at the end, right? As far as I know Oracle does not support that. So you will need to remove the milliseconds at the end.

I had to do something similar as well, and ended up with using substr and to_date(), so you could try the following:

Code:
(
  ...
  DATECOL "to_date(substr(:DATECOL ,1,21),'YYYY-MM-DD-HH24.MM.SS')",
  ...
)
(not sure if I counted the characters correctly in the substr() call)
Reply With Quote
  #8 (permalink)  
Old
Lead Application Develope
 
Join Date: Jun 2004
Location: Liverpool, NY USA
Posts: 2,415
If the version of oracle is 9 or above, and the column type is timestamp, milliseconds are supported. But the format mask would be

YYYY-MM-DD-HH24.MM.SS.FFFFF"
__________________
Bill
You do not need a parachute to skydive. You only need a parachute to skydive twice.
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
Join Date: Apr 2007
Posts: 26
Thanks all.Beilstwh's solution works fine.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On