Hi,
I am trying to generate mail with attachment on oracle 10g R2 database.
I am using UTL_MAIL procedure with the following code.
-------------------------------------my code---------------------------
CREATE OR REPLACE PROCEDURE mail_att_raw(filename varchar2) AS
fil BFILE;
file_len PLS_INTEGER;
MAX_LINE_WIDTH PLS_INTEGER := 54;
buf RAW(2100);
amt BINARY_INTEGER := 2000;
pos PLS_INTEGER := 1; /* pointer for each piece */
filepos PLS_INTEGER := 1; /* pointer for the file */
filenm VARCHAR2(50) := filename; /* binary file attachment */
data RAW(2100);
chunks PLS_INTEGER;
len PLS_INTEGER;
modulo PLS_INTEGER;
pieces PLS_INTEGER;
err_num NUMBER;
err_msg VARCHAR2(100);
resultraw RAW(32000);
BEGIN
/* Assign the file a handle */
fil := BFILENAME('BFILE_DIR', filenm);
/* Get the length of the file in bytes */
file_len := dbms_lob.getlength(fil);
/* Get the remainer when we divide by amt */
modulo := mod(file_len, amt);
/* How many pieces? */
pieces := trunc(file_len / amt);if (modulo <> 0) then
pieces := pieces + 1;end if;/* Open the file */
dbms_lob.fileopen(fil, dbms_lob.file_readonly);/* Read the first amt into the buffer */
dbms_lob.read(fil, amt, filepos, buf);/* For each piece of the file . . . */
FOR i IN 1..pieces LOOP/* Position file pointer for next read */
filepos := i * amt + 1;/* Calculate remaining file length */
file_len := file_len - amt;/* Stick the buffer contents into data */
data := utl_raw.concat(data, buf);/* Calculate the number of chunks in this piece */
chunks := trunc(utl_raw.length(data) / MAX_LINE_WIDTH);/* Don't want too many chunks */
IF (i <> pieces) THEN
chunks := chunks - 1;
END IF;/* For each chunk in this piece . . . */
FOR j IN 0..chunks LOOP/* Position ourselves in this piece */
pos := j * MAX_LINE_WIDTH + 1;/* Is this the last chunk in this piece? */
IF (j <> chunks) THEN len := MAX_LINE_WIDTH;
ELSE
len := utl_raw.length(data) - pos + 1;
IF (len > MAX_LINE_width) THEN
len := MAX_LINE_WIDTH;
END IF;
END IF;/* If we got something, let's write it */
IF (len > 0 ) THEN
resultraw := resultraw || utl_raw.substr(data, pos, len);
END IF;
END LOOP;/* Point at the rest of the data buffer */
IF (pos + len <= utl_raw.length(data)) THEN
data := utl_raw.substr(data, pos + len);
ELSE
data := NULL;
END IF;/* We're running out of file, only get the rest of it */
if (file_len < amt and file_len > 0) then
amt := file_len;
end if;/* Read the next amount into the buffer */dbms_lob.read(fil, amt, filepos, buf);
END LOOP;/* Don't forget to close the file */
dbms_lob.fileclose(fil);
SYS.UTL_MAIL.SEND_ATTACH_RAW(sender => 'xyx@abc.com', recipients => 'xyx@abc.com',subject => 'Testmail', message => 'Hallo', attachment => resultraw, att_filename => filename);
EXCEPTION
WHEN OTHERS THEN--dbms_output.put_line('Fehler');
raise_application_error(-20001,'The following error has occured: ' || sqlerrm);
END;
/
--------------------------------------------
These are the steps that i am following
1.set serveroutput on;
2. create or replace directory BFILE_DIR as 'c:\beispiele\utl_mail';
3. grant read on directory BFILE_DIR to public;
4. Above procedure creation.
5 execute mail_att_raw('test.xls');
i have set the UTL_FILE_DIR parameter like this
alter system set utl_file_dir='C:\' scope=spfile;
this test.xls file i have created in my C:\ drive as well as in C:\BFILE_DIR.
When i doing execution of the procedure ,i am getting the following error.
ERROR at line 1:
ORA-20001: The following error has occured: ORA-22288: file or LOB operation
GETLENGTH failed
The system cannot find the file specified.
ORA-06512: at "SCOTT.MAIL_ATT_RAW", line 85
ORA-06512: at line 1
I have tried many options,but no luck.
If anybody is having any idea,please help me.
Thanks in advance.
Bye