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!

Unable to put into CLOB variable (raw variable too long)

Ashish ShahJul 14 2012 — edited Jul 15 2012
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;
+/+
-----
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 12 2012
Added on Jul 14 2012
6 comments
1,248 views