Solved

Pass filenames to sql ldr at run time

Posted on 2011-03-18
14
426 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

 

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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 …
FreeBSD on EC2 FreeBSD (https://www.freebsd.org) is a robust Unix-like operating system that has been around for many years. FreeBSD is available on Amazon EC2 through Amazon Machine Images (AMIs) provided by FreeBSD developer and security office…
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 get help with Linux/Unix bash shell commands. Use help to read help documents for built in bash shell commands.: Use man to interface with the online reference manuals for shell commands.: Use man to search man pages for unknown command…

690 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