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?
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 ..
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.
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;
/
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.
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;
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;
/
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..
June 22, 2021 - 1:32 am UTC
Great work by the community