ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind
jjkFeb 16 2009 — edited Feb 17 2009Hello everybody,
I've got a problem. I'm using Oracle 9i server.
I get the following error when I try to execute the below mentioned below:
Error Encountered:
STARTED FETCHING RECORDS FROM MS AT 16-FEB-09 07.30.07.973326000 PM +05:30
ERROR WHILE PHASE I ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind
STARTED PULLING FMS STATUS AT 16-FEB-09 07.30.08.041204000 PM +05:30
Code Block:
declare
FMS_STATUS_CD NUMBER(1);
CURSOR NEW_CASES IS
SELECT DISTINCT TO_CHAR(ITN.DOCUMENT_NUMBER) DOCUMENT_NUMBER, NI.IDENT_TEXT, TO_CHAR(SR.CUST_ACCT_ID) CUST_ACCT_ID, PROV.PLAN_NAME
FROM NI_ASSIGNED_ITN ITN, NI_NBR_INV NI, SERV_REQ SR, CUST_ACCT CA,
SVCREQ_PROVPLAN SVC, PROVISIONING_PLAN PROV, TASK TSK
WHERE ITN.NBR_INV_ID = NI.NBR_INV_ID
AND ITN.DOCUMENT_NUMBER = SR.DOCUMENT_NUMBER
--JOIN FOR PLAN_NAME
AND ITN.DOCUMENT_NUMBER = SVC.DOCUMENT_NUMBER
AND SVC.PLAN_ID = PROV.PLAN_ID
--ORDER MUST NOT BE CANCELLED AND SHOULD HAVE BEEN RAISED ON 06-NOV-08
AND (SR.SUPPLEMENT_TYPE <> '1' OR SR.SUPPLEMENT_TYPE IS NULL)
--AND TRUNC(SR.BILL_ACT_DT) = TRUNC(SYSDATE - 1)
--PICK ALL ORDERS
AND TSK.TASK_TYPE LIKE 'FMS%'
AND TSK.TASK_STATUS = 'Complete'
AND TSK.DOCUMENT_NUMBER = ITN.DOCUMENT_NUMBER
--PICK POSTPAID DELS ONLY
AND SR.CUST_ACCT_ID = CA.CUST_ACCT_ID
AND CA.CUSTACCT_PRIORITY_CD IN ('19', '20', '21', '22', '23', '25', '26', '27', '28', '88')
AND IDENT_TEXT = '2174290';
TYPE IDENT_TEXT IS TABLE OF VARCHAR2(10);
TYPE DOCUMENT_NUMBER IS TABLE OF VARCHAR2(10);
TYPE PLANS IS TABLE OF VARCHAR2(10);
TYPE ACCOUNT_NUMBER IS TABLE OF VARCHAR2(10);
DELS IDENT_TEXT;
DOCS DOCUMENT_NUMBER;
PLAN_NAME PLANS;
ACCT_ID ACCOUNT_NUMBER;
BEGIN
BEGIN
DBMS_OUTPUT.PUT_LINE ('STARTED FETCHING RECORDS FROM MS AT ' || SYSTIMESTAMP );
OPEN NEW_CASES;
FETCH NEW_CASES BULK COLLECT INTO DOCS, DELS, ACCT_ID, PLAN_NAME;
CLOSE NEW_CASES;
DBMS_OUTPUT.PUT_LINE ('FINISHED FETCHING RECORDS FROM MS AT ' || SYSTIMESTAMP );
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE ('ERROR WHILE PHASE I ' || SQLERRM );
END;
BEGIN
DBMS_OUTPUT.PUT_LINE ('STARTED PULLING FMS STATUS AT ' || SYSTIMESTAMP );
FOR IND IN DOCS.FIRST..DOCS.LAST
LOOP
BEGIN
SELECT DISTINCT 1 FMS_STATUS INTO FMS_STATUS_CD
FROM TIBCOFMS.SUBSCRIBER@FMS_PRE SUB, TIBCOFMS.SUBSCRIBER_PHONE_NUMBER@FMS_PRE SUBPN
WHERE SUB.ACCOUNT_ID = SUBPN.ACCOUNT_ID
AND SUB.DOCUMENT_NUMBER = DOCS(IND)
AND SUB.ACCOUNT_ID = ACCT_ID(IND)
AND SUBPN.PHONE_NUMBER = DELS(IND);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE (sqlerrm);
WHEN TOO_MANY_ROWS THEN
NULL;
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE (SQLERRM);
END;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE ('ERROR WHILE PHASE II ' || SQLERRM );
END;
END;
Thanks in advance for help