3 Replies Latest reply on Feb 28, 2016 11:19 AM by Bashar.

    Fatal PL/SQL error occured

    976758

      Hi all,

      I am writing one formula column in the report like the following.

      function CF_DTNOFormula return Char is
      v_dtno varchar2(4000);

      begin
      V_DTNO := '';
      for rec1 in (select
      rct.ATTRIBUTE2||rct.ATTRIBUTE10||rct.ATTRIBUTE11 DTNO
      FROM ra_customer_trx_all rtl,
      oe_order_headers mm,
      ar_customers ac,
      ra_customer_trx_lines_all rct,
      ra_cust_trx_line_gl_dist_all rgd
      WHERE ac.customer_id = rtl.bill_to_customer_id
      AND to_char(mm.order_number(+))= rtl.interface_header_attribute1
      AND rct.customer_trx_id = rtl.customer_trx_id
      AND rct.customer_trx_line_id = rgd.customer_trx_line_id
      AND rgd.customer_trx_id=:customer_trx_id
      and rct.inventory_item_id=:inventory_item_id
      group by rct.INVENTORY_ITEM_ID , rct.ATTRIBUTE2||rct.ATTRIBUTE10||rct.ATTRIBUTE11
      )
      Loop
      V_DTNO := V_DTNO ||'-'||REC1.DTNO;
      End loop;

      return v_dtno;
      end;

      Here , problem is when the value v_dtno returns more than 4000 characters its getting  REP-1401:Fatal pl/sql error occurred.

      i want to return values more than 4000 how can i achieve this?

      Could any one help me on this, thanks in advance.

      Best Regards

        • 1. Re: Fatal PL/SQL error occured
          Bashar.

          Hi,

           

          Try using varchar2(32768).

          In PL/SQL, the limit on the varchar2 datatype is higher than the database datatype.

           

          How are you going to display this amount of data anyway?!

           

          Regards,

          Bashar

          • 2. Re: Fatal PL/SQL error occured
            976758

            Hi basher,

             

            Thanks for the reply.

            when i changed varchar2(32768) i am getting the following error.

             

            function CF_DTNOFormula return Char is

            v_dtno varchar2(32768);

            begin

            V_DTNO := '';

                for rec1 in (select

               rct.ATTRIBUTE2||rct.ATTRIBUTE10||rct.ATTRIBUTE11 DTNO

               FROM     ra_customer_trx_all rtl,

                oe_order_headers mm,

                         ar_customers ac,

                         ra_customer_trx_lines_all rct,

                         ra_cust_trx_line_gl_dist_all rgd

            WHERE   ac.customer_id = rtl.bill_to_customer_id

            AND     to_char(mm.order_number(+))= rtl.interface_header_attribute1

            AND     rct.customer_trx_id = rtl.customer_trx_id

            AND     rct.customer_trx_line_id = rgd.customer_trx_line_id

            AND     rgd.customer_trx_id=:customer_trx_id

            and     rct.inventory_item_id=:inventory_item_id

            group by rct.INVENTORY_ITEM_ID , rct.ATTRIBUTE2||rct.ATTRIBUTE10||rct.ATTRIBUTE11 

            )

                Loop

                    V_DTNO := V_DTNO ||'-'||REC1.DTNO;

                End loop;

             

             

            return v_dtno;

            end;

             

            snip.PNG

            i am going to display the data in concated format as u can see in the pic -40002-40023-40018-....-40020 in the report.

             

            PLS-00215: String length constraints must be in range (1 .. 32767)

             

            could you please help me

            • 3. Re: Fatal PL/SQL error occured
              Bashar.

              Sorry for the mistake.

              Please use varchar2(32767).

               

              Regards,

              Bashar