SQL & PL/SQL

Hi,

I am attempting to insert several varchar2 values that make up a single clob into a table.
Version is;

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

I am getting the following error when attempting to insert a value over 8000 characters;

SQL> begin
  2    xddq_ins(rpad('x',8001,'x'));
  3  end;
  4  /
begin
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small

But works fine for values less than 8000 characters, for example;

SQL> begin
  2    xddq_ins(rpad('x',4001,'x'));
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> select length(str) str_len, pos1,pos2,chunk_cnt
  2  from xddq;

   STR_LEN       POS1       POS2  CHUNK_CNT
---------- ---------- ---------- ----------
      4000          1       4000          1
         1       4001       8000          2

The table definition and procedure I'm using is as follows;

create table xddq (Str        varchar2(4000)
                  ,t          timestamp default current_timestamp
                  ,Pos1       integer
                  ,Pos2       integer
                  ,Chunk_Cnt  integer);

create or replace procedure XDDQ_Ins (p_Str in varchar2) as

  STR_EMPTY      exception;

  l_Clob         clob         := to_clob(p_Str);              -- first convert to a clob
  l_Len          pls_integer  := dbms_lob.getlength(l_Clob);  -- clob length
  l_Chunk_Cnt    pls_integer  := ceil(l_Len / 4000);            -- number of chunks

  begin
    if (l_Len < 1) then
      raise STR_EMPTY;
    end if;

    insert into xddq (t
                     ,str
                     ,Pos1
                     ,Pos2
                     ,Chunk_Cnt)
                    (
                     select current_timestamp
                           ,dbms_lob.substr(l_Clob, level*4000, ((level - 1) * 4000) + 1)
                           ,((level - 1) * 4000) + 1
                           ,level*4000
                           ,level
                     from   dual
                     connect by level <= l_Chunk_Cnt
                    );
    exception
      when STR_EMPTY then
        raise_application_error('No string to insert', -20088);
end XDDQ_Ins;
/

The instance character sets are as follows;

SQL> select value
  2  from nls_database_parameters
  3  where parameter in ('NLS_CHARACTERSET','NLS_NCHAR_CHARACTERSET')
  4  /

VALUE
----------------------------------------
WE8MSWIN1252
AL16UTF16

Could anyone help out with regard to how I can overcome this problem?

I need to perform an INSERT of a CLOB in chunks of 4000 characters.
The size of the of the varchar2 parameter into the procedure is never more than 10000 characters.

thanks