Solved

SQL LOADER EMPTY ROW

Posted on 2011-03-10
14
3,559 Views
Last Modified: 2012-05-11
Hi,

Below is the control file I am using to load data into the table INFO_STAGE2.  At the end of the load it is creating an empty row, I mean the  first three columns will be empty and it generates sysdate  for DATE column.

Table:

ROW_NUMBER    VARCHAR2(15 byte)
ID                          VARCHAR2(25 byte)
STATUS                VARCHAR2(5 byte)
DATE                     DATE

Control File:
LOAD DATA
INFILE '/usr/summary/Info.txt'
BADFILE '/usr/Scripts/error_info.bad'
DISCARDFILE '/usr/Scripts/error_info.dsc'
APPEND
INTO TABLE INFO_STAGE2
FIELDS TERMINATED BY "|"
TRAILING NULLCOLS
(
    ROW_NUMBER"trim(:ROW_NUMBER)",
    ID"trim(:ID)",
    STATUS"trim(:STATUS)",
    DATE "sysdate"
)

 

Info.txt
0
Comment
Question by:new_perl_user
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
  • 2
14 Comments
 
LVL 5

Expert Comment

by:morgulo
ID: 35099890
I think you should use "REJECT ROWS WITH ALL NULL FIELDS" option or remove last row from the source file.
0
 

Author Comment

by:new_perl_user
ID: 35099924
There is nothing like null row in the source file. And should I implement the "REJECT ROWS WITH ALL NULL FIELDS" statement in the control file
0
 
LVL 5

Expert Comment

by:morgulo
ID: 35100159
Sorry I've missed sample file. You can try remove CR chars (\r). It is possible that loader interprets this pair as \n\n.
0
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 

Author Comment

by:new_perl_user
ID: 35100226
Sorry I did not get you. if possible can you be more specific.
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 35102154
I tested it with the below control file and it works fine and i can load 5 records successfully into info_stage2 table :

LOAD DATA
INFILE 'c:\Info.txt'
BADFILE 'c:\info.bad'
DISCARDFILE 'c:\error_info.dsc'
APPEND
INTO TABLE INFO_STAGE2
FIELDS TERMINATED BY "|"
TRAILING NULLCOLS
(
    ROW_NUMBER"trim(:ROW_NUMBER)",
    ID"trim(:ID)",
    STATUS"trim(:STATUS)",
    DATE1 "sysdate"
)


ee.doc
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 35102160
also this is my log file  :


a.log
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 35102164
can you provide your log file to let us know what is the actual error you are getting ?
0
 

Author Comment

by:new_perl_user
ID: 35109680
Hi,
 I am attaching the log file from sql loder and also the  snap shot of table which shows the empty row. Still not understanding why it is working for you and for me it is loading an empty row. I used the same control file as above.
INFO.log
Table.doc
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 35114722
your log file shows this "136 Rows successfully loaded." i thought only 5 should be there ?

Can you give your full data file not just 5 records for me to test ?
0
 

Author Comment

by:new_perl_user
ID: 35128409
Hi,
Attached is the whole data file I am trying to load. But it is not the problem how many records where there because sometimes I get only 5 records in this data file or only 1 record sometimes.

But regardless of the no of records is always create a nuill row as shown in the above attached table.doc
Info.txt
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 35134617
Hi, i was able to replicate your issue in my test database. that is just because of empty (blank) lines at the end of your data file. Just open the Info.txt (4 KB) which you had attached in the 35128409 update and go to the end of the file and there are two blank lines which is causing  the issue.

i have modified your data file to remove those blank records and attaching it here. just try to load this and you will not encounter the issue.

Thanks

Info-one.txt
0
 

Author Comment

by:new_perl_user
ID: 35137805

Hi,
 How to remove this blank lines in future. I mean I have to load data from these kind of files everyday, so is there any modification I need to do in the control file to overcome this issue.
0
 
LVL 28

Accepted Solution

by:
Naveen Kumar earned 500 total points
ID: 35144105
Can you try this. It works for me fine in not loading the blank records though my data file has blank records.

LOAD DATA
INFILE 'c:\Info_one.txt'
BADFILE 'c:\info_one.bad'
DISCARDFILE 'c:\error_info.dsc'
APPEND
INTO TABLE INFO_STAGE2
when ID <> ' '
FIELDS TERMINATED BY "|"
TRAILING NULLCOLS
(
    ROW_NUMBER "trim(:ROW_NUMBER)",
    ID "trim(:ID)",
    STATUS "trim(:STATUS)",
    DATE1 "sysdate"
)
0
 

Author Comment

by:new_perl_user
ID: 35147831
It worked. Thank you.
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question