mohammadzahid
asked on
ORA-06502: PL/SQL: numeric or value error
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_E XCEPTIONS" , 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%TYP E;
v_ne_name PVC.NETWORK_ELEMENT_NAME%T YPE;
v_ne_expiry_date PVC.PVC_EXPIRY_DATE%TYPE;
v_int_desc PVC.INTERFACE_DESCRIPTOR%T YPE;
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_EXCEPTIO NS(CUSTOME R_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_EXCEPTIO NS(CUSTOME R_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_EXCEPTIO NS(CUSTOME R_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_EXCEPTIO NS(CUSTOME R_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;
/
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_E
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%TYP
v_ne_name PVC.NETWORK_ELEMENT_NAME%T
v_ne_expiry_date PVC.PVC_EXPIRY_DATE%TYPE;
v_int_desc PVC.INTERFACE_DESCRIPTOR%T
v_pvc_ind PVC.PVC_IND%TYPE;
v_hub_neid PVC.HUB_NETWORK_ELEMENT_ID
v_rmt_ne_id PVC.RMT_NETWORK_ELEMENT_ID
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_EXCEPTIO
,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_EXCEPTIO
,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_EXCEPTIO
,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_EXCEPTIO
,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;
/
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
It may do - because you are defining
v_ne_name PVC.NETWORK_ELEMENT_NAME%T YPE;
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_EL EMENT_NAME %TYPE;
v_ne_name PVC.NETWORK_ELEMENT_NAME%T
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_EL
ASKER
Your suggestion resolved the problem. I increased the size of NETWORK_ELEMENT_NAME in PVC table (greater then NETWORK_ELEMENT.NETWORK_EL EMENT_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
I will really appreciate if you please give me brief description of why this error occured?
Thank you so much!!
-mohammadzahid
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Many thanks for quick and accurate explanation of the error.
-mohammadzahid
-mohammadzahid
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.)
ASKER
Yes. I did just that. Thanks!
Oh, good...didn't see the assist...was afraid ishando didn't get any credit. Thanks!
ASKER
PVC.NETWORK_ELEMENT_NAME varchar2(20)
NETWORK_ELEMENT.NETWORK_EL
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