| SQL Loader to fail in Warning and send email notifications [message #616427] |
Mon, 16 June 2014 16:32  |
 |
reddy0422
Messages: 10
Registered: June 2014
|
Junior Member |
|
|
Hi - I have a requirement that the SQL*Loader Program should end in warning if all the data from the flat file not inserted into the staging table and send e-mail notification to the user that all the rows are not imported and these are the errors(I know the SQL Log file shows us) but the process should do that automatically.
Any thoughts or inputs on this.
Thanks,
|
|
|
|
| Re: Sql * Loader [message #616429 is a reply to message #616427] |
Mon, 16 June 2014 16:45  |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
Firstly, what is your DB version and OS?
Sqlldr has functionality to return exit code. Sending an email based on an action/reaction of a program depends completely on you and your code. Isn't it? And what's the problem using the log file to interpret what went wrong with the loading process?
Your question is like, "So I have a car, it doesn't start, I know that a fine manual is provided with it, but I need my car to send me message on my phone about the problem".
|
|
|
|
|
|
|
|
| Re: Sql * Loader [message #616432 is a reply to message #616431] |
Mon, 16 June 2014 17:01 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
reddy0422 wrote on Tue, 17 June 2014 03:23
The SQL * Loader Concurrent Program will be scheduled on the regular basis,so when the user has to get the email notifications and the program should be end in Warning when all the rows in the data file are not loaded into the staging table.
How to use this as you said Sqlldr has functionality to return exit code,can we do that using the control file.
Did you follow my comment above? Did you look into the documentation about exit code? Even Barbara suggested you here https://community.oracle.com/message/12489859#12489859
An ideal solution IMO is to log the errors in an error logging table, or read the sqlldr log file for any errors, and use the logged details to send an email to users via SMTP, an example here https://community.oracle.com/message/12489859#12489859. Certain OS has the capability to send email with log file details too.
If you post the details to let us know what you have tried so far, I/someone in the forum would help you.
|
|
|
|
| Re: Sql * Loader [message #616433 is a reply to message #616431] |
Mon, 16 June 2014 17:05 |
 |
EdStevens
Messages: 1376
Registered: September 2013
|
Senior Member |
|
|
Your comment about the control file makes it look like you expect sqlloader itself to send the email. sqlldr has one job .. load data from a file into a table. Period.
AFTER sqlldr has completed you can either query the exit code or the log file. These are issues that are entirely about writing shell scripts and nothing to do with the use of sqlldr.
Sqlldr exit codes are documented at http://docs.oracle.com/cd/E11882_01/server.112/e22490/ldr_params.htm#SUTIL3324
Since this is purely a question of shell script programming, you would be better served at http://www.unix.com/shell-programming-and-scripting/
YOu will need to learn how to interrogate a program's exit code and/or get a return value from 'grepping' a file for a known string; you will also need to learn how to use your linux system's 'mail' utility in a batch mode.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
