DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

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

  1. #1
    Join Date
    Feb 2007
    Posts
    3

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

    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.
    Last edited by taufique; 02-16-2007 at 01:50 AM.

  2. #2
    Join Date
    Feb 2007
    Posts
    3
    Can anyone help?

  3. #3
    Join Date
    Sep 2005
    Posts
    278
    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".

  4. #4
    Join Date
    Feb 2007
    Posts
    3
    "var_msisdn" is defined as same length of table.

    Any other clue?
    Last edited by taufique; 02-17-2007 at 05:37 AM.

  5. #5
    Join Date
    Sep 2005
    Posts
    278
    Don't have any idea about that error, but u can try the following statement.

    Code:
    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;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width