| ORA-06502: PL/SQL: numeric or value error: character string buffer too small [message #560126] |
Tue, 10 July 2012 14:01  |
 |
born2achieve
Messages: 2
Registered: July 2012
|
Junior Member |
|
|
Hi,
Following are my declarations and query inside my procedure.i am getting
"ORA-06502: PL/SQL: numeric or value error: character string buffer too smal" error when i try to execute the procedure. can any one help me out please
code :
request_owner request.SCC_USER_ID%TYPE := NULL;
receipt_location request.RECEIPT_LOCATION_LKP_ID%TYPE := NULL;
receipt_type request.RECEIPT_TYPE_LKP_ID%TYPE := NULL;
item_id request.ITEM_ID%TYPE := NULL;
material_description imrrequest.MATERIAL_DESCRIPTION%TYPE := NULL;
m_vendor_id vendor.VENDOR_ID%TYPE := NULL;
m_vendor_site_id vendor_site.VENDOR_SITE_ID%TYPE := NULL;
material_manufacturer vendor.VENDOR_ID%TYPE := NULL;
manufacturer_site vendor_site.VENDOR_SITE_ID%TYPE := NULL;
v_finance_project_code request.FINANCE_PROJECT_CODE_LKP_ID%TYPE := NULL;
candidate request.CANDIDATE_LKP_ID%TYPE := NULL;
protocol_number request.PROTOCOL_NUMBER%TYPE := NULL;
quantity_ordered lot.ORDERED_QTY%TYPE := NULL;
uom lot.UOM%TYPE := NULL;
uom_id request.uom_lkp_id%TYPE:= NULL;
hard_demand request.HARD_DEMAND_LKP_ID%TYPE :=NULL;
requested_delivery_date DATE := NULL;
due_date DATE;
controlled_drug request.CONTROLLED_DRUG_LKP_ID%TYPE := NULL;
request_status_lkp_id request.REQUEST_STATUS_LKP_ID%TYPE := NULL;
request_number request.REQUEST_NUM%TYPE := NULL;
related_clementine_status request.REQUEST_NUM%TYPE := NULL;
lot_number lot.LOT_NUMBER%TYPE := NULL;
requestid request.REQUEST_ID%TYPE;
v_request_num request.REQUEST_NUM%TYPE := NULL;
BEGIN
FOR Cur_Rec IN (select lot.lot_id as lot_id,request_lot.request_id as v_request_id,request.workstream as workstream from lot join request_lot on (lot.lot_id = request_lot.lot_id) join request
on (request.request_id = request_lot.request_id) where (request.workstream = 'BDS' or request.workstream = 'CSM'))
LOOP
select request_num,scc_user_id,receipt_location_lkp_id,receipt_type_lkp_id,mid,product,finance_project_code_lkp_id,
candidate_lkp_id,study_num,hard_demand_lkp_id,due_date,controlled_drug_lkp_id,request_status_lkp_id,vendor_id, vendor_site_id
into
request_number,request_owner,receipt_location,receipt_type,item_id,material_description,v_finance_project_code,candidate,
protocol_number,hard_demand,due_date ,controlled_drug,request_status_lkp_id ,m_vendor_id,m_vendor_site_id
from Request where request_id = Cur_Rec.v_request_id;
END LOOP;
END
|
|
|
|
|
|
|
|
| Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too small [message #560130 is a reply to message #560128] |
Tue, 10 July 2012 14:21  |
Solomon Yakobson
Messages: 1397
Registered: January 2010
|
Senior Member |
|
|
You are selecting from table Request while variables used in INTO clause are declared based on columns in other tables. E.g. material_description is declared as imrrequest.MATERIAL_DESCRIPTION%TYPE, m_vendor_id as vendor.VENDOR_ID%TYPE, m_vendor_site_id as vendor_site.VENDOR_SITE_ID%TYPE, etc. And at least one variable declared based on in "other" table has length less than corresponding column in request table.
SY.
|
|
|
|
] 
Members
Search
Help
Register
Login
Home
