Unable to put into CLOB variable (raw variable too long)
Dear All,
I am trying to send embedded image in email. For the same I amt converting image to CLOB. It works find when size of the image is less than 24573 but if it is more than that it gives me following error. Can someone tell what is wrong here?
ORA-06502: PL/SQL: numeric or value error: raw variable length too long
ORA-06512: at "SYS.UTL_ENCODE", line 243
ORA-06512: at "APPS.GET_ENC_IMG_FROM_FS01", line 24
I am calling following procedure.
-----
CREATE OR REPLACE PROCEDURE apps.get_enc_img_from_fs01 (p_dir IN VARCHAR2, p_file IN VARCHAR2, p_clob IN OUT NOCOPY cLOB)
AS
l_bfile BFILE;
--l_step PLS_INTEGER := 24573;+
--l_step PLS_INTEGER := 24573;+
l_step PLS_INTEGER := 5001;
ntotal_size NUMBER (20);
iseof VARCHAR2 (1) := 'N';
nleft_size NUMBER (20);
ncurrent_pointer NUMBER (20) := 1;
BEGIN
l_bfile := BFILENAME (p_dir, p_file);
DBMS_LOB.fileopen (l_bfile, DBMS_LOB.file_readonly);
ntotal_size := DBMS_LOB.getlength (l_bfile);
nleft_size := ntotal_size;
LOOP
IF nleft_size <= l_step
THEN
l_step := nleft_size;
iseof := 'Y';
END IF;
p_clob := p_clob || UTL_RAW.cast_to_varchar2 (UTL_ENCODE.base64_encode (DBMS_LOB.SUBSTR (l_bfile, ncurrent_pointer, l_step)));
ncurrent_pointer := ncurrent_pointer l_step;+
nleft_size := nleft_size - l_step;
EXIT WHEN iseof = 'Y';
END LOOP;
DBMS_LOB.fileclose (l_bfile);
END;
+/+
-----