urgent + Issue while inserting into table more than 1000 +
Oracle 9i

Hi, 

 I have a table and i am inserting comma separate values into that table but
procedure works for some values but if i try to insert more than  500 or 800
values it give error : can bind a LONG value only for insert into a LONG
column"  Can anyone help with this please? its urgent!

Create table temp(  val Varchar2(4000) null)

PROCEDURE Test
(
  p_str   IN    VARCHAR2   e.g P_str =
'1,3,5,3344,454,1000,344,33,45,66..................................) some time
200 or 100 or 800 or more comma separated values in P_str

)
IS

      lc               NUMBER :=1;
        v_loanlen        NUMBER;
        v_len            NUMBER;
        v_start_sub      NUMBER :=1;
        v_end_sub        NUMBER;

BEGIN
          DELETE FROM Temp;
              v_loanlen:=length(p_str);
         LOOP
            EXIT WHEN v_end_sub=v_loanlen;
            v_end_sub:=INSTR(p_str, ',' ,1,lc)-1;
            IF v_end_sub<0
            THEN
                v_end_sub:=v_loanlen;
            END IF;
                v_len:=v_end_sub-v_start_sub+1;
                insert into Temp (val) VALUES
(SUBSTR(p_str,v_start_sub,v_len));
                lc:=lc+1;
                v_start_sub:=v_end_sub+2;
          END LOOP;
      COMMIT;

  END;

By: KP

JUST MODIFY UR CODE BELOW:
Create table temp( val clob null)

HERE I THINK U R TRYING TO INSERT A SPECIAL CHARACTER. Some of the Asian
language characters can consume up to 3 bytes in UTF8 encoding. MAY BE THIS IS
THE PROBLEM.

HOPE IT WILL HELP YOU.
MARK AS ANSWER IF ITS HELPED YOU.

i tried to ccreate temp table with clob but it gives error

ORA-03237: Initial Extent of specified size cannot be allocated in tablespace
(Sample_DATA_8K)

OU HAVE TO NEED TO ALTER UR USER ACCOUNT BY THE FOLLOWING WAY:
alter user username quota unlimited on users;
alter user username quota unlimited on temp;


HOPE IT WILL RESOLVE UR PROBLEM.
MARK AS ANSWER if its helped you.

i found the problem with my procesure and  its now resolved

Anyways, Thanks a lotshawpnendu for your idea.