proceduer returning error "ORA-01406: fetched column value was truncated"

[taufique]

Dear:

I am trying to execute the following procedure and but it is returing exception "ORA-01406: fetched column value was truncated" at the first cursor fetch:
--------------------------------------------------------------
CREATE OR REPLACE procedure process_payment is

--variables
var_msisdn varchar2 (15);
var_amount number;
err_code varchar2 (2000);

--cursor, Error is coming from here
cursor cur_EVT is
select msisdn,amount
from cust_pay@tovoucher
where status=0;

Begin
FOR w_cur_EVT in cur_EVT LOOP
var_msisdn := w_cur_EVT.msisdn;
var_amount := w_cur_EVT.amount;
--update e-voucher
update cust_pay@tovoucher
set status=9
where msisdn=var_msisdn
and amount=var_amount
and status=0;

commit;
END LOOP;

insert into process_log (TIME_STAMP,STATUS)
(select sysdate,'successful' from dual);

commit;

EXCEPTION
WHEN OTHERS THEN
rollback;
err_code := SQLERRM;
insert into process_log (TIME_STAMP,STATUS,NOTE)
(select sysdate,'failed',err_code from dual);

commit;
end;
/

--------------------------------------------------------------

I am accessing a remote DB through a dblink as shown in the script. I can not figure out the reason. Please help.

-Taufique.

[taufique]

Can anyone help?

[tabreaz]

I think the variable "var_msisdn" is defined to be shorter than the length of data available in database.

Check the msisdn size in the table "cust_pay@tovoucher".

[taufique]

"var_msisdn" is defined as same length of table.

Any other clue?

[tabreaz]

Don't have any idea about that error, but u can try the following statement.

MERGE INTO cust_pay@tovoucher a
USING
	( SELECT msisdn,amount
	  FROM cust_pay@tovoucher
	  WHERE status=0
	)b
ON (
	a.msisdn = b.msisdn
	a.amount = b.amount
   )
WHEN MATCHED THEN
	UPDATE SET
		a.status = 9;