loading txt file from sql loader to oracle database

hasya05 · #1 (**permalink**) 05-04-12, 11:52

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

Littlefoot · #2 (**permalink**) 05-04-12, 13:50

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

hasya05 · #3 (**permalink**) 05-06-12, 00:54

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

Littlefoot · #4 (**permalink**) 05-06-12, 03:57

Oracle says

Quote:

(use TRAILING NULLCOLS)

Did you try it?

hasya05 · #5 (**permalink**) 05-06-12, 19:49

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

Littlefoot · #6 (**permalink**) 05-07-12, 02:45

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?

hasya05 · #7 (**permalink**) 05-07-12, 09:37

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

magicwand · #8 (**permalink**) 05-07-12, 10:31

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

Littlefoot · #9 (**permalink**) 05-07-12, 11:52

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!

hasya05 · #10 (**permalink**) 05-07-12, 12:02

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

magicwand · #11 (**permalink**) 05-07-12, 18:18

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 ?

hasya05 · #12 (**permalink**) 05-07-12, 21:45

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