OraFAQ Forum: Server Utilities » SQL Loader to fail in Warning and send email notifications

Home » RDBMS Server » Server Utilities » SQL Loader to fail in Warning and send email notifications

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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,

Report message to a moderator

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".

Report message to a moderator

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

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/

Report message to a moderator

Re: Sql * Loader [message #616431 is a reply to message #616429]

Mon, 16 June 2014 16:53

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.

Report message to a moderator

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.

Report message to a moderator

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.

Report message to a moderator

Previous Topic:	.xls file into table by using sqlloader
Next Topic:	EXPDP getting error ORA-39002

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue May 20 15:49:47 CDT 2025