Solved

SQL LOADER EMPTY ROW

Posted on 2011-03-10
14
3,056 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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Cannot open form error 6 63
Identify records which do NOT qualify for a view 9 36
Maintaining Oracle Managed Accounts 2 29
SQL Query 34 79
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
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 …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to take different types of Oracle backups using RMAN.

939 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

11 Experts available now in Live!

Get 1:1 Help Now