Thanks for the responseTom

dba_scheduler_job_run_details.additional_info gave only below info:
"ORA-27369: job of type EXECUTABLE failed with exit code: Value too large to be stored in data type"

I don't think it's related to path as when I comment out the below line in shell script, the scheduler job runs Successfully.

uuencode $output1  db_daily_table_cnt | mail -s "Test Mail" ankit.vohra1@gmail.com

MOS note 1561664.1 --> 887805.1 gives the cause for above error as "The problem was caused by job argument configuration." but I am not using any job arguments here.

I think it has something to do with buffering the file in memory for processing.
I am saying so because earlier it failed with same error at cat $output >> $output1 which I replaced with while loop so that whole file is not read in a single shot, but is done line by line.

Now I think while sending mail, the command unnencode is also trying to read whole file (denoted by $output1 variable & has size 17761 bytes) and is failing with same error.

I need to mail the file at the end of processing.

Not sure how to do a work around for this as I did in case of cat command.

You mentioned "so you need to explicitly set path information" , Do you mean giving absolute file path ? I did provide that.

thanks

Hi Tom,

After doing above changes, I executed my script from server. All went fine and mail was received too.

Here are logs:

Executing While loop now...
While loop ends.Doing unnencode
unnencode done. Sending mail...
mail sent


Here is size of tmp1:

myserver:DB$ ls -ltr /tmp/tmp1
-rw-r--r-- 1 oracle oinstall 24539 Jan 30 08:23 /tmp/tmp1
myserver:DB$



But when I executed same script from Oracle job, all went fine but /tmp/tmp1 has 0 size, & hence no mail received:

SQL> exec DBMS_SCHEDULER.RUN_JOB ( 'GEN_DAILY_CNT_FILE' );
PL/SQL procedure successfully completed.
SQL>


Here are the logs:

Executing While loop now...
While loop ends.Doing unnencode
unnencode done. Sending mail...
mail sent


myserver:DB$ ls -ltr /tmp/tmp1
-rw-r--r-- 1 oracle oinstall 0 Jan 30 08:27 /tmp/tmp1
myserver:DB$

And no mail was received.

FYI, here is my full shell script:

#!/bin/ksh

output=/u01/app/oracle/local/scripts/db_daily_table_cnt.log
output1=/u01/app/oracle/local/scripts/db_daily_table_cnt_f.log
rm $output
rm $output1

export ORACLE_SID=ORCL
export  ORACLE_HOME=/u01/app/oracle/product/12.2.0/db_1
export PATH=$ORACLE_HOME/bin:$PATH

$ORACLE_HOME/bin/sqlplus   -s   <<EOF
/ as sysdba
@/u01/app/oracle/local/scripts/db_daily_table_cnt.sql
exit;
EOF


echo "Executing While loop now..." > /u01/app/oracle/local/scripts/logging_temp.log

while IFS= read -r line
do
  echo "$line" >> $output1
done < "$output"

echo "While loop ends.Doing unnencode" >> /u01/app/oracle/local/scripts/logging_temp.log

uuencode $output1 /u01/app/oracle/local/scripts/db_daily_table_cnt > /tmp/tmp1

echo "unnencode done. Sending mail..." >> /u01/app/oracle/local/scripts/logging_temp.log

mail -s "Test Mail" ankit.vohra1@gmail.com < /tmp/tmp1

echo "mail sent" >> /u01/app/oracle/local/scripts/logging_temp.log

Seems unnencode is not generating any output in tmp1 due to some reason.

Thanks

Hi Chris

$output1 has 17671 characters of data when its passed to unnencode.

Its basically spool output from a sql file called from shell script (@/u01/app/oracle/local/scripts/db_daily_table_cnt.sql).

thanks

Hi Chris

Yes, I did that.

--In the sql file, output is spooled to filename in $output

$ORACLE_HOME/bin/sqlplus -s <<EOF
/ as sysdba
@/u01/app/oracle/local/scripts/db_daily_table_cnt.sql
exit;
EOF

--then I am just copying its contents to another file $output1..... I tried this way because it was not working directly with $output... so....

while IFS= read -r line
do
echo "$line" >> $output1
done < "$output"

--Here I am passing $output1 to unnencode , but

uuencode $output1 /u01/app/oracle/local/scripts/db_daily_table_cnt > /tmp/tmp1
mail -s "Test Mail" ankit.vohra1@gmail.com < /tmp/tmp1


As I mentioned earlier, please note that tmp1 is 0 size when I call from Oracle Scheduler's RUN_JOB. If I run the script on server (via ./script.ksh) , it works just fine, tmp1 does get size and mail is sent too.

thanks

Hi Ankit,

Have you tried giving the full path for uuencode?

I.e." <path>/uuencode" in the script when it is called by the scheduler.

Cheers!

Guys

Giving full paths for uuencode & mail commands did the trick here.
Seems scheduler expects full paths for these to work properly.

So at the end, I gave full paths and it worked :

/usr/bin/uuencode $output1 /u01/app/oracle/local/scripts/db_daily_table_cnt | /usr/bin/mail -s "Test Mail" ankit.vohra1@gmail.com

Thank you all for assisting and sharing your experience & knowledge.
Cheers !!