Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

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

User_ES3X7Jul 18 2013 — edited Jul 22 2013

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;

This post has been answered by Saroja Kandepuneni-Oracle on Jul 21 2013

Jump to Answer

Locked Post

New comments cannot be posted to this locked post.

Locked on Aug 19 2013

Added on Jul 18 2013

#database-key-features, #workflow

2 comments

1,126 views