# Execute shell script from plsql trigger **URL:** \ **Category:** Shell Programming and Scripting\ **Created:** [June 24, 2010, 12:37am UTC](https://community.unix.com/t/execute-shell-script-from-plsql-trigger/268241 "2010-06-24T00:37:01Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![hemant.bs11](https://community.unix.com/letter_avatar/hemant.bs11/32/6_22734026db12803ebb3e059622ac3534.png) [@hemant.bs11](https://community.unix.com/u/hemant.bs11)\ **Post date:** [June 24, 2010, 12:37am UTC](https://community.unix.com/t/execute-shell-script-from-plsql-trigger/268241/1 "2010-06-24T00:37:01Z")
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 ---
**Author:** ![guruprasadpr](https://community.unix.com/user_avatar/community.unix.com/guruprasadpr/32/1542_2.png) [@guruprasadpr](https://community.unix.com/u/guruprasadpr)\ **Post date:** [June 24, 2010, 1:22am UTC](https://community.unix.com/t/execute-shell-script-from-plsql-trigger/268241/2 "2010-06-24T01:22:14Z")
Hi Check this: [how to call shell script from pl/sql](http://www.unix.com/shell-programming-scripting/138723-executing-shell-script-pl-sql-block.html) Guru. ---
**Author:** ![hemant.bs11](https://community.unix.com/letter_avatar/hemant.bs11/32/6_22734026db12803ebb3e059622ac3534.png) [@hemant.bs11](https://community.unix.com/u/hemant.bs11)\ **Post date:** [June 24, 2010, 1:29am UTC](https://community.unix.com/t/execute-shell-script-from-plsql-trigger/268241/3 "2010-06-24T01:29:33Z")
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: ```nohighlight 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 ---
**Author:** ![durden\_tyler](https://community.unix.com/user_avatar/community.unix.com/durden_tyler/32/1450_2.png) [@durden\_tyler](https://community.unix.com/u/durden_tyler)\ **Post date:** [June 24, 2010, 1:32pm UTC](https://community.unix.com/t/execute-shell-script-from-plsql-trigger/268241/4 "2010-06-24T13:32:32Z")
> [@hemant.bs11](#): > > ... > > ```plaintext > 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. > > ... 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 ---
**Author:** ![hemant.bs11](https://community.unix.com/letter_avatar/hemant.bs11/32/6_22734026db12803ebb3e059622ac3534.png) [@hemant.bs11](https://community.unix.com/u/hemant.bs11)\ **Post date:** [June 24, 2010, 11:54pm UTC](https://community.unix.com/t/execute-shell-script-from-plsql-trigger/268241/5 "2010-06-24T23:54:57Z")
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: [FONT=Courier][SIZE=2]exec dbms\_scheduler.run\_job('TPP\_SEND\_EMAIL\_JOB'); The job is failing with the error: [FONT=Courier][SIZE=2][FONT=Courier][SIZE=2]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 [/SIZE][/FONT][/SIZE][/FONT][/SIZE][/FONT] ---
**Author:** ![Ygor](https://community.unix.com/user_avatar/community.unix.com/ygor/32/104_2.png) [@Ygor](https://community.unix.com/u/Ygor)\ **Post date:** [June 25, 2010, 12:19am UTC](https://community.unix.com/t/execute-shell-script-from-plsql-trigger/268241/6 "2010-06-25T00:19:07Z")
If you just want to send a mail, why not use the utl\_smtp package? ---
**Author:** ![hemant.bs11](https://community.unix.com/letter_avatar/hemant.bs11/32/6_22734026db12803ebb3e059622ac3534.png) [@hemant.bs11](https://community.unix.com/u/hemant.bs11)\ **Post date:** [June 25, 2010, 12:44am UTC](https://community.unix.com/t/execute-shell-script-from-plsql-trigger/268241/7 "2010-06-25T00:44:44Z")
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 ---
**Author:** ![Ygor](https://community.unix.com/user_avatar/community.unix.com/ygor/32/104_2.png) [@Ygor](https://community.unix.com/u/Ygor)\ **Post date:** [June 25, 2010, 2:28am UTC](https://community.unix.com/t/execute-shell-script-from-plsql-trigger/268241/8 "2010-06-25T02:28:42Z")
You can use the html\_mail procedure from: [Ask Tom "Sending HTML using UTL\_SMTP"](http://asktom.oracle.com/pls/asktom/f/f?p=100:11:0::NO::P11_QUESTION_ID:1739411218448) If you need configurable templates, then you will need to use Oracle BI Publisher with email bursting. Probably out of scope of these forums.