2 Replies Latest reply: Jul 22, 2013 1:09 AM by 986026 RSS

    ORA-06502: PL/SQL: numeric or value error ORA-06512: at "APPS.WF_NOTIFICATION", line 5754 ORA-06512: at line 5

    986026

      Hi,

       

      I have a variable l_body in my pl/sql code with varchar2(32767).

       

      it displays max. 44 lines, more than 44 lines it gives buffer size error.

       

      if I use clob data type then it gives value or numeric erro on more than 44 lines.

       

      code is below

       

      regards

      l_clob       CLOB;

       

      l_body  varchar2(32767);

      BEGIN
             Dbms_lob.CreateTemporary(l_clob,TRUE);



             l_body := null;

           l_body :=
      '<Html>';

           l_body := l_body||
      '<Body>';



      --          l_body := l_body||'<Font Face="Verdana, Arial, Helvetica" Color="#000000">';



      -- l_body := l_body||'<BR> <BR> <BR>' || l_str || '<Br>';

                l_body := l_body||
      '<BR> ' || 'Please note that Receipt Number <B> '|| Trans_Id || ' with amount: ' || trim(to_char(total_amount,'999G999G999G999D99')) ||'</B> has been sent to you for approval. Detail is given below:' || '<Br> <BR> ';

      --------------------------


                l_body := l_body||
      '</Table><Br><Br>';



                l_body := l_body||
      '<Table Border=1">';



      --          l_body := l_body||'<Tr Bgcolor=rgb(0,154,61)>';

                l_body := l_body||
      '<Tr Bgcolor=rgb(162,202,230)>';

                l_body := l_body||
      '<Th><P><Font Size = 2 Face="Verdana, Arial, Helvetica"><div align=left>Date';

              l_body := l_body||
      '<Th><P><Font Size = 2 Face="Verdana, Arial, Helvetica"><div align=left>Vendor Name';

                l_body := l_body||
      '<Th><P><Font Size = 2 Face="Verdana, Arial, Helvetica"><div align=left>PO Number';

                l_body := l_body||
      '<Th><P><Font Size = 2 Face="Verdana, Arial, Helvetica"><div align=left>Release Number';

                l_body := l_body||
      '<Th><P><Font Size = 2 Face="Verdana, Arial, Helvetica"><div align=left>Line';

                l_body := l_body||
      '<Th><P><Font Size = 2 Face="Verdana, Arial, Helvetica"><div align=left>Item Description';

              l_body := l_body||
      '<Th><P><Font Size = 2 Face="Verdana, Arial, Helvetica"><div align=left>Quantity';

                l_body := l_body||
      '<Th><P><Font Size = 2 Face="Verdana, Arial, Helvetica"><div align=left>Unit';

                l_body := l_body||
      '<Th><P><Font Size = 2 Face="Verdana, Arial, Helvetica"><div align=left>PO Unit Price';

                l_body := l_body||
      '<Th><P><Font Size = 2 Face="Verdana, Arial, Helvetica"><div align=left>Currency';

                l_body := l_body||
      '<Th><P><Font Size = 2 Face="Verdana, Arial, Helvetica"><div align=left>Amount';

                l_body := l_body||
      '<Th><P><Font Size = 2 Face="Verdana, Arial, Helvetica"><div align=left>Code Combination';

                l_body := l_body||
      '</Tr>';


             FOR x IN C LOOP

                l_body := l_body||'<Tr>';

                l_body := l_body||
      '<Td><P><Font Size = 2><div align=left>'||x.transaction_date||'</Td>';

                l_body := l_body||
      '<Td><P><Font Size = 2><div align=left>'||x.vendor_name||'</Td>';

              l_body := l_body||
      '<Td><P><Font Size = 2><div align=left>'||x.po_number||'</Td>';

              l_body := l_body||
      '<Td><P><Font Size = 2><div align=left>'||x.release_num||'</Td>';

                l_body := l_body||
      '<Td><P><Font Size = 2><div align=left>'||x.line_num||'</Td>';

                l_body := l_body||
      '<Td><P><Font Size = 2><div align=left>'||x.item_description||'</Td>';

              l_body := l_body||
      '<Td><P><Font Size = 2><div align=right>'||x.quantity||'</Td>';

                l_body := l_body||
      '<Td><P><Font Size = 2><div align=left>'||x.unit_of_measure||'</Td>';

                l_body := l_body||
      '<Td><P><Font Size = 2><div align=right>'||x.po_unit_price||'</Td>';

                l_body := l_body||
      '<Td><P><Font Size = 2><div align=left>'||x.currency_code||'</Td>';

                l_body := l_body||
      '<Td><P><Font Size = 2><div align=right>'||x.amount||'</Td>';

                l_body := l_body||
      '<Td><P><Font Size = 2><div align=left>'||x.cc||'</Td>';

                l_body := l_body||
      '</Tr>';



              END LOOP;

        l_body := l_body||
      '</Table><Br><Br>';



        l_body := l_body||
      '</Body>'||CHR(10);

        l_body := l_body||
      '</Html>'||CHR(10);



              dbms_lob.writeappend(l_clob,LENGTH(l_body),l_body);



      RETURN L_Clob;

          END Format_Notification_Msg;