SQL & PL/SQL

We have a cron scheduled which provides the status of database in our mails. Recently we cloned the instance and while doing that we bounced the whole server.

After that we stopped receiving mails from that instance. When I checked the crontab and ran the scripts manually. It showed following error.

SQL> alter session SET smtp_out_server = 'xxxx1:100';
declare
v_utl_filehandler UTL_FILE.FILE_TYPE;
v_rawfile RAW(32767);
v_size NUMBER;
5 v_block NUMBER;
6 v_boolean BOOLEAN;
v_file_dir varchar2(100);
7 8 v_transfer_location varchar2(100) := 'xxxx@xxx.in,xxxx@xxx.in,xxxx@xxx.in';
9 v_file_name varchar2(100) := 'mailattachment.doc';
10 begin
11 v_file_dir := 'MAIL_DIR';
12 v_utl_filehandler := UTL_FILE.FOPEN(v_file_dir, v_file_name, 'r');
13 UTL_FILE.fgetattr(v_file_dir, v_file_name, v_boolean, v_size, v_block);
14 UTL_FILE.get_raw(v_utl_filehandler, v_rawfile, v_size);
15 UTL_FILE.FCLOSE(v_utl_filehandler);
16
17 sys.utl_mail.send_attach_raw( sender => 'xxxxx@xxxx.in'
18 , recipients => v_transfer_location
19 , subject => 'Database status from EBS UAT'
20 , message => 'Dear Sir'||CHR(13)||CHR(13)||
21 'Please find attached database status file from EBS UAT.'||CHR(13)||CHR(13)||
22 'Thanks'||CHR(13)||
23 'oracle@UATDB1'||CHR(13)||CHR(13)
24 , attachment => v_rawfile
25 , att_inline => FALSE
26 , att_filename => v_file_name
27 );
28 end;
29 /
declare
*
ERROR at line 1:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
ORA-06512: at line 12

Before cloning it was working fine.

Edited by: Gaurav on May 24, 2012 11:52 AM