Unix script for removing the footer from the datafile while loading into externaltable using sqlload

Shell Programming and Scripting
1

Hi,

Can you please tell me the Unix shell script for removing footer from the datafile while loading into external table using sqlloader

we will be skipping the header like this while creating the table

organization external
(
type oracle_loader
default directory directoryname
access parameters
(
records delimited by newline
skip 2
badfile 'datafile.txt.bad'
nodiscardfile 
nologfile
fields
(

what we can do to remove the footer

2

there are two ways to do it.

  1. Pass the total no of loads while invoking SQL Loader. (Count the actual no of records from input file and pass it)
TOTAL_COUNT=`wc -l $FILE_NAME
TOTAL_COUNT=`expr $TOTAL_COUNT - <total no of headers and trailers>` 
sqlldr -control=$CTL_FILE -load=$TOTAL_COUNT -log $LOG_FILE -userid $USER_ID <$PSWDFILE >> $output_file
  1. check for prefix of record (ex if footer is like TRLR*****) and use WHEN clause
 
OPTIONS (DIRECT=FALSE,ERRORS=999)
LOAD DATA
INFILE '_INPUT_DIR/INPUT_FILE'
BADFILE '_LOG_DIR/FILE_NAME.bad'
DISCARDFILE '_LOG_DIR/FILE_NAME.dis'
DISCARDMAX 999

INTO TABLE table_name INSERT
WHEN ( (1:4) != 'TRLR' ) 
(
attr1 
attr2
)

---------- Post updated at 05:04 PM ---------- Previous update was at 05:01 PM ----------

I would suggest for first option. Its straight forward. Just you need to count the total no of records and pass it to sql loader.

3

Hi,

Issue is like i will be having a config file where i will have the details like

1.fileheader:2
2.filefooter:2
3.Filelocation:/......../

all these i will take these details and need to remove the footer and onething is like these details are not constant it may varied

so how the first solution will work according to this condition

can you please let me know

4

if you put those details in environment variable then -

#count the total records from file includes header and footer also
TOTAL_COUNT=`wc -l $FILE_NAME`

#Now subtract the count of footer and header(this gives only the actual records which needs to be loaded)
#As already you have defined fileheader and filefooter in config file 
TOTAL_COUNT=`expr $TOTAL_COUNT - $fileheader - $filefooter` 

#pass total load count to sql loader 
sqlldr -control=$CTL_FILE -load=$TOTAL_COUNT -log $LOG_FILE -userid $USER_ID <$PSWDFILE >> $output_file

ex. suppose you have 15 records including 2 headers and 2 footers.
so total load comes 15 - 2 - 2 = 11
In control file you have declared SKIP=2, that means it will skip those headers. Now it will load only 11 record starting from 3rd record and will leave your last 2 records because it can load only 11 records. And those last 2 records are footers.

5

Hi,
i understood the script you have sent but i have a config file which is sent to us
which have the details
1.fileheader=2
2.filefooter=2
3.location:/scripts/filename.txt ---this file where we are have the records including the header and records and footer

TOTAL_COUNT=`wc -l $FILE_NAME` -- what i need to mention here which filename

TOTAL_COUNT=`expr $TOTAL_COUNT - $fileheader - $filefooter` ---this fileheader and filefooter comes from only config file but we are not reading config file here

6

FILE_NAME is the data file which will have records and will be loaded to database by using sql loader.

If you are not executing the config file then what is the use of config file? Before you come to sql loader portion, you need to execute that file. Use export command into that then only your shell will be reading that. like-

export fileheader=2
export filefooter=2