Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Ankit.

Asked: January 22, 2020 - 4:37 pm UTC

Answered by: Connor McDonald - Last updated: February 10, 2020 - 4:17 am UTC

Category: PL/SQL - Version: 12.2.0.1

Viewed 100+ times

You Asked

Hi Tom

I have been trying to call an executable shell script placed on AIX 7.2 from Oracle 12.2 via following code:

BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'GEN_DAILY_CNT_FILE',
    job_type        => 'executable',
    job_action      => '/u01/app/oracle/local/scripts/db_daily_table_cnt.ksh',
    start_date      =>  '22-JAN-20 02.00.00 PM US/Eastern',
    enabled         => TRUE ,
    auto_drop       => FALSE,
    comments        => 'Call daily cnt job on demand');
END;
/


I am facing error "ORA-27369: job of type EXECUTABLE failed with exit code: Value too large to be stored in data type" while trying to run it via
exec DBMS_SCHEDULER.RUN_JOB (   'GEN_DAILY_CNT_FILE'     )



In my executable file, this line was causing the problem:

cat $output >> $output1


 wc -c $output

17671 db_daily_table_cnt.log

So I altered that line with following code:

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


After that, it failed with same error at below line:

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


Any suggestion to overcome this somehow.

thanks

and we said...

Check the column dba_scheduler_job_run_details.additional_info

It will normally have the true error. The most common example is that path definitions are not correct.

The script you run from the scheduler does not automatically pick up the standard oracle profile, so you need to explicitly set path information etc.

MOS note 1561664.1 has other possible causes.

and you rated our response

  (6 ratings)

Reviews

Follow-up

January 23, 2020 - 6:03 am UTC

Reviewer: Ankit Vohra from India

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
Connor McDonald

Followup  

January 29, 2020 - 9:15 am UTC

OK, can try you this to narrow down things more

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

and also, throughout the script, add some debugging like:

echo "Up to here" >> /tmp/debug

etc etc

then we can perhaps narrow down to where things exactly are going wrong

Follow-up

January 30, 2020 - 1:35 pm UTC

Reviewer: Ankit Vohra from India

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
Chris Saxon

Followup  

February 05, 2020 - 3:03 pm UTC

So what value does $output1 when you pass it to uuencode? Is this correct?

Follow-up

February 05, 2020 - 3:07 pm UTC

Reviewer: Ankit Vohra from India

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
Chris Saxon

Followup  

February 05, 2020 - 3:20 pm UTC

Have you tried spooling the query to a file, then passing the name of the spooled file to uuencode?

Follow-up

February 05, 2020 - 3:35 pm UTC

Reviewer: Ankit Vohra from India

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
Chris Saxon

Followup  

February 06, 2020 - 1:29 pm UTC

Include the value of $output1 in your debugging to ensure it has the value you think it has.

And, as suggested below, check the paths are correct.

Full path

February 06, 2020 - 6:26 am UTC

Reviewer: A reader

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!

Follow-up - SOLUTION

February 07, 2020 - 4:44 pm UTC

Reviewer: Ankit Vohra from India

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 !!
Connor McDonald

Followup  

February 10, 2020 - 4:17 am UTC

glad we could help

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database