Inserting a clob with length greater than 8192 with utl_file.put(); gets ORA-06502

(Doc ID 760144.1)

Last updated on AUGUST 25, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 10.1.0.2 to 11.1.0.7 [Release 10.1 to 11.1]
Information in this document applies to any platform.
This problem can occur on any platform.
Oracle Server Enterprise Edition - Version: 10.1.0.2 to 11.1.0.7

Symptoms

Database characterset is UTF8.

Statement issued:
declare
mymol clob;
DIR_NAME varchar2(40) := 'MY_DIR';
file_id UTL_FILE.file_type;
begin
select mol into mymol from provagv where xxx;
file_id := utl_file.fopen( DIR_NAME,'testcase3.txt','W');
utl_file.put(file_id,mymol);
utl_file.fclose(file_id);
end;
/

If the clob (mymol) size is bigger than 8192, you will get the following error:
ORA-06502: PL/SQL: numeric or value error

Changes

Recent migration from 9i to 10g.

Characterset changed from WEIS8859P1 to AL32UTF8.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms