?
Solved

ORA-06502: PL/SQL: numeric or value error

Posted on 2005-04-19
9
Medium Priority
?
2,604 Views
Last Modified: 2008-01-16
Hello,

I am getting error when executing a stand alone procude in sqlplus:

ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "MPLS_DEV.PVC_VALIDATION_EXCEPTIONS", line 91
ORA-06512: at line 1

Can someone please help resolve this error and explain why this error is occuring.

Thanks!!

CREATE OR REPLACE PROCEDURE Pvc_Validation_Exceptions
AS

    v_ne_id            PVC.NETWORK_ELEMENT_ID%TYPE;
      v_ne_name          PVC.NETWORK_ELEMENT_NAME%TYPE;
      v_ne_expiry_date   PVC.PVC_EXPIRY_DATE%TYPE;
      v_int_desc         PVC.INTERFACE_DESCRIPTOR%TYPE;
      v_pvc_ind          PVC.PVC_IND%TYPE;
      v_hub_neid         PVC.HUB_NETWORK_ELEMENT_ID%TYPE;
      v_rmt_ne_id        PVC.RMT_NETWORK_ELEMENT_ID%TYPE;      
      v_count1 PLS_INTEGER := 0;
      
      
      /* 20004 - Check if an active CIU in network_element is not in iol.nh_statistics_lan_wan table */
      CURSOR c1_ciuactive
      IS
      SELECT COUNT(*)
            ,network_element_name
    FROM   customer.NETWORK_ELEMENT
      WHERE ne_status = 'Active'
      AND customer_id = 777776
      AND network_element_id  
      NOT IN
      (SELECT network_element_id FROM iol.NH_STATISTICS_LAN_WAN)
      GROUP BY
            network_element_name;      
             
   
    /* 20005 -  Check if CIU and DLCI in PVC table are not in IOL.NH_STATISTICS_LAN_WAN table */
    CURSOR c1_ciuvalidation
      IS
      SELECT COUNT(*)
            ,NETWORK_ELEMENT_id
              ,network_element_name
              ,interface_descriptor
      FROM PVC
      WHERE network_element_id
      NOT IN
        (SELECT NETWORK_ELEMENT_id FROM iol.NH_STATISTICS_LAN_WAN)
      GROUP BY
             NETWORK_ELEMENT_id
              ,network_element_name
              ,interface_descriptor              
      ORDER BY 3;
      
    /* 20006 - Find if duplicate Remote Entry exist in the PVC table */
    CURSOR c1_pvcduplicate
      IS
      SELECT COUNT(*)
            ,network_element_id
              ,network_element_name
              ,interface_descriptor
              ,pvc_ind
              ,hub_network_element_id
      FROM PVC
      WHERE network_element_id = network_element_id
      AND network_element_name = network_element_name
      AND pvc_ind = pvc_ind
      AND hub_network_element_id = hub_network_element_id
      AND interface_descriptor = interface_descriptor
      GROUP BY network_element_id
              ,network_element_name
                  ,interface_descriptor
                  ,pvc_ind
                  ,hub_network_element_id;
                  
   /* 20007 - Check if PVC record is expired in IOL.PVC table and it is in IOL.NH_STATISTICS_LAN_WAN table */
    CURSOR c1_pvcexpired
      IS
      SELECT COUNT(*)
            ,rmt_network_element_id
              ,v_ne_name
              ,v_int_desc              
      FROM PVC
      WHERE PVC_expiry_date IS NOT NULL
      AND rmt_network_element_id
      IN
        (SELECT network_element_id FROM iol.NH_STATISTICS_LAN_WAN)
      GROUP BY rmt_network_element_id
              ,v_ne_name
                ,v_int_desc;

                  
BEGIN
     
    /* Check if CIU is in network_element table and "Active", not in iol.nh_statistics_lan_wan table */
     OPEN c1_ciuactive;
         LOOP
           FETCH c1_ciuactive INTO v_count1, v_ne_name;
             EXIT WHEN c1_ciuactive%NOTFOUND;            
             IF v_count1 > 0 THEN
             INSERT
             INTO mpls_dev.CUSTOMER_EXCEPTIONS(CUSTOMER_ID
                                              ,PROCESS_NAME
                          ,CODE_UNIT_NAME
                          ,ERROR_ID
                         ,QUALIFER_TYPE
                                   ,QUALIFIER_VALUE
                                   ,CREATE_DATE_TIME
                         ,EXCEPTION_DATE)
            VALUES                 (777776
                                    ,'Data Load'
                         ,'IOL_DATA_EXCEPTIONS'
                         ,20004
                         ,'Network Element Name'
                         ,v_ne_name
                         ,NULL
                         ,SYSDATE);                                                                    END IF;
            END LOOP;
        CLOSE c1_ciuactive;
    COMMIT;
      
      
    /* Check if CIU and DLCI in PVC table are not in IOL.NH_STATISTICS_LAN_WAN table */
        OPEN c1_ciuvalidation;
          LOOP
              FETCH c1_ciuvalidation INTO v_count1, v_ne_id, v_ne_name, v_int_desc;
              EXIT WHEN c1_ciuvalidation%NOTFOUND;
              IF v_count1 > 0 THEN
              INSERT
              INTO mpls_dev.CUSTOMER_EXCEPTIONS(CUSTOMER_ID
                                              ,PROCESS_NAME
                                           ,CODE_UNIT_NAME
                          ,ERROR_ID
                          ,QUALIFER_TYPE
                          ,QUALIFIER_VALUE
                         ,CREATE_DATE_TIME
                         ,EXCEPTION_DATE)
      VALUES                                      (777776
                        ,'Data Load'
                        ,'IOL DATA EXCEPTIONS'
                        ,20005
                        ,'PVC Name'
                        ,v_ne_name ||'-'||v_int_desc
                        ,NULL
                        ,SYSDATE);
              END IF;
            END LOOP;
         CLOSE c1_ciuvalidation;
       COMMIT;       
      
      
   /* Find if duplicate Remote Entry exist in the PVC table */
       OPEN c1_pvcduplicate;
         LOOP
           FETCH c1_pvcduplicate INTO v_count1, v_ne_id, v_ne_name, v_int_desc, v_pvc_ind, v_hub_neid;
             EXIT WHEN c1_pvcduplicate%NOTFOUND;
             
             IF v_count1 > 1 THEN              
             INSERT
             INTO mpls_dev.CUSTOMER_EXCEPTIONS(CUSTOMER_ID
                                              ,PROCESS_NAME
                          ,CODE_UNIT_NAME
                          ,ERROR_ID
                         ,QUALIFER_TYPE
                         ,QUALIFIER_VALUE
                         ,CREATE_DATE_TIME
                         ,EXCEPTION_DATE)
                   VALUES             (777776
                          ,'Data Load'
                          ,'IOL DATA EXCEPTIONS'
                          ,20006
                         ,'PVC Name'
                         ,v_ne_name ||'-'||v_int_desc
                         ,NULL
                         ,SYSDATE);
              END IF;
            END LOOP;
        CLOSE c1_pvcduplicate;
       COMMIT;
      
      /* Check if PVC record is expired in IOL.PVC table and it is in IOL.NH_STATISTICS_LAN_WAN table */
       OPEN c1_pvcexpired;
         LOOP
           FETCH c1_pvcexpired INTO v_count1, v_rmt_ne_id, v_ne_name, v_int_desc;
             EXIT WHEN c1_pvcexpired%NOTFOUND;
             
             IF v_count1 > 2 THEN              
             INSERT
            INTO mpls_dev.CUSTOMER_EXCEPTIONS(CUSTOMER_ID
                                              ,PROCESS_NAME
                         ,CODE_UNIT_NAME
                         ,ERROR_ID
                         ,QUALIFER_TYPE
                        ,QUALIFIER_VALUE
                         ,CREATE_DATE_TIME
                         ,EXCEPTION_DATE)
      VALUES                  (777776
                        ,'Data Load'
                        ,'IOL DATA EXCEPTIONS'
                        ,20007
                        ,'PVCExpired PVC found in the PVC table'
                        ,v_ne_name ||'-'||v_int_desc
                        ,NULL
                        ,SYSDATE);
              END IF;
            END LOOP;
        CLOSE c1_pvcexpired;
       COMMIT;
END;
/
0
Comment
Question by:mohammadzahid
  • 4
  • 3
  • 2
9 Comments
 
LVL 15

Assisted Solution

by:ishando
ishando earned 1800 total points
ID: 13820930
Are the columns PVC.NETWORK_ELEMENT_NAME and NETWORK_ELEMENT.NETWORK_ELEMENT_NAME the same size?

It could be that NETWORK_ELEMENT.NETWORK_ELEMENT_NAME (the value you are selecting) is larger than PVC.NETWORK_ELEMENT_NAME the type you define your variable as.
0
 
LVL 11

Author Comment

by:mohammadzahid
ID: 13820987
No they are not the same size.

PVC.NETWORK_ELEMENT_NAME  varchar2(20)
NETWORK_ELEMENT.NETWORK_ELEMENT_NAME varchar2(30)

Should I increase the size of PVC.NETWORK_ELEMENT_NAME  varchar2(20)  --> PVC.NETWORK_ELEMENT_NAME  varchar2(30)

will that resolve the error I am getting?

Thanks,

-mohammadzahid
0
 
LVL 15

Expert Comment

by:ishando
ID: 13821011
It may do - because you are defining

  v_ne_name          PVC.NETWORK_ELEMENT_NAME%TYPE;

its only varchar2(20), but you are selecting from a field which is varchar2(30) - if the value selected is more than 20 you will get this error.

You could increase PVC.NETWORK_ELEMENT_NAME to varchar2(30)
or define
  v_ne_name        NETWORK_ELEMENT.NETWORK_ELEMENT_NAME%TYPE;  
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 11

Author Comment

by:mohammadzahid
ID: 13821012
Your suggestion resolved the problem. I increased the size of NETWORK_ELEMENT_NAME in PVC table (greater then NETWORK_ELEMENT.NETWORK_ELEMENT_NAME), compiled and executed. Procedure ran successfully.

I will really appreciate if you please give me brief description of why this error occured?

Thank you so much!!

-mohammadzahid
0
 
LVL 25

Accepted Solution

by:
jrb1 earned 200 total points
ID: 13821205
You were trying to read a value from NETWORK_ELEMENT.NETWORK_ELEMENT_NAME which in your DB was longer than 20 characters...but you were trying to read it in to a variable that was defined as varchar2(20).  Oracle will not just truncate in this case--it errors.
0
 
LVL 11

Author Comment

by:mohammadzahid
ID: 13821232
Many thanks for quick and accurate explanation of the error.  

-mohammadzahid

0
 
LVL 25

Expert Comment

by:jrb1
ID: 13821330
You're welcome.  (In the case where the answer comes from multiple people, you can go past the last comment and select "split points"...and that way reward everyone for their help.)
0
 
LVL 11

Author Comment

by:mohammadzahid
ID: 13821346
Yes. I did just that. Thanks!

0
 
LVL 25

Expert Comment

by:jrb1
ID: 13821407
Oh, good...didn't see the assist...was afraid ishando didn't get any credit.  Thanks!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
If you try to migrate from Elastix to Issabel, you will face a lot of issues. These problems are inevitable but fortunately, you can fix them. In the guide below, I will explain how I performed the migration while keeping all data and successfully t…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Suggested Courses

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question