Home » SQL & PL/SQL » SQL & PL/SQL » ORA-06502:number or value error:character string buffer too small(13327) (Version 10.1.2.0.2)
ORA-06502:number or value error:character string buffer too small(13327) [message #337525] Thu, 31 July 2008 02:56 Go to next message
vagabond
Messages: 3
Registered: July 2008
Location: VietNam
Junior Member

hi all,

I have a procedure in DB. In this procedure, I have 1 variable type TABLE%ROWTYPE. But, whenever I access this variable, It raises the error :"ORA-06502:number or value error:character string buffer too small(13327)". I try to use variable type cursor%ROWTYPE but it raises the same error.Pls tell me how can I fix this problem. Here the source codes :

PROCEDURE Generate_File( p_DTR IN OUT FT_TRANSFERS%ROWTYPE,
p_Success OUT BOOLEAN,
p_Err_Msg IN OUT VARCHAR2 )
IS
TYPE Msg_Detail_Type IS TABLE OF VARCHAR2( 500 ) INDEX BY BINARY_INTEGER;
TYPE Msg_Tag_Type IS TABLE OF FT_SWIFT_TAGS.Tag%TYPE INDEX BY BINARY_INTEGER;
TYPE Msg_Index_Type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;

v_Msg_Row_Id ROWID;
v_Step NUMBER;
v_Amount NUMBER;
v_Book_Branch_Client FM_CLIENT.Client_No%TYPE;
v_Stl_Branch_Client FM_CLIENT.Client_No%TYPE;
v_Sender FM_CLIENT.Client_No%TYPE;
v_Receiver FM_CLIENT.Client_No%TYPE;
v_Ord_Cust_FM_Addr FM_CLIENT_CONTACT.Address%TYPE;
v_Ord_Cust_Addr1 VARCHAR2( 35 );
v_Ord_Cust_Addr2 VARCHAR2( 35 );
v_Ord_Cust_Addr3 VARCHAR2( 35 );
v_Ord_Cust_Addr4 VARCHAR2( 35 );
v_Ben_Cust_Addr1 VARCHAR2( 35 );
v_Ben_Cust_Addr2 VARCHAR2( 35 );
v_Ben_Cust_Addr3 VARCHAR2( 35 );
v_Ben_Cust_Addr4 VARCHAR2( 35 );
v_Pay_Dtl1 VARCHAR2( 35 );
v_Pay_Dtl2 VARCHAR2( 35 );
v_Pay_Dtl3 VARCHAR2( 35 );
v_Pay_Dtl4 VARCHAR2( 35 );
v_Pay_Dtl5 VARCHAR2( 35 );
v_Pay_Dtl6 VARCHAR2( 35 );
v_Sen_CI_Code FM_CLIENT_CONTACT.Address%TYPE;
v_Ben_CI_Code FM_CLIENT_CONTACT.Address%TYPE;
v_Sen_PCI_Code FM_CLIENT_CONTACT.Address%TYPE;
v_Ben_PCI_Code FM_CLIENT_CONTACT.Address%TYPE;
v_Found VARCHAR2( 01 );
v_Err VARCHAR2( 500 );
v_Err_Code FM_ERROR.Error_No%TYPE;
t_Details Msg_Detail_Type;
t_Field_Nos Msg_Index_Type;
t_Field_Tags Msg_Tag_Type;
v_Msg_Internal_Key FT_SWIFT_MASTERS.Internal_Key%TYPE;
r_Adv FT_ADV_DETAILS%ROWTYPE;
r_Ord FT_PMTORD_DETAILS%ROWTYPE;

e_Err_Defined EXCEPTION;

BEGIN
r_Adv := NULL;
r_Ord := NULL;

SAVEPOINT GENERATE_PCSFILE;

v_Step := 01;

--*** Retrieve payment/advice details
IF p_DTR.Tran_Class = TRAN_CLASS_INCOMING
THEN
BEGIN
SELECT DAD.*
INTO r_Adv
FROM FT_Adv_Details DAD
WHERE DAD.Internal_Key = p_DTR.Internal_Key;

EXCEPTION
WHEN NO_DATA_FOUND THEN
p_Err_Msg := FT_CMN.Get_Msg( 700501, NULL );
RAISE e_Err_Defined;
END;

ELSIF p_DTR.Tran_Class IN ( TRAN_CLASS_OUTGOING, TRAN_CLASS_INTERNAL )
THEN
BEGIN
SELECT DPD.*
INTO r_Ord
FROM FT_PmtOrd_Details DPD
WHERE DPD.Internal_Key = p_DTR.Internal_Key;

EXCEPTION
WHEN NO_DATA_FOUND THEN
p_Err_Msg := FT_CMN.Get_Msg( 700501, NULL );
RAISE e_Err_Defined;
END;
END IF;

--*** Ordering customer name.
v_Step := 10;
IF r_Ord.ByOrd_Name_Address IS NULL <--ERROR HERE
THEN
Re: ORA-06502:number or value error:character string buffer too small(13327) [message #337533 is a reply to message #337525] Thu, 31 July 2008 03:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Copy and paste what you executed and your table description (how could we know what is X%rowtype?).

Regards
Michel
Re: ORA-06502:number or value error:character string buffer too small(13327) [message #337546 is a reply to message #337525] Thu, 31 July 2008 03:41 Go to previous messageGo to next message
vagabond
Messages: 3
Registered: July 2008
Location: VietNam
Junior Member

Here the source codes (version 10g ):

PROCEDURE Generate_File( p_DTR IN OUT FT_TRANSFERS%ROWTYPE,
p_Success OUT BOOLEAN,
p_Err_Msg IN OUT VARCHAR2 )
IS
...
r_Ord FT_PMTORD_DETAILS%ROWTYPE;
BEGIN
...
r_Ord := NULL;
...
BEGIN
SELECT DPD.*
INTO r_Ord
FROM FT_PmtOrd_Details DPD
WHERE DPD.Internal_Key = p_DTR.Internal_Key;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE e_Err_Defined;
END;

IF r_Ord.ByOrd_Name_Address IS NULL <--ERROR HERE
THEN
...
And the structure of table :
CREATE TABLE FT_PMTORD_DETAILS
(
INTERNAL_KEY NUMBER(12) NOT NULL,
BEN_ACCT_NO VARCHAR2(34 CHAR),
BEN_NAME_ADDRESS VARCHAR2(143 CHAR),
BEN_BNK_ID VARCHAR2(11 CHAR),
BENBNK_NAME_ADDRESS VARCHAR2(143 CHAR),
DETAILS_OF_PMT VARCHAR2(215 CHAR),
DETAILS_OF_CHRG VARCHAR2(3 CHAR),
INTERM_BANK_NAME_ADDRESS VARCHAR2(143 CHAR),
STATE_BANK_NO VARCHAR2(12 CHAR),
PRIORITY VARCHAR2(1 CHAR),
INTERM_BANK_CODE VARCHAR2(8 CHAR),
INTERM_BRANCH_CODE VARCHAR2(6 CHAR),
INTERM_CLEARING_CODE VARCHAR2(8 CHAR),
BEN_BANK_CODE VARCHAR2(8 CHAR),
BEN_BRANCH_CODE VARCHAR2(6 CHAR),
BEN_CLEARING_CODE VARCHAR2(8 CHAR),
SENDER_BANK_CODE VARCHAR2(8 CHAR),
SENDER_BRANCH_CODE VARCHAR2(6 CHAR),
SENDER_CLEARING_CODE VARCHAR2(8 CHAR),
BYORD_NAME_ADDRESS VARCHAR2(143 CHAR),
SENDER_ACCT_NO VARCHAR2(25 CHAR),
DESCRIPTION VARCHAR2(300 CHAR),
ORDER_DETAIL VARCHAR2(3 CHAR),
OPERATION_CODE VARCHAR2(3 CHAR),
DOC_TYPE VARCHAR2(3 CHAR)
)
Re: ORA-06502:number or value error:character string buffer too small(13327) [message #337548 is a reply to message #337546] Thu, 31 July 2008 03:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Thu, 31 July 2008 10:12
please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Copy and paste what you executed and your table description (how could we know what is X%rowtype?).

Regards
Michel


Use guidelines and use code tags.
Re: ORA-06502:number or value error:character string buffer too small(13327) [message #337556 is a reply to message #337525] Thu, 31 July 2008 04:06 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Sorry - I don't believe that the line highlighted is the line causing the error.

I could quite easily be convinced that the next line down is raising the exception, but you don't show any further code.

Show us a cut and paste from SQL*Plus of the results of running the code, along with the code 10 lines either side of the line listed as being the cause of the error.
Re: ORA-06502:number or value error:character string buffer too small(13327) [message #337568 is a reply to message #337556] Thu, 31 July 2008 04:54 Go to previous messageGo to next message
vagabond
Messages: 3
Registered: July 2008
Location: VietNam
Junior Member

But, It's true. In my DB, there are many procedure have the structure like that one but only that one raise error.Pls see the attach file.
By the way, Thanks a lot.

....
v_Step := 10.121;

IF r_PmtOrd.ByOrd_Name_Address IS NULL
THEN
......
  • Attachment: pcs_error.JPG
    (Size: 13.17KB, Downloaded 100 times)
Re: ORA-06502:number or value error:character string buffer too small(13327) [message #337574 is a reply to message #337568] Thu, 31 July 2008 05:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The only thing you can know is that it is between 121 and 122 (if it exists) and not absolutly on this line.
I agree with JRowbottom, your analysis is doubtful.

Regards
Michel
Re: ORA-06502:number or value error:character string buffer too small(13327) [message #337576 is a reply to message #337568] Thu, 31 July 2008 05:19 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You need to show us more code.
Show us everything between v_step := 10.121 and the next time v_step gets set.
Previous Topic: Select and Update Record
Next Topic: ORA-04067 clob2blob procedure error
Goto Forum:
  


Current Time: Thu Dec 08 20:08:20 CST 2016

Total time taken to generate the page: 0.07974 seconds