| Skip Trailer in SQL Loader [message #73967] |
Thu, 12 August 2004 01:52  |
sameep
Messages: 1
Registered: August 2004
|
Junior Member |
|
|
Hi All,
I am using SQL Loader to load the data file containing the header and the trailor. I am able to skip the header lines using skip keyword. But I am not sure how to avoid the last few lines (which are part of trailor) from getting inserted into the database? Is there any way to skip these trailing section?
Also can we get the line number of the record during loading is being processed.
Thanks in advance,
Sameep
|
|
|
|
|
|
| Re: Skip Trailer in SQL Loader [message #117368 is a reply to message #73967] |
Tue, 26 April 2005 14:20  |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
I don't know how to explicitly skip a trailer like you would with the skip on the header, unless you know the number of rows in the file you want to load, but you could always use a when clause to check the format and not load it if the format matches the trailing format (or load it if it doesn't match).
There is a load parameter which says how many records to load if that helps.
Also, there is a pseudo column for something that acts like a rownum. Check out RECNUM and SEQUENCE depending on your need.
|
|
|
|
| Re: Skip Trailer in SQL Loader [message #117370 is a reply to message #117368] |
Tue, 26 April 2005 14:28 |
 |
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
as martin said, a little scripting would help.
or you need to have a special record as trailer record and use a when clause.
bash-2.03$ skip_rec
Enter the datfile to be loaded
dept.dat
SQL*Loader: Release 9.2.0.4.0 - Production on Tue Apr 26 14:18:19 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Commit point reached - logical record count 4
bash-2.03$ cat skip_rec
#!/usr/bin/bash
echo "Enter the datfile to be loaded"
read DF
LN=$((`wc -l $DF | awk '{print $1'}` - 1 ))
sqlldr userid=scott/tiger control=dept.ctl load=$LN
bash-2.03$ cat dept.ctl
LOAD DATA
infile 'dept.dat'
replace INTO TABLE dept FIELDS TERMINATED BY "," trailing nullcols
(
deptno,
dname,
loc
)
bash-2.03$ cat dept.dat
10,ACCOUNTING,NEW YORK
20,RESEARCH,DALLAS
30,SALES,CHICAGO
40,OPERATIONS,BOSTON
trailerrecord
bash-2.03$ query mutation scott.dept
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
