Solved

Pass filenames to sql ldr at run time

Posted on 2011-03-18
14
414 Views
Last Modified: 2012-05-11
I need to write a shell script to grab several filenames and pass them to sql ldr as the data files.  My files and control file will be in the same directory.  Also, will sql ldr create .bad and .dsc files on its own based on my input files, or do I need to specify?  I will have more than one infile at once.
Thanks so much for any help you can give.
0
Comment
Question by:newtoperlpgm
  • 7
  • 7
14 Comments
 
LVL 5

Expert Comment

by:paulqna
ID: 35172014
for file in `ls /path/to/input/files/*.csv`
do sqlldr user/password control='control.file',log='${file}.log',data='${file}',bad='${file}.bad',discard='${file}.discard'
done

Open in new window



Of course "/path/to/input/files/*.csv" can be changed to anything matching your requirement.
0
 
LVL 5

Expert Comment

by:paulqna
ID: 35172021
and you can add this after each sqlldr execution to show in one line if the file was successfully processed:
retcode=`echo $?` 
case "$retcode" in 
0) echo "SQL*Loader execution successful" ;; 
1) echo "SQL*Loader execution exited with EX_FAIL, see logfile" ;; 
2) echo "SQL*Loader execution exited with EX_WARN, see logfile" ;; 
3) echo "SQL*Loader execution encountered a fatal error" ;; 
*) echo "unknown return code";; 
esac

Open in new window

0
 

Author Comment

by:newtoperlpgm
ID: 35182353
thanks so much I am going to try it now, hope it works.
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

Author Comment

by:newtoperlpgm
ID: 35182881
can I just ask what esac means?  Thanks!
0
 
LVL 5

Expert Comment

by:paulqna
ID: 35184111
esac is the end of the case "statement" just like if/then/fi or for/do/done.

See also here.
0
 

Author Comment

by:newtoperlpgm
ID: 35193736
say, can you show me how to throw that retcode portion at the bottom of the code into an email, as this will be an automated process and I want to email the result to myself.  Also, since it's automated, no one will watch see it on the screen.  Thanks.
0
 

Author Comment

by:newtoperlpgm
ID: 35193990
Will this work?  

retcode=`echo $?`
case "$retcode" in
0) echo "SQL*Loader execution successful" | mail address@server.com ;;
1) echo "SQL*Loader execution exited with EX_FAIL, see logfile" mail address@server.com ;;
2) echo "SQL*Loader execution exited with EX_WARN, see logfile" mail address@server.com ;;
3) echo "SQL*Loader execution encountered a fatal error"  mail address@server.com ;;
*) echo "unknown return code" mail address@server.com;;
esac
0
 
LVL 5

Expert Comment

by:paulqna
ID: 35194030
This code will also attach the logfile for each import to the email message.
(and it wont output to screen anymore, as requested)
(assuming mailx is working on your server)
(and it will mail to the user as defined in MY_MAILUSR)

MY_MAILUSR="user@server.domain"
for file in `ls /path/to/input/files/*.csv`
do sqlldr user/password control='control.file',log='${file}.log',data='${file}',bad='${file}.bad',discard='${file}.discard'
     retcode=`echo $?` 
     case "$retcode" in 
     0) mailx -s "${file}: SQL*Loader execution successful" $MY_MAILUSR < ${file}.log;; 
     1) mailx -s "${file}: SQL*Loader execution exited with EX_FAIL, see logfile" $MY_MAILUSR < ${file}.log;; 
     2) mailx -s "${file}: SQL*Loader execution exited with EX_WARN, see logfile" $MY_MAILUSR < ${file}.log;; 
     3) mailx -s "${file}: SQL*Loader execution encountered a fatal error" $MY_MAILUSR < ${file}.log;; 
     *) mailx -s "${file}: unknown return code" $MY_MAILUSR < ${file}.log;; 
esac
done

Open in new window

0
 

Author Comment

by:newtoperlpgm
ID: 35194089
never mind, I got it to work with the above emailing the echo response, unless you can recommend a better way to do this. Thanks!
0
 
LVL 5

Expert Comment

by:paulqna
ID: 35194097
I did already! (?)
0
 

Author Comment

by:newtoperlpgm
ID: 35208219
Hey thanks, that worked great.  I want to move my data files that were uploaded into ORacle to another directory,  

will the following work?
MY_MAILUSR="user@server.domain"
for file in `ls /path/to/input/files/*.csv`
do sqlldr user/password control='control.file',log='${file}.log',data='${file}',bad='${file}.bad',discard='${file}.discard'
     retcode=`echo $?`
     case "$retcode" in
     0) mailx -s "${file}: SQL*Loader execution successful" $MY_MAILUSR < ${file}.log;;
       mv *.txt loadedfilesdirectory
     1) mailx -s "${file}: SQL*Loader execution exited with EX_FAIL, see logfile" $MY_MAILUSR < ${file}.log;;
     2) mailx -s "${file}: SQL*Loader execution exited with EX_WARN, see logfile" $MY_MAILUSR < ${file}.log;;
     3) mailx -s "${file}: SQL*Loader execution encountered a fatal error" $MY_MAILUSR < ${file}.log;;
     *) mailx -s "${file}: unknown return code" $MY_MAILUSR < ${file}.log;;
esac
done
0
 
LVL 5

Accepted Solution

by:
paulqna earned 250 total points
ID: 35208596
not that has a syntax error and is not the recommended way, and you want to avoid reloading failed files again and again also:

MY_MAILUSR="user@server.domain"
for file in `ls /path/to/input/files/*.csv`
do sqlldr user/password control='control.file',log='${file}.log',data='${file}',bad='${file}.bad',discard='${file}.discard'
     retcode=`echo $?` 
     case "$retcode" in 
     0) mailx -s "${file}: SQL*Loader execution successful" $MY_MAILUSR < ${file}.log
       mv ${file} success_directory;;
     1) mailx -s "${file}: SQL*Loader execution exited with EX_FAIL, see logfile" $MY_MAILUSR < ${file}.log
       mv ${file} fail_directory;;
     2) mailx -s "${file}: SQL*Loader execution exited with EX_WARN, see logfile" $MY_MAILUSR < ${file}.log
       mv ${file} fail_directory;;
     3) mailx -s "${file}: SQL*Loader execution encountered a fatal error" $MY_MAILUSR < ${file}.log
       mv ${file} fail_directory;;
     *) mailx -s "${file}: unknown return code" $MY_MAILUSR < ${file}.log
       mv ${file} fail_directory;;
esac
done

Open in new window

0
 

Author Closing Comment

by:newtoperlpgm
ID: 35209336
I learned a lot and was able to accomplish my task
0
 
LVL 5

Expert Comment

by:paulqna
ID: 35209467
Both reasons were worth helping you for  :-)
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Attention: This article will no longer be maintained. If you have any questions, please feel free to mail me. jgh@FreeBSD.org Please see http://www.freebsd.org/doc/en_US.ISO8859-1/articles/freebsd-update-server/ for the updated article. It is avail…
Using libpcap/Jpcap to capture and send packets on Solaris version (10/11) Library used: 1.      Libpcap (http://www.tcpdump.org) Version 1.2 2.      Jpcap(http://netresearch.ics.uci.edu/kfujii/Jpcap/doc/index.html) Version 0.6 Prerequisite: 1.      GCC …
Learn how to navigate the file tree with the shell. Use pwd to print the current working directory: Use ls to list a directory's contents: Use cd to change to a new directory: Use wildcards instead of typing out long directory names: Use ../ to move…
In a previous video, we went over how to export a DynamoDB table into Amazon S3.  In this video, we show how to load the export from S3 into a DynamoDB table.

823 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question