ORA-06502: PL/SQL: numeric or value error: character string buffer too small

[Vinod Vijay]

Hi, I have modified a working PL SQL code by adding some condition(Please see the code below, I have modified where it says //FROM HERE to //TO TILL HERE (both 1 and 2 parts). Now it is not working and I'm getting the following error
ORA-06502: PL/SQL: numeric or value error: character string buffer too small

I tried a lot in figuring out and spent many hours in it but still didn't find any solution. And finally posting it here. Please help me in rectifying this issue.
Please note that I have removed some unwanted code in between which I didn't touched at all.

Warranty_Exp_Ren_Process.sql :-

CREATE OR REPLACE PROCEDURE Warranty_Exp_Ren_Process IS


lr_quote_head       spt_quote_header%ROWTYPE;
lr_warr             warranty_expiration_master%ROWTYPE;

ip_status           NUMBER(1);

lv_err_cnt          NUMBER(10) := 0;
lv_err_msg          VARCHAR2(512);
lv_error            EXCEPTION;
lv_ren_process_msg  VARCHAR2(256) := 'Warranty Expiration Renewal Process ';
lv_process_log_num  NUMBER(10);

lr_adv_mngr         VARCHAR2(8);
lv_agreement_id     VARCHAR2(12);
lv_bill_freq        VARCHAR2(8);
lv_curr_code        VARCHAR2(8);
lv_create_somwft    VARCHAR2(1);
lr_cust_adv         VARCHAR2(8);
lv_cust_num         VARCHAR2(30);
lv_invoice_rule     VARCHAR2(8);
lv_nego_mngr        VARCHAR2(8);
lv_nego_qlid        VARCHAR2(8);
lv_notify_type      VARCHAR2(5);
lv_order_cnt        NUMBER(4);
lv_order_status     VARCHAR2(8);
lv_ppm_contract     BOOLEAN;
lv_ppm_type         VARCHAR2(1);
lv_process_msg      VARCHAR2(1000);
lv_process_status   VARCHAR2(1);
lv_send_email       VARCHAR2(1);
lv_spot_ord_num     VARCHAR2(256);
lv_soln_id          VARCHAR2(2);
lv_som_int_num      NUMBER(16);
lv_tran_type        VARCHAR2(60);
lv_warr_category    VARCHAR2(2);
lv_warr_exp_num     NUMBER(16);
lv_warr_type        VARCHAR2(1);
lv_wot_quote_num    VARCHAR2(10);
lv_om_ord_num       VARCHAR2(18);

lv_prepaid_term     NUMBER(3);  /* TAR 340244 */
lv_contr_term       NUMBER(2);
lv_warr_end_date    DATE;
CURSOR Cr_warr IS SELECT * FROM warranty_expiration_master
    WHERE process_record = 'Y';

CURSOR Cr_SOM_Info IS SELECT DISTINCT NVL(customer_advocate,'Unassigned') CA
    FROM warranty_expiration_master WHERE create_som_request = 'Y';

CURSOR Cr_Create_SOM (ca_qlook IN VARCHAR2) IS
    SELECT warr_exp_process_number exp_num FROM warranty_expiration_master
        WHERE create_som_request = 'Y' AND
               customer_advocate = ca_qlook;
              
CURSOR Cr_Customer_for_unAssignedCA IS                                                        //FROM HERE  -1
    SELECT DISTINCT customer_number cust_num FROM warranty_expiration_master
        WHERE create_som_request = 'Y' AND
               customer_advocate IS NULL;               

CURSOR Cr_Create_SOM_for_unAssignedCA (customer_num IN VARCHAR2) IS
    SELECT warr_exp_process_number exp_num FROM warranty_expiration_master
        WHERE create_som_request = 'Y' AND customer_number = customer_num AND
               customer_advocate IS NULL;                                            //TO TILL HERE -1
------------------------------------------------------------------------------

BEGIN
  
  -----------   //CODE REMOVED
  -------- 

    FOR lr_info IN Cr_SOM_Info
    LOOP
        lv_err_msg := 'Warranty Expiration Process encountered Error '||
                        'Inserting Data to SOM ';
        BEGIN
      
            IF lr_info.CA = 'Unassigned' THEN                                          // FROM HERE -2
            
              --Picking up all distinct customers belonging to Unassigned CA(s)
              FOR lr_cust IN Cr_Customer_for_unAssignedCA 
	           LOOP
	            
	              SELECT som_interface_log_seq.NEXTVAL INTO lv_som_int_num FROM DUAL;
	              
	              lv_err_msg := lv_err_msg||'Interface ';
	              INSERT INTO som_spot_interface (som_interface_number,process_number,
                     som_interface_for, order_or_renewal, message_acknowledged,
                     message_interface_status, interface_sent_date,
                     interface_status_message, updated_date, updated_by)
                  VALUES(lv_som_int_num, 0, 'CA_WARRANTY', 'W', 'I', 'I', SYSDATE,
                     'Interface Log Record Created by Warranty Expiration Process',
                     SYSDATE, 'WARR_EXP_RENEWAL');
	               
	               lv_err_msg := lv_err_msg||'Warr Renewal Mapping ';
	               --For each customers belonging to Unassigned CA(s), picking up all its warranty processes
	                FOR lr_create IN Cr_Create_SOM_for_unAssignedCA(lr_cust.cust_num)
	                  LOOP
	                      INSERT INTO som_warr_renewal_mapping (som_interface_number,
	                               warr_exp_process_number, updated_date)
	                      VALUES (lv_som_int_num, lr_create.exp_num, SYSDATE);
	                  END LOOP;  
	            END LOOP;                                          // TO TILL HERE -2
           ELSE                              
                   
                SELECT som_interface_log_seq.NEXTVAL INTO lv_som_int_num FROM DUAL;

                lv_err_msg := lv_err_msg||'Interface ';
                
                INSERT INTO som_spot_interface (som_interface_number,process_number,
                   som_interface_for, order_or_renewal, message_acknowledged,
                   message_interface_status, interface_sent_date,
                   interface_status_message, updated_date, updated_by)
                VALUES(lv_som_int_num, 0, 'CA_WARRANTY', 'W', 'I', 'I', SYSDATE,
                   'Interface Log Record Created by Warranty Expiration Process',
                   SYSDATE, 'WARR_EXP_RENEWAL');

                lv_err_msg := lv_err_msg||'Warr Renewal Mapping ';
            	FOR lr_create IN Cr_Create_SOM(lr_info.CA)
	             LOOP
	                  INSERT INTO som_warr_renewal_mapping (som_interface_number,
	                      warr_exp_process_number, updated_date)
	                  VALUES (lv_som_int_num, lr_create.exp_num, SYSDATE);
	             END LOOP;
            END IF;
            
        EXCEPTION WHEN OTHERS THEN
            lv_err_cnt := lv_err_cnt + 1;
            lv_err_msg := lv_err_msg||'Table and the Error is - '||SQLERRM;
            SPT_Create_Error_Log(lv_process_log_num, lv_err_cnt, lv_err_msg,
                'WARR_EXP_RENEWAL', NULL, ip_status);
        END;
    END LOOP;

    UPDATE warranty_expiration_master
    SET create_som_request = 'I'
    WHERE create_som_request = 'Y';

    SPT_Create_Import_Log('U', 'E', lv_process_log_num, lv_ren_process_msg||
        'Completed Successfully', 2, 0, 'WARR_EXP_RENEWAL', NULL, 0, 0, 0,
        NULL, ip_status);
EXCEPTION WHEN OTHERS THEN
    ROLLBACK;
    lv_ren_process_msg := lv_ren_process_msg||'Failed Fatally and the Error '||
                        'is - '||SUBSTR(SQLERRM, 1, 120);
    SPT_Create_Import_Log('U', 'E', lv_process_log_num, lv_ren_process_msg, 2,
        0, 'WARR_EXP_RENEWAL', NULL, 0, 0, 0, NULL, ip_status);
END Warranty_Exp_Ren_Process;
/

[Martin Vajsar]

Oracle usually produces stack trace for PL/SQL errors. There will be a line number in the stack trace specifying the point where the error has occurred, this might help you to pinpoint the error. If you still cannot figure it out, post the stacktrace here.

As a rough guess, your usage of the lv_err_msg variable seems strange to me. You append some text to this variable in every iteration of the loop you've added; if the loop is executed lots of times, the resulting text might exceed the declared length of that variable. There might be other problems though.

[Vinod Vijay]

Martin Vajsar wrote:Oracle usually produces stack trace for PL/SQL errors. There will be a line number in the stack trace specifying the point where the error has occurred, this might help you to pinpoint the error. If you still cannot figure it out, post the stacktrace here.

As a rough guess, your usage of the lv_err_msg variable seems strange to me. You append some text to this variable in every iteration of the loop you've added; if the loop is executed lots of times, the resulting text might exceed the declared length of that variable. There might be other problems though.

Thanks Martin, I think this could be the root cause of this issue. I will verify it and update you.

[chris webster]

Side issue: If you have PL/SQL variables that you plan to use for database values, use anchored datatypes (%ROWTYPE or %TYPE) instead of an explicit type/length e.g. lv_customer_name customer_table.customer_name%TYPE. This means (1) you can be sure any value you fetch into the variable will fit in it OK, and (2) if the column length changes you don't have to search through all your code to find the places where your code will break.

Also, for things like messages etc you might as well make your variables big enough for any reasonable value e.g. VARCHAR2(2000) or VARCHAR2(4000) - see the PL/SQL data types reference for your version of Oracle:

Oracle 10g:

Small VARCHAR2 variables are optimized for performance, and larger ones are optimized for efficient memory use. The cutoff point is 2000 bytes. For a VARCHAR2 that is 2000 bytes or longer, PL/SQL dynamically allocates only enough memory to hold the actual value. For a VARCHAR2 variable that is shorter than 2000 bytes, PL/SQL preallocates the full declared length of the variable. For example, if you assign the same 500-byte value to a VARCHAR2(2000 BYTE) variable and to a VARCHAR2(1999 BYTE) variable, the former takes up 500 bytes and the latter takes up 1999 bytes.

Oracle 11g:

Memory Allocation
For a CHAR variable, PL/SQL allocates at compile time enough memory for the maximum size.

For a VARCHAR2 variable, memory allocation depends on maximum size:

If the maximum size is less than 4,000 bytes, PL/SQL allocates at compile time enough memory for the maximum size.

If the maximum size is 4,000 bytes or more, PL/SQL allocates at run time enough memory for the actual value.

For example, suppose that variables a and b are declared as follows:

a VARCHAR2(3999);
b VARCHAR2(4000);
If you assign the same 500-byte value to both variables, PL/SQL allocates 3,999 bytes for a at compile time and 500 bytes for b at run time.

Thus, PL/SQL optimizes smaller VARCHAR2 variables for performance and larger ones for efficient memory use.