Clob datatype in PL/SQL program going to exception when it crosses the varchar2 limit and giving the "Error:ORA-06502: PL/SQL: numeric or value error" , Why Clob datatype is behaving like varchar2 datatype. I think clob can hold upto 4 GB of data. Please help me to find out the root cause.
DECLARE
t_Sql_Stmt CLOB DEFAULT Empty_Clob();
t_No_Of_Days_Pending NUMBER := 0;
Ip_Period_Name VARCHAR2(10) := 'SEP-18';
BEGIN
FOR i IN (SELECT *
FROM (SELECT Gjb.Default_Period_Name Period_Name,
'Test' Ledger_Name,
Gjb.Name Batch_Name,
'Required' Batch_Approval_Status,
Gjb.Created_By Batch_User_Name,
Gjb.Created_By Batch_User_Id,
Gjb.Created_By Created_By,
Gjb.Status Batch_Status,
'test@test.com' Email_Address,
Gjb.Je_Batch_Id Batch_Id,
Nvl(Round((SYSDATE - Gjb.Creation_Date), 2) * 24,
0) Hrs_Outstanding,
Gjb.Approval_Status_Code,
Decode(Gjb.Status, 'U', 'Unposted', 'Posted') Batch_Status_Desc,
NULL Approver_Email_Id,
'Not Submitted' Approver,
To_Char(Gjb.Creation_Date,
'MM/DD/YYYY HH:MI:SS AM') || ' Phx Time' Creation_Date,
NULL Submission_Date,
NULL Sub_Hrs_Outstanding
FROM Gl_Je_Batches Gjb
WHERE Gjb.Default_Period_Name = Ip_Period_Name
AND Gjb.Approval_Status_Code = 'R'
AND Gjb.Status = 'U'
AND (SYSDATE - Gjb.Creation_Date) >
t_No_Of_Days_Pending)
WHERE Rownum <= 130) LOOP
BEGIN
t_Sql_Stmt := t_Sql_Stmt || ' select ' || '''' || i
.Period_Name || '''' || ' ||' || ' ''~'' ' || '|| ' || '''' ||
i.Ledger_Name || '''' || ' ||' || ' ''~'' ' || '|| ' || '''' || i
.Batch_Name || '''' || ' ||' || ' ''~'' ' || '|| ' || '''' || i
.Batch_Approval_Status || '''' || ' ||' || ' ''~'' ' ||
'|| ' || '''' || i
.Batch_User_Name || '''' || ' ||' || ' ''~'' ' || '|| ' || '''' ||
i.Batch_User_Id || '''' || ' ||' || ' ''~'' ' || '|| ' || '''' || i
.Created_By || '''' || ' ||' || ' ''~'' ' || '|| ' || '''' ||
i.Approver || '''' || ' ||' || ' ''~'' ' || '|| ' || '''' || i
.Hrs_Outstanding || '''' || ' from dual ';
Dbms_Output.Put_Line('No Problem Batch name' || i.Batch_Name);
Dbms_Output.Put_Line('Length' || Length(t_Sql_Stmt));
EXCEPTION
WHEN OTHERS THEN
NULL;
--dbms_output.put_line('In Exception');
Dbms_Output.Put_Line('Problem with Batch_name: ' || i.Batch_Name);
Dbms_Output.Put_Line('Error: ' || SQLERRM);
Dbms_Output.Put_Line('Length' || Length(t_Sql_Stmt));
END;
END LOOP;
END;
set serverout on
declare
c clob;
begin
c := 'some chars';
for i in 1 .. 24 loop
c := c || c;
dbms_output.put_line('C is now '||length(c));
end loop;
end;
/
Below is the output for code you provided
C is now 20
C is now 40
C is now 80
C is now 160
C is now 320
C is now 640
C is now 1280
C is now 2560
C is now 5120
C is now 10240
C is now 20480
C is now 40960
C is now 81920
C is now 163840
C is now 327680
C is now 655360
C is now 1310720
C is now 2621440
C is now 5242880
C is now 10485760
C is now 20971520
C is now 41943040
C is now 83886080
C is now 167772160
You are being a little loose with your datatype conversions.
If we are dealing with varchar2 across the board, we can map that to a clob, eg
SQL> DECLARE
2 t_Sql_Stmt CLOB DEFAULT Empty_Clob();
3 BEGIN
4 FOR i IN (SELECT *
5 from dba_objects
6 where rownum < 200000 )
7 LOOP
8 t_Sql_Stmt := t_Sql_Stmt ||
9 i.owner ||
10 i.object_name ;
11 END LOOP;
12 Dbms_Output.Put_Line('Length ' || Length(t_Sql_Stmt));
13
14 END;
15 /
Length 2945946
PL/SQL procedure successfully completed.
But if you throw some non-varchar2 into the mix, then we are going to try convert the entire expression to varchar2 in order to get it to work...which then gets into length limits based on varchar2, eg
SQL> DECLARE
2 t_Sql_Stmt CLOB DEFAULT Empty_Clob();
3 BEGIN
4 FOR i IN (SELECT *
5 from dba_objects
6 where rownum < 200000 )
7 LOOP
8 t_Sql_Stmt := t_Sql_Stmt ||
9 i.owner ||
10 i.object_name || i.object_id; --- object_id is numeric
11 END LOOP;
12 Dbms_Output.Put_Line('Length' || Length(t_Sql_Stmt));
13
14 END;
15 /
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 8
ORA-06512: at line 8
So just make sure you are explicit with your datatypes, eg
SQL> DECLARE
2 t_Sql_Stmt CLOB DEFAULT Empty_Clob();
3 BEGIN
4 FOR i IN (SELECT *
5 from dba_objects
6 where rownum < 200000 )
7 LOOP
8 t_Sql_Stmt := t_Sql_Stmt ||
9 i.owner ||
10 i.object_name || cast(i.object_id as varchar2);
11 END LOOP;
12 Dbms_Output.Put_Line('Length ' || Length(t_Sql_Stmt));
13
14 END;
15 /
Length 3333231
PL/SQL procedure successfully completed.
SQL>
SQL> DECLARE
2 t_Sql_Stmt CLOB DEFAULT Empty_Clob();
3 BEGIN
4 FOR i IN (SELECT *
5 from dba_objects
6 where rownum < 200000 )
7 LOOP
8 t_Sql_Stmt := t_Sql_Stmt ||
9 i.owner ||
10 i.object_name || to_char(i.object_id);
11 END LOOP;
12 Dbms_Output.Put_Line('Length ' || Length(t_Sql_Stmt));
13
14 END;
15 /
Length 3333231
PL/SQL procedure successfully completed.