2011-04-14

how to skip the first and the last record, with sqlldr

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
)

Niciun comentariu:

Trimiteți un comentariu