Home » RDBMS Server » Server Utilities » SQL Loader to fail in Warning and send email notifications
SQL Loader to fail in Warning and send email notifications [message #616427] Mon, 16 June 2014 16:32 Go to next message
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 Go to previous messageGo to next message
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 to fail in Warning and send email notifications [message #616430 is a reply to message #616427] Mon, 16 June 2014 16:47 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Why duplicate topics? Isn't it similar to http://www.orafaq.com/forum/t/193198/
Re: Sql * Loader [message #616431 is a reply to message #616429] Mon, 16 June 2014 16:53 Go to previous messageGo to next message
reddy0422
Messages: 10
Registered: June 2014
Junior Member
DB Version -->11.2.0.3.0
OS-->Linux

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.
Please let me know if any questions.
Re: Sql * Loader [message #616432 is a reply to message #616431] Mon, 16 June 2014 17:01 Go to previous messageGo to next message
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 Go to previous message
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.
Previous Topic: .xls file into table by using sqlloader
Next Topic: EXPDP getting error ORA-39002
Goto Forum:
  


Current Time: Thu Mar 28 03:27:57 CDT 2024