Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL Loader -524 error

Posted on 2011-04-28
12
Medium Priority
?
489 Views
Last Modified: 2012-12-22
I am getting SQL*Loader-524:
partial record found at end of datafile
when trying to import data from a data file into an Oracle table using SQL Loader.  

There is nothing at the end of my file, there aren't any spaces of returns after my last column of data, anyone know how I can avoid this?
Thanks.
0
Comment
Question by:newtoperlpgm
  • 4
  • 3
  • 3
10 Comments
 
LVL 5

Expert Comment

by:jason987
ID: 35487641
Put a return after the last row.
0
 

Author Comment

by:newtoperlpgm
ID: 35487701
That was the first thing I tried, but that did not work.  Any other suggestions?
Thanks!
0
 
LVL 5

Expert Comment

by:jason987
ID: 35487825
Try copy/pasting just the contents into a new file.  

Convert the file to unix format.

Make sure your syntax is 100% correct.  

Verify a known good file and compare it to yours.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35488266
Do you have trailing null columns set in the control file?

Can you post your controlfile and sample data that produces the error so we can reproduce the problem and provide a solution?
0
 

Author Comment

by:newtoperlpgm
ID: 35492789
I do have trailing null columns set in the control file.

I believe it is having a problem with the % as data in one of the columns.
How to get around this?
Thanks.

BRCODE|RECT|LOG_DATE|SAM_TYPE|SAM_TA|SAM_BLDG
224044|0|04/25/2011 10:55|Air Sample|TA-0|AIrNet
BRCODE|RES_ID|ACT_UNIT|ERR_UNIT|LD_UNIT|LD|DET|ISO|ACT|ERROR
224044|3|Ci|%|Ci||FALSE|NONE||
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35492805
Can you post the controlfile?

I'll set up a test case on my database and provide working code or at least a fix.
0
 
LVL 5

Assisted Solution

by:jason987
jason987 earned 1000 total points
ID: 35492847
Escape it?

BRCODE|RECT|LOG_DATE|SAM_TYPE|SAM_TA|SAM_BLDG
224044|0|04/25/2011 10:55|Air Sample|TA-0|AIrNet
BRCODE|RES_ID|ACT_UNIT|ERR_UNIT|LD_UNIT|LD|DET|ISO|ACT|ERROR
224044|3|Ci|\%|Ci||FALSE|NONE||

http://download.oracle.com/docs/cd/B14117_01/server.101/b10825/ldr_control_file.htm#i1006645
0
 
LVL 5

Expert Comment

by:jason987
ID: 35492863
Hmm actually it seems like quoting it is the oracle way.
0
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1000 total points
ID: 35492913
The '%' shouldn't be any problem.

Until I get your specs, here's what I just loaded using 10.2.0.3.

After:
SQL> select * from tab1;

    BRCODE     RES_ID AC E LD L DET   ISO  A
---------- ---------- -- - -- - ----- ---- -
ERROR
-----------------------------------------------------------------
    224044          3 Ci % Ci   FALSE NONE
drop table tab1 purge;
create table tab1(
BRCODE number,
RES_ID number,
ACT_UNIT char(2),
ERR_UNIT char(1),
LD_UNIT char(2),
LD	char(1),
DET char(5),
ISO char(4),
ACT char(1),
ERROR char(1)
);

Open in new window

load data
 infile *
 truncate INTO TABLE Tab1
 FIELDS TERMINATED BY '|' TRAILING NULLCOLS
(
BRCODE,
RES_ID,
ACT_UNIT,
ERR_UNIT,
LD_UNIT,
LD	char(1),
DET,
ISO,
ACT,
ERROR
)
begindata
224044|3|Ci|%|Ci||FALSE|NONE||

Open in new window

0
 

Assisted Solution

by:newtoperlpgm
newtoperlpgm earned 0 total points
ID: 35493022
I planned to escape the character % bit it will no longer be used as a data value, pct will be used instead.  It turned out it was the problem.  In my dev environment sql loader version is 8.0.5 so that may be why.  
Thanks for all your help.
0

Featured Post

Technology Partners: 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

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
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.
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…
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
Suggested Courses

581 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