Solved

Pass filenames to sql ldr at run time

Posted on 2011-03-18
14
407 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
 

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Installing FreeBSD… FreeBSD is a darling of an operating system. The stability and usability make it a clear choice for servers and desktops (for the cunning). Savvy?  The Ports collection makes available every popular FOSS application and packag…
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 several ways to interact with files and get file information from the bash shell. ls lists the contents of a directory: Using the -a flag displays hidden files: Using the -l flag formats the output in a long list: The file command gives us mor…
Learn how to find files with the shell using the find and locate commands. Use locate to find a needle in a haystack.: With locate, check if the file still exists.: Use find to get the actual location of the file.:

705 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now