Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

ORA-22288: file or LOB operation GETLENGTH failed while using utl_mail

693693Apr 21 2009
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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 19 2009
Added on Apr 21 2009
0 comments
486 views