I tried 1st example.

DECLARE
TYPE sturecord
   IS
      RECORD (stid              NUMBER, Regid         NUMBER);

   TYPE stutype IS TABLE OF sturecord;

   stutable                    stutype;
   stid                       CLOB;
     Regid                  CLOB;
BEGIN
    SELECT DISTINCT (st.stu_id), rg.Reg_id
   BULK COLLECT
   INTO stutable
   FROM Student_details st, student_registration rg
   WHERE st.stu_id = rg.stu_id;
   
   dbms_lob.createtemporary(stid,true);
   dbms_lob.createtemporary(Regid,true);
   
   FOR i IN stutable.FIRST .. stutable.LAST
      LOOP
       dbms_output.put_line(i) ;
         stid        := stid || stutable (i).stid || ',';
         Regid   := Regid || stutable (i).Regid || ',';
      END LOOP;
END;

Am getting same error.

"ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 25"

I tried some sample i didn't get any error.

DECLARE
   c   CLOB;
   a   Number;
BEGIN
   c := c ||rpad('*', 32764, '*');
   c := c ||rpad('*', 32764, '*');
   a := length(c);
 dbms_output.put_line(a) ;
END;

65528
any idea about this?

Dbms output has a limit of 255 character / line
That why you may split this ..

Thanks a lot, its working for me.

Temporary Table having more than 44000 record, approximate size is 3mb.
CLOB Datatype has max length 4GB.

Clob datatype permits for the storage of up to 4GB of data in Oracle9i and before or (4GB)x(Database Block Size) bytes of data in Oracle 10g and above. CLOBs contain information that is subject to character set conversion.

So if the tablespace in your database are of standard blocksize (8k) then CLOB has max length of 4GB * 8000 = 32TB.

Sorry for that

I'm a little worried about your comment "Just declaring a variable of type clob, is equivalent to varchar2(32767) in plsql." because that just simply isn't true. We have many cases in our code where we create very large blocks of dynamic sql code and store it in a PL/SQL Clob variable without creating a temporary lob. Well over a 32k limit.

Here is an example.

declare
    v_clob clob; 
begin
    for i in 1..4096 loop
        v_clob := v_clob || '11111111111111111111111111111111';
    end loop;
    dbms_output.put_line('Length of V_CLOB is ' || length(v_clob) || ' Characters');
end;
/

Now am getting this error ORU-10027: buffer overflow, limit of 20000 bytes.

declare
c clob;
begin
for i in 1..10000 loop
c := c || '1234567890' || to_char(i) ; -- no recast does work
--c := c || '1234567890' || i ; -- recast! does not work
end loop;
dbms_output.put_line(length(c));
end;

I think you missed that he was concatenating numbers to clobs. I had the same problem and ended up here.

This fails for me in 12c because I'm concatenating a number to a clob:

DECLARE
  tom clob;
  student_id number;
BEGIN
  FOR i IN 1 .. 32768 LOOP
    student_id := i;
    -- the concatenate operator's clob support seems to be
    -- defeated by the implicit number to character conversion
    -- so it's processing the right hand side as varchar2 instead
    tom := tom || student_id || ',';
  END LOOP;
END;

It works fine if you make the to_char explicit:

DECLARE
  tom clob;
  student_id number;
BEGIN
  FOR i IN 1 .. 32768 LOOP
    student_id := i;
    tom := tom || to_char(student_id) || ',';
  END LOOP;
END;

Refactoring will likewise solve this problem:

DECLARE
  tom clob;
  student_id number;
BEGIN
  dbms_lob.createtemporary(tom, true);
  FOR i IN 1 .. 32768 LOOP
    student_id := i;
    dbms_lob.append( tom, student_id || ',' );
  END LOOP;
END;

Hi,

Oracle 12c.

Need help in concatenating the multiple rows into single value and update y table which has CLOB datatype.

By using PLSQL code we generated 200,700 bytes of
JSON string and same was divided by 32767 and inserted into x table. So, now we have 7 rows in x table as below ...

[{"keys":["PartType","PartClass"],"length":2,"_fields":["SI- Purchased Software","CFI-Software"],"_fieldLookup":{"PartType":0,"PartClass":1}},.....32767
:{"PartType":0,"PartClass":1}},{"keys":["PartType","PartClass"],"length":2,"_fields":["Service Kit,Compact Disk Drive","Assy,Base,Cus,Mod,Ship,.....32767
Class"],"length":2,"_fields":["Kit,Compact Diskette","Assy,Base,Cus,Mod,Ship,Svc"],"_fieldLookup":{"PartType":0,"PartClass":1}},{"keys":["PartT.....32767
Cus,Mod,Ship,Svc"],"_fieldLookup":{"PartType":0,"PartClass":1}},{"keys":["PartType","PartClass"],"length":2,"_fields":["MOD,Instructions","Assy.....32767
ss"],"length":2,"_fields":["CD/Floppy Drive combo","Drive,Combo"],"_fieldLookup":{"PartType":0,"PartClass":1}},{"keys":["PartType","PartClass"].....32767
ype":0,"PartClass":1}},{"keys":["PartType","PartClass"],"length":2,"_fields":["SI- Purchased Floppy Drive","CFI-CSTM FCTRY INTG"],"_fieldLookup.....32767
:0,"PartClass":1}},{"keys":["PartType","PartClass"],"length":2,"_fields":["Socket,PLCC","Socket"],"_fieldLookup":{"PartType":0,"PartClass":1}}] .....4084

Declare
V_Clob Clob;
L_Data Clob;
v_sno number(9);
Cursor C_Clb Is Select Sno,Cont From Imcg_Temp Order By Sno;
c_rec C_Clb%rowtype;
Begin
dbms_lob.createtemporary(V_Clob,true);
Open c_clb;
Loop
fetch c_clb into c_rec ; --v_sno,V_Clob;
Exit When c_clb%Notfound;
--L_Data := Dbms_Lob.Substr( V_Clob, 32767);
If Length(C_Rec.Cont)>=4000 Then
--L_Data := L_Data||c_rec.sno; -- giving output
L_Data := L_Data||To_Clob(C_Rec.Cont) ; -- ORA-06502: PL/SQL: numeric or value error
-- L_Data := L_Data||rpad(c_rec.Cont,32767) ; --ORA-06502: PL/SQL: numeric or value error
end if;
End Loop;
Close C_Clb;

Update Imcg_Temp_Bkp3 Set Cont = to_clob(L_Data) ;
Dbms_Output.Put_Line(Sql%Rowcount || ' rows updated');

dbms_output.put_line( l_data );
Dbms_Output.Put_Line( 'length = ' || Length(L_Data) );
dbms_lob.freetemporary(V_Clob);
End;
/

SQL> set serverout on
SQL> Declare
  2    V_Clob Clob;
  3     L_Data Clob;
  4    v_sno number(9);
  5    Cursor C_Clb Is Select rpad('x',32000,'x') cont from dual connect by level <= 10;
  6    c_rec C_Clb%rowtype;
  7  Begin
  8    dbms_lob.createtemporary(V_Clob,true);
  9    Open c_clb;
 10    Loop
 11      fetch c_clb into c_rec ; --v_sno,V_Clob;
 12      Exit When c_clb%Notfound;
 13
 14      If Length(C_Rec.Cont)>=4000 Then
 15      --L_Data := L_Data||c_rec.sno; -- giving output
 16      L_Data := L_Data||To_Clob(C_Rec.Cont) ; -- ORA-06502: PL/SQL: numeric or value error
 17      -- L_Data := L_Data||rpad(c_rec.Cont,32767) ; --ORA-06502: PL/SQL: numeric or value error
 18      end if;
 19      End Loop;
 20      Close C_Clb;
 21
 22     Dbms_Output.Put_Line( 'length = ' || Length(L_Data) );
 23    dbms_lob.freetemporary(V_Clob);
 24  End;
 25  /
length = 320000

PL/SQL procedure successfully completed.

SQL> show parameter max_str

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_string_size                      string      EXTENDED

Thanks for quick reply.

where do i extend ?

my Json string in table is as below:

[{"keys":["PartType","PartClass"],"length":2,"_fields":["SI- Purchased Software","CFI-Software"],"_fieldLookup":{"PartType":0,"PartClass":1}},.....32767
:{"PartType":0,"PartClass":1}},{"keys":["PartType","PartClass"],"length":2,"_fields":["Service Kit,Compact Disk Drive","Assy,Base,Cus,Mod,Ship,.....32767
Class"],"length":2,"_fields":["Kit,Compact Diskette","Assy,Base,Cus,Mod,Ship,Svc"],"_fieldLookup":{"PartType":0,"PartClass":1}},{"keys":["PartT.....32767
Cus,Mod,Ship,Svc"],"_fieldLookup":{"PartType":0,"PartClass":1}},{"keys":["PartType","PartClass"],"length":2,"_fields":["MOD,Instructions","Assy.....32767
ss"],"length":2,"_fields":["CD/Floppy Drive combo","Drive,Combo"],"_fieldLookup":{"PartType":0,"PartClass":1}},{"keys":["PartType","PartClass"].....32767
ype":0,"PartClass":1}},{"keys":["PartType","PartClass"],"length":2,"_fields":["SI- Purchased Floppy Drive","CFI-CSTM FCTRY INTG"],"_fieldLookup.....32767
:0,"PartClass":1}},{"keys":["PartType","PartClass"],"length":2,"_fields":["Socket,PLCC","Socket"],"_fieldLookup":{"PartType":0,"PartClass":1}}] .....4084



I want to concatenate above 7 rows into sing row. but my plsql block is failing with 'ORA-06502: PL/SQL: numeric or value error'

Also I tried with below function but it is giving multiple rows for 7th row form table.
Select Json_Arrayagg( Cont Order By Sno Returning Clob) Json_Data;

Please help
From Imcg_Temp

Check the line number of your error.
Isn't the error not caused by the update where you use a to_clob function on a clob variable?

Yes, issue at Update only.. i fixed it with empty clob and then updated the JONS data to table.

Thank you all..