CODEWITHSUNDEEP
oracle-databaseshellemail
S.I.D.D.H.I
S.I.D.D.H.I

Reputation: 27

mail not working when called from oracle job and email address is dynamic

I am calling shell script from Oracle Package. The shell script will generate some files from oracle tables and email summary of files generated.

The email list mentioned in shell script is hard coded. I am trying to replace the hard coded email in shell script and get this list from oracle table.

All the changes made are fine. There is no error during execution. But I am not getting email for which address is taken from variable.

Here are details of flow and changes made:

This is a job in oracle package:

DBMS_SCHEDULER.CREATE_JOB(
    job_name             => 'ExtractGen',
    job_type             => 'EXECUTABLE',
    number_of_arguments  => 3,
    job_action           => vjob,
    auto_drop            => true);

DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE('ExtractGen',1,var1);
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE('ExtractGen',2,var2);
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE('ExtractGen',3,var3);
DBMS_SCHEDULER.ENABLE('ExtractGen');

This is a new block added in shell script to get email list from oracle table to replace hard coded email address used:

emailList=`$ORACLE_HOME/bin/sqlplus -S user/pwd@sid<<EOF
set heading off
SET echo OFF
SET verify OFF
SET heading OFF
SET feedback OFF
SET pagesize 0
SET linesize 1000
SET escape ~
SELECT EMAIL_ID FROM tbl_email WHERE Upper(EMAIL_GROUP) = 'EXTRACT';
EOF`

mail -s "Extract: Successful" $emailList < $l_extract_dir_name/mail.log;

Earlier, hard coded emails where used instead of variable $emailList and was working fine. After replacing it with variable, I am not getting email. But if i put the next line with hard coded email for testing as

mail -s "Extract: Successful Hard coded" [email protected] < $l_extract_dir_name/mail.log;

I am getting one email within the same execution sent by hard coded block. I have put different subject to identify differences.

The next test I did was to call shell script directly instead of calling it from oracle job. (Because required tables to get files will already be prepared, this flow works fine without any differences as calling from oracle job). When shell script was executed directly with required parameter, I am getting 2 emails, both from hard coded as well as dynamic email variable.

Can anyone help?

Thanks in advance!

Upvotes: 1

Views: 48

Answers (1)

J. Chomel
J. Chomel

Reputation: 8393

Make sure your $ORACLE_HOME is set when shell is called from Oracle job.

If it isn't the case, you must add source <your shell> command before launching the job. e.g. say you normally run bash:

add this line at the beginning of ExtractGen (after #!/bin/bash or similar if it exists, because #!... must be first line)

source ~/.bashrc

Upvotes: 1

Related Questions