Hi,
When I am executing the following anonymous pl/sql block I am getting the error ORA-06502: PL/SQL: numeric or value error
set serveroutput on;
declare
l_dyn_str_1 LONG;
l_dyn_str_2 CLOB;
l_dyn_str_size int;
l_umatch_target varchar2(50):='JTF_TAE_1001_ACCOUNT_UMATCHES';
l_dmc_winner_target varchar2(50):='JTF_TAE_1001_ACCOUNT_UMATCHES';
l_max_terr number(10):=344;
BEGIN
l_dyn_str_1 :=
'insert into ' || l_umatch_target ||
' (trans_object_id,' ||
'trans_detail_object_id,' ||
'terr_id,' ||
'absolute_rank,' ||
'txn_date,' ||
'worker_id)(';
-- SOLIN, bug 5633062
-- move jtf_terr_all out of inline view to avoid
-- database bug
l_dyn_str_1 := l_dyn_str_1 ||
'select ' ||
'ilv.trans_object_id,' ||
'-1,' || --trans_detail_object_id
'ilv.terr_id,' ||
'jt.absolute_rank,' ||
'null,' ||
'1' ||
' from (' ;
dbms_output.put_line('Before Loop');
for i IN 1..l_max_terr loop
l_dyn_str_2 := l_dyn_str_2 ||
'select ' ||
'a.link trans_object_id,' ||
-- ' -1 trans_detail_object_id, ' ||
'a.terr_id' || i || ' terr_id ' ||
-- ' b.absolute_rank, ' ||
'from ' || l_dmc_winner_target || ' a ';
if (i < l_max_terr) then
l_dyn_str_2 := l_dyn_str_2 || ' union ';
end if;
end loop;
dbms_output.put_line('Loop Ended');
l_dyn_str_2 := l_dyn_str_2 || ' ) ilv, ' ||
' jtf_terr_all jt ' ||
'where ilv.terr_id = jt.terr_id) ';
l_dyn_str_size:= DBMS_LOB.getlength(l_dyn_str_2);
dbms_output.put_line('The size of l_dyn_str_2 is in charcters is '||l_dyn_str_size);
l_dyn_str_size:= LENGTHB(TO_CHAR(SUBSTR(l_dyn_str_2,1,65567)));
dbms_output.put_line('The size of l_dyn_str_2 in bytes is '||l_dyn_str_size);
dbms_output.put_line('The size of total string is '||DBMS_LOB.getlength(l_dyn_str_1||l_dyn_str_2));
dbms_output.put_line(l_dyn_str_1||l_dyn_str_2);
END;
/
The output is:
===========
Error report:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 63
06502. 00000 - "PL/SQL: numeric or value error%s"
*Cause:
*Action:
Before Loop
Loop Ended
The size of l_dyn_str_2 is in charcters is 32622
The size of l_dyn_str_2 in bytes is 32622
The size of total string is 32818
The last print message was not printed since the variable l_dyn_str_1 || l_dyn_str_2 has the size 32818 and I guess a CLOB can not accommodate a variable of this size. Am I right?
And when I change the value of l_max_terr in the script to 343, the it is getting executed successfully.
The out put is :
----------------------
anonymous block completed
Before Loop
Loop Ended
The size of l_dyn_str_2 is in charcters is 32527
The size of l_dyn_str_2 in bytes is 32527
The size of total string is 32723
insert into JTF_TAE_1001_ACCOUNT_UMATCHES (trans_object_id,trans_detail_object_id,terr_id,absolute_rank,txn_date,worker_id)(select ilv.trans_object_id,-1,ilv.terr_id,jt.absolute_rank,null,1 from (select a.link trans_object_id,a.terr_id1 terr_id from JTF_TAE_1001_ACCOUNT_UMATCHES a union select a.link trans_object_id,a.terr_id2 terr_id from JTF_TAE_1001_ACCOUNT_UMATCHES a union ...(I didnt paste the whole string because it is very large).
So I would like to know two thing here.
1. What is the maximum size of a CLOB variable?
2. What we have to do when we are dealing with l_max_terr > 343 (My requirement is 980)?
Os there any way or is there any variable that can accommodate to run this script successfully?