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 > loading txt file from sql loader to oracle database

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Jan 2010
Posts: 29
loading txt file from sql loader to oracle database

Hello All,

i am trying to load data but i am getting errors:


here is my table structure:

create table LINECVG_LOOKUP
(L2COCD NUMBER(2),
L2SABB CHAR(2BYTE),
L2PRCD CHAR(3BYTE),
L2RRDT DATE,
L2CVCD VARCHAR2(6BYTE),
SSLNCV NUMBER(4)
);


Sample data:

L2COCD L2SABB L2PRCD L2RRDT L2CVCD SSLNCV
2 AL ATC 10/13/1997 ADDEQ 2100
2 AL ATC 10/13/1997 BI 1901
2 AL ATC 10/13/1997 COLL 2103
2 AL ATC 10/13/1997 COMP 2101
2 AL ATC 10/13/1997 MEDPAY 2801
2 AL ATC 10/13/1997 PD 2001


My control fiel:

LOAD DATA
INFILE '\\OHLEWNAS0240\THIDWLOAD\line_cvg_project\linecvg 1.txt'
BADFILE '\\OHLEWNAS0240\THIDWLOAD\line_cvg_project\linecvg 1.bad'
DISCARDFILE '\\OHLEWNAS0240\THIDWLOAD\line_cvg_project\linecvg 1.dsc'

INTO TABLE "DATUSER"."LINECVG_LOOKUP"
TRUNCATE
FIELDS TERMINATED BY ' '
OPTIONALLY ENCLOSED BY '"'
(L2COCD ,

L2SABB ,

L2PRCD ,

L2RRDT "TO_DATE(:L2RRDT,'MM/DD/YY')",

L2CVCD ,

SSLNCV )
---------------------------------------------------------------

when i am trying to load data i a getting error:


Record 1: Rejected - Error on table "DATUSER"."LINECVG_LOOKUP", column L2SABB.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 2: Rejected - Error on table "DATUSER"."LINECVG_LOOKUP", column L2SABB.
Column not found before end of logical record (use TRAILING NULLCOLS)





Can anybody suggest me what the error might be?





Thanks
Hasya
Reply With Quote
  #2 (permalink)  
Old
Lost Boy
 
Join Date: Jan 2004
Location: Croatia, Europe
Posts: 3,995
Works fine for me.

A control file:
Code:
options (skip = 1)
load data
infile *
replace
INTO TABLE LINECVG_LOOKUP
TRUNCATE
FIELDS TERMINATED BY ' '
(L2COCD ,
 L2SABB ,
 L2PRCD ,
 L2RRDT "TO_DATE(:L2RRDT,'MM/DD/YYYY')",
 L2CVCD ,
 SSLNCV 
)

begindata
L2COCD L2SABB L2PRCD L2RRDT L2CVCD SSLNCV
2 AL ATC 10/13/1997 ADDEQ 2100
2 AL ATC 10/13/1997 BI 1901
2 AL ATC 10/13/1997 COLL 2103
2 AL ATC 10/13/1997 COMP 2101
2 AL ATC 10/13/1997 MEDPAY 2801
2 AL ATC 10/13/1997 PD 2001
Loading session:
Code:
SQL> $sqlldr scott/tiger control=test1.ctl log=test1.log

SQL*Loader: Release 11.2.0.2.0 - Production on Pet Svi 4 19:49:13 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 5
Commit point reached - logical record count 6

SQL>
The result:
Code:
SQL> select * from linecvg_lookup;

    L2COCD L2 L2P L2RRDT     L2CVCD     SSLNCV
---------- -- --- ---------- ------ ----------
         2 AL ATC 13.10.1997 ADDEQ        2100
         2 AL ATC 13.10.1997 BI           1901
         2 AL ATC 13.10.1997 COLL         2103
         2 AL ATC 13.10.1997 COMP         2101
         2 AL ATC 13.10.1997 MEDPAY       2801
         2 AL ATC 13.10.1997 PD           2001

6 rows selected.

SQL>
Log file excerpt:
Code:
Table LINECVG_LOOKUP, loaded from every logical record.
Insert option in effect for this table: TRUNCATE

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
L2COCD                              FIRST     *  WHT      CHARACTER            
L2SABB                               NEXT     *  WHT      CHARACTER            
L2PRCD                               NEXT     *  WHT      CHARACTER            
L2RRDT                               NEXT     *  WHT      CHARACTER            
    SQL string for column : "TO_DATE(:L2RRDT,'MM/DD/YYYY')"
L2CVCD                               NEXT     *  WHT      CHARACTER            
SSLNCV                               NEXT     *  WHT      CHARACTER            


Table LINECVG_LOOKUP:
  6 Rows successfully loaded.
  0 Rows 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.


Space allocated for bind array:                  99072 bytes(64 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          1
Total logical records read:             6
Total logical records rejected:         0
Total logical records discarded:        0
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Jan 2010
Posts: 29
Hello

Thanks for your reply,I don't know why it is not working fine for me

here is my modified control file:

options (skip = 1)
LOAD DATA
INFILE '\\OHLEWNAS0240\THIDWLOAD\line_cvg_project\linecvg 1.txt'
BADFILE '\\OHLEWNAS0240\THIDWLOAD\line_cvg_project\linecvg 1.bad'
DISCARDFILE '\\OHLEWNAS0240\THIDWLOAD\line_cvg_project\linecvg 1.dsc'
replace
INTO TABLE DATUSER.LINECVG_LOOKUP
TRUNCATE
FIELDS TERMINATED BY ' '
(L2COCD ,
L2SABB ,
L2PRCD ,
L2RRDT "TO_DATE(:L2RRDT,'MM/DD/YY')",
L2CVCD ,
SSLNCV )



Error:


Record 1: Rejected - Error on table DATUSER.LINECVG_LOOKUP, column L2SABB.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 2: Rejected - Error on table DATUSER.LINECVG_LOOKUP, column L2SABB.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 3: Rejected - Error on table DATUSER.LINECVG_LOOKUP, column L2SABB.


please help me!!


Thanks
Hasya
Reply With Quote
  #4 (permalink)  
Old
Lost Boy
 
Join Date: Jan 2004
Location: Croatia, Europe
Posts: 3,995
Oracle says
Quote:
(use TRAILING NULLCOLS)
Did you try it?
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Jan 2010
Posts: 29
Thanks littelfoot for your reply,

I tried TRAILING NULLCOLS,Now it is throwing error as

Record 1: Rejected - Error on table DATUSER.LINECVG_LOOKUP, column L2COCD.
ORA-01722: invalid number

Record 2: Rejected - Error on table DATUSER.LINECVG_LOOKUP, column L2COCD.
ORA-01722: invalid number


Please suggest me where i am going wrong

Thanks
Hasya
Reply With Quote
  #6 (permalink)  
Old
Lost Boy
 
Join Date: Jan 2004
Location: Croatia, Europe
Posts: 3,995
I don't know. I created a table using CREATE TABLE you provided. I used sample data you provided. I fixed a control file, just a little bit.

It works for me (on 11g XE), it doesn't work for you. Which database do you use, anyway?
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Jan 2010
Posts: 29
Hello Littelefoot,

I am using Oracle10g

Does it depends which version i am using,And do i need change quotes or something to accept the data because error is


Record 1: Rejected - Error on table DATUSER.LINECVG_LOOKUP, column L2COCD.
ORA-01722: invalid number


Thanks in advance

Hasya
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
Join Date: Mar 2010
Location: Vienna, Austria
Posts: 149
Your problem seems to be the date conversion string:

Code:
L2RRDT "TO_DATE(:L2RRDT,'MM/DD/YY')",
but the date format you have is

Code:
2 AL ATC 10/13/1997 COLL 2103
So, if you change the line in the loader control file to

Code:
L2RRDT "TO_DATE(:L2RRDT,'MM/DD/YYYY')",
it should work
__________________
"There is always an easy solution to every problem - neat, plausible, and wrong."
-- H.L. Mencken
Reply With Quote
  #9 (permalink)  
Old
Lost Boy
 
Join Date: Jan 2004
Location: Croatia, Europe
Posts: 3,995
What a sharp eye you have!

I used YYYY format mask in my control file; didn't pay attention to Hasya's ... shame on me!
Reply With Quote
  #10 (permalink)  
Old
Registered User
 
Join Date: Jan 2010
Posts: 29
Thanks for your sugesstion,

But still i am getting the same error


i have changed the control file year format,in the sql ldr it showed Commit point reached 64(i have 60,000 records ),But no data in the table and i got the below error



And also in the log file L2COCD is showing as character but it is number,Do i need to change anything there?


Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
L2COCD FIRST * WHT O(') CHARACTER
L2SABB NEXT * WHT O(') CHARACTER
L2PRCD NEXT * WHT O(') CHARACTER
L2RRDT NEXT * WHT O(') CHARACTER
SQL string for column : "TO_DATE(:L2RRDT,'MM/DD/YYYY')"
L2CVCD NEXT * WHT O(') CHARACTER
SSLNCV NEXT * WHT O(') CHARACTER

Record 1: Rejected - Error on table DATUSER.LINECVG_LOOKUP, column L2COCD.
ORA-01722: invalid number

Record 2: Rejected - Error on table DATUSER.LINECVG_LOOKUP, column L2COCD.
ORA-01722: invalid number

Last edited by hasya05; 05-07-12 at 12:07.
Reply With Quote
  #11 (permalink)  
Old
Registered User
 
Join Date: Mar 2010
Location: Vienna, Austria
Posts: 149
hasya,

I'm not sure, about the 10g sqlldr behavior on implicit type conversions (my installation also is a 11gR2).

Just to prove, try

Code:
(L2COCD  "TO_NUMBER(:L2COCD)",
Btw: you know, you have to "skip" the first line of your data, don't you ?
__________________
"There is always an easy solution to every problem - neat, plausible, and wrong."
-- H.L. Mencken
Reply With Quote
  #12 (permalink)  
Old
Registered User
 
Join Date: Jan 2010
Posts: 29
Hello Magicwand,

Thanks for helping,

But no luck....still getting the same error,But in the sqlldr it is showing

commit point reached - logical record count 64....

OMG i am not understanding what's happening...


Yes i have to skip first record they are column names...

here is my log file:

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
L2COCD FIRST * WHT O(') CHARACTER
SQL string for column : "TO_NUMBER(:L2COCD)"
L2SABB NEXT * WHT O(') CHARACTER
L2PRCD NEXT * WHT O(') CHARACTER
L2RRDT NEXT * WHT O(') CHARACTER
SQL string for column : "TO_DATE(:L2RRDT,'MM/DD/YYYY')"
L2CVCD NEXT * WHT O(') CHARACTER
SSLNCV NEXT * WHT O(') CHARACTER

Record 1: Rejected - Error on table DATUSER.LINECVG_LOOKUP, column L2COCD.
ORA-01722: invalid number

Record 2: Rejected - Error on table DATUSER.LINECVG_LOOKUP, column L2COCD.
ORA-01722: invalid number


Thanks
Hasya
Reply With Quote
  #13 (permalink)  
Old
Lost Boy
 
Join Date: Jan 2004
Location: Croatia, Europe
Posts: 3,995
Could you ATTACH a few sample records from your file. Do not copy/paste them into a message (we already have that), attach a TXT file.
Reply With Quote
  #14 (permalink)  
Old
Registered User
 
Join Date: Jan 2010
Posts: 29
hello Littelefoot,

I have attached few records from my data...


Thank you very much for your help
Hasya
Attached Files
File Type: txt linecvg1.txt (5.1 KB, 7 views)
Reply With Quote
  #15 (permalink)  
Old
Registered User
 
Join Date: Jan 2010
Posts: 29
waiting for your reply...
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