Example: you have a text file ( fixed delimited ) and you want to load it with sql loader ( sqlldr command ) . The problem is that this file has a header and a footer
To skip the first line from the source text file is simple ( SKIP=1 ) .. for example you have a header in the file
To skip the last line from the source text file , use the following workaround :
http://oracle.ittoolbox.com/groups/technical-functional/oracle-dev-l/how-to-skip-the-trailor-line-using-sql-loader-635925
Usually, when there is a trailer, there is some sort of record type
indicator. Use this in a condition of loading (I believe it is a WHEN in
SQLLOADER). Suppose your record indicator is in the first position, and is
H[eader], D[etail], T[railer]. You already probably used SKIP to skip the
header, but you can accomplish both by
WHEN (1:1)='D'
There is a pseudocolumn in SQLLOADER that shows the record number; look in
the manual; I believe is it RECORD.
Warning :
http://www.orafaq.com/wiki/SQL*Loader_FAQ
SQL*Loader does not allow the use of
OR in the WHEN clause. You can only use
AND as in the example above! To workaround this problem, code multiple "INTO TABLE ... WHEN" clauses. Here is an example:
LOAD DATA
INFILE 'mydata.dat' BADFILE 'mydata.bad' DISCARDFILE 'mydata.dis'
APPEND
INTO TABLE my_selective_table
WHEN (01) <> 'H' and (01) <> 'T'
(
region CONSTANT '31',
service_key POSITION(01:11) INTEGER EXTERNAL,
call_b_no POSITION(12:29) CHAR
)
INTO TABLE my_selective_table
WHEN (30:37) = '20031217'
(
region CONSTANT '31',
service_key POSITION(01:11) INTEGER EXTERNAL,
call_b_no POSITION(12:29) CHAR)
