• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 441
  • Last Modified:

Pass filenames to sql ldr at run time

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
newtoperlpgm
Asked:
newtoperlpgm
  • 7
  • 7
1 Solution
 
paulqnaCommented:
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
 
paulqnaCommented:
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
 
newtoperlpgmAuthor Commented:
thanks so much I am going to try it now, hope it works.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
newtoperlpgmAuthor Commented:
can I just ask what esac means?  Thanks!
0
 
paulqnaCommented:
esac is the end of the case "statement" just like if/then/fi or for/do/done.

See also here.
0
 
newtoperlpgmAuthor Commented:
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
 
newtoperlpgmAuthor Commented:
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
 
paulqnaCommented:
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
 
newtoperlpgmAuthor Commented:
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
 
paulqnaCommented:
I did already! (?)
0
 
newtoperlpgmAuthor Commented:
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
 
paulqnaCommented:
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
 
newtoperlpgmAuthor Commented:
I learned a lot and was able to accomplish my task
0
 
paulqnaCommented:
Both reasons were worth helping you for  :-)
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 7
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now