Skip to Main Content
  • Questions
  • I cannot append data more than 32kb in CLOB

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Deepak.

Asked: May 30, 2016 - 10:06 am UTC

Last updated: June 22, 2021 - 1:32 am UTC

Version: oracle 11g

Viewed 10K+ times! This question is

You Asked

Hi,

Am using CLOB data type in stored procedure. I am appending data from temporary table in CLOB

I have a following plsql.

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

TYPE stutype IS TABLE OF sturecord;

stutable stutype;
stid CLOB := NULL;
Regid CLOB := NULL;
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;

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;

In line 22 am getting error like "ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 22"

Temporary Table having more than 44000 record, approximate size is 3mb.

CLOB Datatype has max length 4GB.

Please help how to resolve this error.

Thanks



and we said...

Just declaring a variable of type clob, is equivalent to varchar2(32767) in plsql. If you want a "real" clob, you either create one explicitly, or get one back from a table and use that. Here's two examples


SQL> set serverout on
SQL> declare
  2    c clob;
  3  begin
  4    dbms_lob.createtemporary(c,true);
  5
  6    for i in 1 .. 1000 loop
  7      c := c || rpad(i,100);
  8    end loop;
  9
 10    dbms_output.put_line(dbms_lob.getlength(c));
 11
 12    dbms_lob.freetemporary(c);
 13  end;
 14  /
100000

PL/SQL procedure successfully completed.

SQL>
SQL> drop table t purge;

Table dropped.

SQL> create table t ( c1 clob );

Table created.

SQL>
SQL> declare
  2    c clob;
  3  begin
  4    insert into t values ( empty_clob() )
  5    returning c1 into c;
  6
  7    for i in 1 .. 1000 loop
  8      dbms_lob.writeappend(c,100,rpad(i,100));
  9    end loop;
 10
 11    commit;
 12  end;
 13  /

PL/SQL procedure successfully completed.

SQL>
SQL> select dbms_lob.getlength(c1) from t;

DBMS_LOB.GETLENGTH(C1)
----------------------
                100000

1 row selected.

SQL>
SQL>


Hope this helps.

Rating

  (13 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Getting same error

Deepak G, May 31, 2016 - 5:36 am UTC

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?
Connor McDonald
May 31, 2016 - 6:24 am UTC

Recast it using my second example, ie

stid := stid || stutable (i).stid || ',';

becomes

dbms_lob.writeappend(stid,
length(stutable(i).stid)+1,
stutable(i).stid || ','
);

and let us know who you go.

Beware of dbms output limit

Ghassan, May 31, 2016 - 6:01 am UTC

Dbms output has a limit of 255 character / line
That why you may split this ..
Connor McDonald
May 31, 2016 - 6:26 am UTC

Their code does not have any references to dbms_output ?

Its worked

Deepak G, May 31, 2016 - 7:06 am UTC

Thanks a lot, its working for me.
Connor McDonald
June 01, 2016 - 1:44 am UTC

Glad we could help

To Deepak : On CLOB size

Rajeshwaran, Jeyabal, May 31, 2016 - 7:07 am UTC

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.

Oh

Ghassan, May 31, 2016 - 7:14 am UTC

Sorry for that

Shawn, May 31, 2016 - 3:05 pm UTC

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;
/

Connor McDonald
October 19, 2016 - 12:51 am UTC

True, my mistake, I did an initial glance at the error and interpreted their code as 'long' not 'clob'.


ORU-10027: buffer overflow, limit of 20000 bytes.

Deepak G, October 18, 2016 - 9:23 am UTC

Now am getting this error ORU-10027: buffer overflow, limit of 20000 bytes.
Connor McDonald
October 19, 2016 - 12:52 am UTC

set serveroutput on size unlimited

example good and bad

Michael Schrinner, February 15, 2018 - 7:44 am UTC

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;

Chris missed the fact that Deepak was concatenating numbers

ACGI Support, November 01, 2019 - 6:09 pm UTC

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;

Connor McDonald
November 05, 2019 - 4:11 am UTC

Nice input. Thanks

Cant append multiple 32k rows into single row from table

Ravi Kumar, June 15, 2021 - 10:34 am UTC

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;
/
Connor McDonald
June 16, 2021 - 6:47 am UTC

Check your max string size ...

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


If you have not set it to extended, then a varchar2 is capped at 4k.

Ravi Kumar, June 16, 2021 - 7:47 am UTC

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

line no

Anton Scheffer, June 16, 2021 - 8:42 am UTC

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?

Cant append multiple 32k rows into single row from table

Ravi Kumar, June 17, 2021 - 11:41 am UTC

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

Thank you all..
Connor McDonald
June 22, 2021 - 1:32 am UTC

Great work by the community

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here