Hello,

I am trying to write a pl/sql procedure(oracle) in which I am storing the final output in a clob variable, but it gives me the error when I test the procedure by providing the input: [Error] Execution (1: 1): ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 20

I read on certain blogs which says that CLOB can store upto 4gb of data. How can I write a procedure which will let me store data more than 32 kb in CLOB ? As the data in CLOB reached to 32KB it throws an error. Can someone please help me out? Thank you.

Please see the below sample code:

CREATE OR REPLACE procedure APPS.TESTT1 ( P_START_DATE IN VARCHAR2, P_END_DATE IN VARCHAR2,
p_xml_out OUT Clob
) as

CURSOR cursor1(P_START_DATE VARCHAR2,P_END_DATE VARCHAR2) IS

SELECT
...
FROM ...
....
WHERE ....
and h.ordered_date >=to_date(P_START_DATE,'MM/DD/YYYY')
and h.ordered_date <=to_date(P_END_DATE,'MM/DD/YYYY')
UNION ALL
SELECT ....
FROM ...
WHERE
...
and h.ordered_date >=to_date(P_START_DATE,'MM/DD/YYYY')
and h.ordered_date <=to_date(P_END_DATE,'MM/DD/YYYY')
....


Cursor cursor2(P_ORDER_NUMBER VARCHAR2) IS
SELECT DISTINCT ...
...
FROM ...
WHERE ...
and h.order_number=P_ORDER_NUMBER
UNION ALL
SELECT DISTINCT
....
FROM ....
WHERE ...
and h.order_number=P_ORDER_NUMBER;





begin
p_xml_out :='<?xml version="1.0"?>';
p_xml_out:=p_xml_out||'<Order>';
p_xml_out :=p_xml_out||'<Payload>';
For r_ord_head in cursor1(P_START_DATE,P_END_DATE) loop
p_xml_out :=p_xml_out||'<FullPayload>';
p_xml_out :=p_xml_out||'<orId>'||r_ord_head.ordId ||'</orId>';
p_xml_out :=p_xml_out||'<dl>'||r_ord_head.dealer ||'</dl>';
p_xml_out :=p_xml_out||'<channel>'||r_ord_head.channel ||'</channel>';
p_xml_out :=p_xml_out||'<po>'||r_ord_head.po ||'</po>';
p_xml_out :=p_xml_out||'<sts>'||r_ord_head.stat ||'</sts>';
p_xml_out := p_xml_out||'<gross>'||r_ord_head.grossAmt ||'</gross>';

For r_ord_det in cursor2(r_ord_head.ordId) loop
p_xml_out :=p_xml_out||'<relPayload>'; ;
p_xml_out :=p_xml_out||'<line>'||r_ord_det.lineId ||'</line>' ;
p_xml_out:=p_xml_out||'<item>'||r_ord_det.itemId||'</item>';
p_xml_out :=p_xml_out||'<relS>'||r_ord_det.released_status ||'</relS>' ;
p_xml_out :=p_xml_out||'<itemDesc>'||r_ord_det.item_desc ||'</itemDesc>' ;
p_xml_out :=p_xml_out||'<delId>'||r_ord_det.did ||'</delId>' ;
p_xml_out :=p_xml_out||'<sDate>'||r_ord_det.ship_date ||'</sDate>' ;
p_xml_out :=p_xml_out||'</relPayload>';
end loop;


p_xml_out :=p_xml_out||'</FullPayload>';
end loop;
p_xml_out :=p_xml_out||'</Payload>';
p_xml_out:=p_xml_out||'</Order>';
end;

SQL> CREATE OR REPLACE
  2  procedure my_proc( p_xml_out OUT Clob) as
  3  begin
  4  p_xml_out :='<?xml version="1.0"?>';
  5  p_xml_out :=p_xml_out||'<Order>';
  6  p_xml_out :=p_xml_out||'<Payload>';
  7
  8  for i in 1 .. 1000
  9  loop
 10    p_xml_out :=p_xml_out||'<relPayload>';
 11    p_xml_out :=p_xml_out||'<line>asdasdasd</line>' ;
 12    p_xml_out :=p_xml_out||'<item>asdasdasd</item>';
 13    p_xml_out :=p_xml_out||'<relS>asdasdasd</relS>' ;
 14    p_xml_out :=p_xml_out||'<itemDesc>asdasdasd</itemDesc>' ;
 15    p_xml_out :=p_xml_out||'<delId>asdasdasd</delId>' ;
 16    p_xml_out :=p_xml_out||'<sDate>asdasdasd</sDate>' ;
 17    p_xml_out :=p_xml_out||'</relPayload>';
 18  end loop;
 19
 20  p_xml_out := p_xml_out||'</FullPayload>';
 21  p_xml_out := p_xml_out||'</Payload>';
 22  p_xml_out := p_xml_out||'</Order>';
 23  end;
 24  /

Procedure created.

SQL>
SQL> sho err
No errors.
SQL>
SQL> variable c clob
SQL> exec my_proc(:c);

PL/SQL procedure successfully completed.

SQL> set serverout on
SQL> exec dbms_output.put_line(dbms_lob.getlength(:c));
169069

PL/SQL procedure successfully completed.

SQL>
SQL> variable c1 varchar2(4000)
SQL> exec my_proc(:c1);
BEGIN my_proc(:c1); END;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 1


SQL>
SQL>