Solved

SQL LOADER EMPTY ROW

Posted on 2011-03-10
14
2,968 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
  • 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
 

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to recover a database from a user managed backup

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now