Shell Programming and Scripting

Hi,
I have been assigned a job which requires me to send mails from unix(Mailx) upon on certain actions triggered in the database.
On insert/update of a certain field into one of the database tables the shell script present in Unix box responsible to send mail though mailx needs to be triggered so as to send the mails in real time.

Please do assist me in this regard, as i am not able find any such option.

Regards,
Hemanth

Hi
Check this: how to call shell script from pl/sql

Guru.

Hi Guru,
I have tried using this option and this doesnt seemd to call my sh script in unix.
The script i used with dbms_scheduler is:

Code:

begin
dbms_scheduler.create_program
(
'TPP_SEND_EMAIL',
'EXECUTABLE',
'/Temp/ENV/code/BIL/users/usrbil01/Hemz/sndmailscrpt.ksh',
0,
TRUE,
'Send email to ISU'
);
dbms_output.put_line('SUCCESS');
exception
when others then
dbms_output.put_line('Error: '||SQLERRM);
end;

This script returns success but has not called my script 'sndmailscrpt.ksh' responsible to send email.

Please let me know the possible reason behind this.

Regadrs,
Hemanth

The reason is that you have simply created a scheduler program, nothing more. In order to execute the script you'll have to run the corresponding job.

I'd suggest you have a look at Oracle's documentation of the DBMS_SCHEDULER supplied package.

tyler_durden

Hi Tyler,
Thank you for your input.
I have now been able to create a job in this regard and run the same, but this results in an error.
The error seems to be related to OS, but i am not aware as to how to get through this
Job:
begin
dbms_scheduler.create_job
(
job_name =>'TPP_SEND_EMAIL_JOB',
job_type =>'EXECUTABLE',
job_action =>'/Temp/ENV/code/BIL/users/usrbil01/Hemz/sndmailscrpt.ksh',
repeat_interval =>'FREQ=MINUTELY; INTERVAL=30',
enabled =>true,
comments =>'Send Email on ISU update'
);
end;
/

Command to run the job:
exec dbms_scheduler.run_job('TPP_SEND_EMAIL_JOB');

The job is failing with the error:
ORA-27370: job slave failed to launch a job of type EXECUTABLE
ORA-27300: OS system dependent operation:accessing extjob wrapper failed with status: 2
ORA-27301: OS failure message: No such file or directory
ORA-27302: failure occurred at: sjsec 3

Please do assist me getting over:
> Error as above
> Is "repeat_interval" mandatory here, I want to execute this job from within a trigger as and when the trigger is executed without specifying a timeline for its execution(Minutely,hourly etc..).
Is this possible, if not please suggest me a way forward.
I am running out of time for my delivery on this

Thank you in advance.
Awaiting your reply

Regards,
Hemanth

If you just want to send a mail, why not use the utl_smtp package?

Hi,
Firstly, Thank you for the input.
Below are my requirements:
> I have to send mail in html format having dynamic fields like (name,address etc..) which may change from person to person the mail is sent for.
> The template for the mail has to be configrable i.e, it is constant as of now but may be changed in future.

I have also been able to gather a few information about the utl_mail package in 10g which may solve the purpose of sending mail through database, but I am not able to find out if that supports sending email in html format and also support of dynamic data and configurable template.
I see most of this possible in unix, hence lokking at a job from database.
Please do let me know if i can expedite one of these approaches subject to my time lines