Home » SQL & PL/SQL » SQL & PL/SQL » pl/sql returns "ORA-01406: fetched column value was truncated"
icon5.gif  pl/sql returns "ORA-01406: fetched column value was truncated" [message #219799] Fri, 16 February 2007 03:20 Go to next message
halogen
Messages: 12
Registered: February 2007
Junior Member
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.

-Halogen.
Re: pl/sql returns "ORA-01406: fetched column value was truncated" [message #219801 is a reply to message #219799] Fri, 16 February 2007 03:24 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Which Oracle version ?

Also is var_msisdn varchar2 (15); big enough too hold the value ?

[Updated on: Fri, 16 February 2007 03:26]

Report message to a moderator

Re: pl/sql returns "ORA-01406: fetched column value was truncated" [message #219802 is a reply to message #219801] Fri, 16 February 2007 03:26 Go to previous messageGo to next message
halogen
Messages: 12
Registered: February 2007
Junior Member
oracle 9i
Re: pl/sql returns "ORA-01406: fetched column value was truncated" [message #219803 is a reply to message #219802] Fri, 16 February 2007 03:27 Go to previous messageGo to next message
halogen
Messages: 12
Registered: February 2007
Junior Member
var_MSISDN is simple varchar (20)
Re: pl/sql returns "ORA-01406: fetched column value was truncated" [message #219804 is a reply to message #219803] Fri, 16 February 2007 03:32 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

halogen wrote on Fri, 16 February 2007 10:27
var_MSISDN is simple varchar (20)


Oki but why then have you declared
var_msisdn as varchar2 (15) in your code.

Re: pl/sql returns "ORA-01406: fetched column value was truncated" [message #219808 is a reply to message #219804] Fri, 16 February 2007 03:38 Go to previous messageGo to next message
halogen
Messages: 12
Registered: February 2007
Junior Member
sorry the length 15 at the column definition. So the varibale length is matching.
Re: pl/sql returns "ORA-01406: fetched column value was truncated" [message #219812 is a reply to message #219808] Fri, 16 February 2007 03:42 Go to previous messageGo to next message
halogen
Messages: 12
Registered: February 2007
Junior Member
Hello tahpush:

When there is record at the first cursor, only then I am getting error. If there is no record at the cursor, procedure gets executed successfylly.

[Updated on: Fri, 16 February 2007 03:43]

Report message to a moderator

Re: pl/sql returns "ORA-01406: fetched column value was truncated" [message #219815 is a reply to message #219808] Fri, 16 February 2007 03:44 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Hm, care to share a copy-paste from sqlplus of a desc of your table?
How could you first say it is a varchar2(20) and then all of a sudden it turns out to be a varchar2(15)?
Re: pl/sql returns "ORA-01406: fetched column value was truncated" [message #219819 is a reply to message #219799] Fri, 16 February 2007 03:55 Go to previous messageGo to next message
halogen
Messages: 12
Registered: February 2007
Junior Member
desc of table:

Name Null? Type
----------------------------------------- -------- ----------------------------
CIRCLE_ID NOT NULL NUMBER
MSISDN NOT NULL VARCHAR2(15)
CUST_CODE NOT NULL VARCHAR2(64)
AMOUNT NOT NULL NUMBER
TRANS_NUMB NOT NULL VARCHAR2(20)
STATUS NOT NULL NUMBER
ENTRY_DATE NOT NULL DATE
PROCESS_ID NUMBER
PROCESS_DATE DATE
CHANNEL NOT NULL VARCHAR2(50)
INFO1 NOT NULL VARCHAR2(20)
INFO2 NOT NULL VARCHAR2(50)
INFO3 NOT NULL NUMBER
INFO4 NOT NULL VARCHAR2(50)
PROCESS_STATUS NUMBER
POSTED_DATE DATE
DESCRIPTION VARCHAR2(30)
Re: pl/sql returns "ORA-01406: fetched column value was truncated" [message #219823 is a reply to message #219819] Fri, 16 February 2007 04:01 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
and what about the process_log table?
Could it be that the column status is not >= varchar2(10) ?

[Updated on: Fri, 16 February 2007 04:02]

Report message to a moderator

Re: pl/sql returns "ORA-01406: fetched column value was truncated" [message #219824 is a reply to message #219823] Fri, 16 February 2007 04:04 Go to previous messageGo to next message
halogen
Messages: 12
Registered: February 2007
Junior Member
two are different tables. Truncation error is coming at the first cursor fetch!!
Re: pl/sql returns "ORA-01406: fetched column value was truncated" [message #219826 is a reply to message #219824] Fri, 16 February 2007 04:10 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Can you do a dbms_output.put_line of the values
when you run the procedure and remove the update ?
Re: pl/sql returns "ORA-01406: fetched column value was truncated" [message #219828 is a reply to message #219826] Fri, 16 February 2007 04:16 Go to previous messageGo to next message
halogen
Messages: 12
Registered: February 2007
Junior Member
the error is coming at the time of fetching, exactly at:

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


For this values are not traceable.
Re: pl/sql returns "ORA-01406: fetched column value was truncated" [message #219830 is a reply to message #219828] Fri, 16 February 2007 04:27 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Just to make sure

Declare your variables as

var_msisdn  tablename.colname@databaselink%TYPE

and so on

[Updated on: Fri, 16 February 2007 04:29]

Report message to a moderator

Re: pl/sql returns "ORA-01406: fetched column value was truncated" [message #219854 is a reply to message #219799] Fri, 16 February 2007 06:30 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Just wonder if you got it sorted ?
Razz
Re: pl/sql returns "ORA-01406: fetched column value was truncated" [message #219870 is a reply to message #219854] Fri, 16 February 2007 08:28 Go to previous messageGo to next message
halogen
Messages: 12
Registered: February 2007
Junior Member
Thanks for the idea. I will let you know by tomorrow ( I am waiting to have connectivity with remote db).
Re: pl/sql returns "ORA-01406: fetched column value was truncated" [message #219877 is a reply to message #219870] Fri, 16 February 2007 08:47 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
How do you know where the error occurs?? Your WHEN OTHERS exception would hide the linenumber where the error was raised!
Re: pl/sql returns "ORA-01406: fetched column value was truncated" [message #219895 is a reply to message #219877] Fri, 16 February 2007 10:23 Go to previous messageGo to next message
halogen
Messages: 12
Registered: February 2007
Junior Member
it's from debug/trace through pl/sql developer.
the error is being raised at the first cursor fetch.
Re: pl/sql returns "ORA-01406: fetched column value was truncated" [message #219901 is a reply to message #219799] Fri, 16 February 2007 10:33 Go to previous messageGo to next message
halogen
Messages: 12
Registered: February 2007
Junior Member
This is the response from oracle site on my raised issue, need clearer info on what to do Embarassed

ORA-01406: fetched column value was truncated
Cause: In a host language program, a FETCH operation was forced to truncate a character string. The program buffer area for this column was not large enough to contain the entire string. The cursor return code from the fetch was +3.

Action: Increase the column buffer area to hold the largest column value or perform other appropriate processing.
icon14.gif  Re: pl/sql returns "ORA-01406: fetched column value was truncated" [message #220047 is a reply to message #219901] Sun, 18 February 2007 02:42 Go to previous message
halogen
Messages: 12
Registered: February 2007
Junior Member
I have used substr function at fething time, now its working:

---------------------------------------
select substr(msisdn,-10) msisdn,amount
from cust_pay@tovoucher
where status=0;
---------------------------------------

Thank you all for your help.
Previous Topic: NLS_LANG PROBLEM
Next Topic: pl/sql question
Goto Forum:
  


Current Time: Fri Dec 02 23:16:29 CST 2016

Total time taken to generate the page: 0.15909 seconds