According to oracle the size of the CLOB variable is
>
size of a CLOB value (bytes) 4G * value of DB_BLOCK_SIZE parameter
SQL> show parameter db_block_size
db_block_size integer 8192
SQL> begin
2 declare
3 v_text1 clob;
4 i pls_integer := 0;
5 begin
6 loop
7 exit when i > 30000 ;
8 v_text1 := v_text1 || 'a' || i || ' char(1), ';
9 i := i + 1;
10 end loop;
11
12 exception
13 when others then
14 dbms_output.put_line('Error');
15 dbms_output.put_line (i);
16 dbms_output.put_line (length(v_text1));
17 raise;
18 end;
19 end;
20 /
Error
2259
32775
begin
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 17
SQL>
According to the above limits definedd by oracle, the variable v_text should hold
4G * 8192 bytes of data. But it fails at 32775.
Can any one help?
thks
vineet