Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

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

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Unix script for removing the footer from the datafile while loading into externaltable using sqlload
# 1  
Old 06-12-2012
Unix script for removing the footer from the datafile while loading into externaltable using sqlload
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
Code:
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
Last edited by fpmurphy; 06-12-2012 at 08:26 AM.. Reason: added code tags, removed size,color, etc.
# 2  
Old 06-12-2012
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)
Code:
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

2. check for prefix of record (ex if footer is like TRLR*****) and use WHEN clause
Code:
 
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  
Old 06-12-2012
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  
Old 06-12-2012
if you put those details in environment variable then -
Code:
#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  
Old 06-12-2012
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  
Old 06-12-2012
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-

Code:
export fileheader=2
export filefooter=2

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Advanced & Expert Users

Script to automate add/resize datafile in Oracle ASM Storage

Hi all, Could anyone please share ideas or logic on how to automate add/resize datafile in Oracle ASM Storage for Oracle RAC/Single Node databases when any tablespace space alert is trigerred by OEM? Thanks for your help and time on it! Thanks&regards, a1_win Please DON'T use CODE tags... (0 Replies)
Discussion started by: a1_win
0 Replies

2. Shell Programming and Scripting

How to avoid locks while loading database through UNIX script.?

Hi, I have the following requirement: I have load_cdr as main scriptwhich loads all the file records into vertica database through unix script. But there are two files which try to update same table..n thats y my script is getting failed... Can any1 give me some logic how to over come this... (6 Replies)
Discussion started by: gnnsprapa
6 Replies

3. Shell Programming and Scripting

Removing header or footer from file

Hi Every one, what is the coomand to remove header or footer from a file. Please help me by providing command/syntax to remove header/footer from unix. Thanks in advance for all your support. (5 Replies)
Discussion started by: sridhardwh
5 Replies

4. Shell Programming and Scripting

Add header and footer with record count in footer

This is my file(Target.txt) name|age|locaction abc|23|del xyz|24|mum jkl|25|kol The file should be like this 1|03252012 1|name|age|location 2|abc|23|del 2|xyz|24|mum 2|jkl|25|kol 2|kkk|26|hyd 3|4 Column 1 is row indicator for row 1 and 2, column indicator is 1,for data rows... (1 Reply)
Discussion started by: itsranjan
1 Replies

5. Shell Programming and Scripting

Removing header and footer

I have two files which are getting sent to a UNIX server in order to be bcp'd into a database. The bcp is failing because there's a header and footer row on the file which give the date of the file and the number of rows in it. That's because the file is also being used for another process, so we... (1 Reply)
Discussion started by: Tom Sawyer
1 Replies

6. Shell Programming and Scripting

sorting the datafile in an order given in second datafile

Hi, I have two files: first input file is having 7-8 columns, and second data file is like I want to arrange my datafile1 in the order given in second data file, by comparing the seconddatafile with the second column of first file and print the entire line....also if any... (2 Replies)
Discussion started by: CAch
2 Replies

7. Shell Programming and Scripting

unix script for loading a data from a file into database

Hi All, I am trying to load a data from a files in a particular directory to a database.. cd $SCC isql metdb >> ${LOGDATA}/LOAD.LOG < ! load from "${LDATA}/${FORM}.ld" insert into $LOADTABLE ! But it's showing the error "syntax error at line 46 : `<<' unmatched" Can u plz help me... (5 Replies)
Discussion started by: grajesh_955
5 Replies

8. Shell Programming and Scripting

A footer for shell script

Hi My question might sound crazy, but nevertheless, I have made mu mind to ask. Please ignore if I sound absurd. I have a shell script. When run, this script, prints out a menu to the user and takes the input from the user. It works fine. I just wondered, is it possible to have a 'footer'... (2 Replies)
Discussion started by: guruparan18
2 Replies

9. UNIX for Dummies Questions & Answers

sqlload not found error

When i try to run a job in unix, i am getting sqlload error as, sqlload : file not found. Can any one please let me know whats the reason for this.. The PATH & ORACLE_HOME paths names are correct... thanks:confused: hr is the script: here is the script : plz hv a look TABLE_NAME=... (2 Replies)
Discussion started by: abhi_123
2 Replies

10. Shell Programming and Scripting

Combine a datafile with Master datafile, emergent!

Hi guys, my supervisor has asked me to solve the problem in 7 days, I've taken 3 days to think about it but couldn't figure out any idea. Please give me some thoughts with the following problem, I have index.database that has only index date: 1994 1995 1996 1997 1998 1999 I have... (6 Replies)
Discussion started by: onthetopo
6 Replies
Login or Register to Ask a Question