?
Solved

SQL LOADER EMPTY ROW

Posted on 2011-03-10
14
Medium Priority
?
3,736 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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 2000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses

762 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