Solved

Pass filenames to sql ldr at run time

Posted on 2011-03-18
14
423 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
Technology Partners: 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!

 

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

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

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

In tuning file systems on the Solaris Operating System, changing some parameters of a file system usually destroys the data on it. For instance, changing the cache segment block size in the volume of a T3 requires that you delete the existing volu…
My previous tech tip, Installing the Solaris OS From the Flash Archive On a Tape (http://www.experts-exchange.com/articles/OS/Unix/Solaris/Installing-the-Solaris-OS-From-the-Flash-Archive-on-a-Tape.html), discussed installing the Solaris Operating S…
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.:
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…
Suggested Courses

737 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