ORA-06502: PL/SQL: numeric or value error: character to number conversion error
24,683
Your problem is that your procedure has more parameters then you pass to it on MyBatis call, so at any point after the missing parameter you should have your ORA-06502: PL/SQL: numeric or value error error since the following paramters doesn't have the same type on the order you are passing it.
Your procedure has this:
(...)
p_EXP_PERIOD item.EXP_PERIOD%type,
p_CODE item.CODE%type,
p_UM item_pack.UM%type,
p_IMG item_pack.IMG%type,
p_CONV_FACTOR item_pack.CONV_FACTOR%type,
p_VOL item_pack.VOL%type,
(...)
On your call in MyBatis you are doing:
(...)
#{p_exp_period,jdbcType=DOUBLE},
#{p_code,jdbcType=VARCHAR},
#{p_um,jdbcType=VARCHAR},
#{p_conv_factor,jdbcType=DOUBLE},
#{p_vol,jdbcType=INTEGER},
#{p_info,jdbcType=VARCHAR},
(...)
So after the p_IMG item_pack.IMG%type, that you are not passing any miss type would cause your error.
Related videos on Youtube
23 : 32
PL/SQL Tutorial | User Defined Exception | Pragma Exception_INIT in Oracle Database 11g
04 : 45
Oracle PL/SQL Tutorial - Numbers
02 : 02
What does it mean? ORA-06502: PL/SQL: numeric or value error: character string buffer too small
17 : 24
Oracle Tutorial - Conversion Functions TO_DATE | TO_CHAR | TO_NUMBER
04 : 13
Oracle TO_CHAR Function
04 : 24
Tutorial#45 TO_NUMBER function | Convert varchar value into number format
12 : 07
How to get the exact line number for an error in Oracle PL/SQL?
12 : 11
About that error: ORA-06502: numeric or value error
10 : 36
1. How to resolve the Numeric Value is not recognized Error in Snowflake|Snowflake Errors|VCKLY Tech
05 : 23
ORA 01078 failure in processing system parameters Oracle Database error
02 : 16
ora-03114 :not connected to ORACLE
17 : 54
PL/SQL Tutorial #18: System defined exceptions with Simple Examples
Author by
Theo
Updated on July 09, 2022Comments
-
Theo almost 2 years
is there any way to specify in a mybatis resultMap that a String should have 20 characters at most? or how to set the precission, like NUMBER(4,2) ?
for example I have a result map that looks like this:
<resultMap id="IMap" type="com.optsol.beans.Item"> <result property="item_id" column="ITEM_ID" jdbcType="INTEGER" /> <result property="item_desc" column="ITEM_DESC" jdbcType="VARCHAR" /> <result property="min_qty" column="MIN_QTY" jdbcType="INTEGER" /> <result property="max_qty" column="MAX_QTY" jdbcType="INTEGER" /> <result property="abc" column="ABC" jdbcType="VARCHAR" /> <result property="item_weight" column="ITEM_WEIGHT" jdbcType="DOUBLE" /> <result property="size_cl" column="SIZE_CL" jdbcType="INTEGER" /> <result property="weight_class" column="WEIGHT_CLASS" jdbcType="INTEGER" /> <result property="exp_period" column="EXP_PERIOD" jdbcType="DOUBLE" /> <result property="code" column="CODE" jdbcType="VARCHAR" /> <result property="um" column="UM" jdbcType="VARCHAR" /> <result property="img" column="IMG" jdbcType="VARCHAR" /> <result property="conv_factor" column="CONV_FACTOR" jdbcType="DOUBLE" /> <result property="vol" column="VOL" jdbcType="INTEGER" /> <result property="info" column="INFO" jdbcType="VARCHAR" /> <result property="statist_report" column="STATIST_PERIOD" jdbcType="INTEGER" /> <result property="pack_parent" column="PACK_PARENT" jdbcType="INTEGER" /> <result property="group_parent" column="GROUP_PARENT" jdbcType="INTEGER" /> <result property="order_full" column="ORDER_FULL" jdbcType="INTEGER" /> <result property="insDate" column="INSDATE" /> <result property="updDate" column="UPDDATE" /> <result property="insUser" column="INSUSER" jdbcType="VARCHAR" /> <result property="updUser" column="UPDUSER" jdbcType="VARCHAR" /> </resultMap>can I limit the number of digits of int to be only 1, show N elements after the "." on a floating point number or limit the String size to a certain number?
the view I'm working on has this column structure:
('ITEM_ID','NUMBER(10)','No',null,1,null,'NO','NO','NO'); ('ITEM_DESC','VARCHAR2(80)','No',null,2,null,'NO','NO','NO'); ('MIN_QTY','NUMBER(10)','Yes',null,3,null,'NO','NO','NO'); ('MAX_QTY','NUMBER(10)','Yes',null,4,null,'NO','NO','NO'); ('ABC','VARCHAR2(1)','No',null,5,null,'NO','NO','NO'); ('ITEM_WEIGHT','NUMBER(10,3)','Yes',null,6,null,'NO','NO','NO'); ('SIZE_CL','NUMBER(2)','Yes',null,7,null,'NO','NO','NO'); ('WEIGHT_CLASS','NUMBER(2)','Yes',null,8,null,'NO','NO','NO'); ('EXP_PERIOD','NUMBER(10,3)','Yes',null,9,null,'NO','NO','NO'); ('CODE','VARCHAR2(20)','Yes',null,10,null,'NO','NO','NO'); ('UM','VARCHAR2(15)','No',null,11,null,'NO','NO','NO'); ('IMG','VARCHAR2(100)','Yes',null,12,null,'NO','NO','NO'); ('CONV_FACTOR','NUMBER(10,4)','Yes',null,13,null,'NO','NO','NO'); ('VOL','NUMBER(10)','Yes',null,14,null,'NO','NO','NO'); ('INFO','VARCHAR2(100)','Yes',null,15,null,'NO','NO','NO'); ('STATIST_PERIOD','NUMBER(5)','Yes',null,16,null,'NO','NO','NO'); ('PACK_PARENT','NUMBER(10)','Yes',null,17,null,'NO','NO','NO'); ('GROUP_PARENT','NUMBER(10)','Yes',null,18,null,'NO','NO','NO'); ('ORDER_FULL','NUMBER(1)','No',null,19,null,'NO','NO','NO'); ('INSDATE','DATE','No',null,20,null,'NO','NO','NO'); ('UPDDATE','DATE','No',null,21,null,'NO','NO','NO'); ('INSUSER','VARCHAR2(4000)','Yes',null,22,null,'NO','NO','NO'); ('UPDUSER','VARCHAR2(4000)','Yes',null,23,null,'NO','NO','NO');Edit 1: Whenever I try to call a stored procedure to add a new item in this view... I'll get this error:
org.apache.ibatis.exceptions.PersistenceException: ### Error updating database. Cause: java.sql.SQLException: ORA-06502: PL/SQL: numeric or value error: character to number conversion error ORA-06512: at line 2I made a class for testing purpose that tries to call the add method:
private ItemService iService = new ItemService(Item.class); @Test public void addProcTest() { Map<String, Object> paramMap = new HashMap<String, Object>(); paramMap.put("p_item_desc", "theo3"); paramMap.put("p_min_qty", 1); paramMap.put("p_max_qty", 2); paramMap.put("p_abc", "A"); paramMap.put("p_item_weight", 2); paramMap.put("p_size_cl", 1); paramMap.put("p_weight_class", 1); paramMap.put("p_exp_period", 2); paramMap.put("p_code", "theo"); paramMap.put("p_um", "MP"); paramMap.put("p_conv_factor",2.0); paramMap.put("p_vol", 2); paramMap.put("p_info", "theo2"); paramMap.put("p_pack_parent", 105249); paramMap.put("p_group_parent", 0); paramMap.put("p_order_full", 0); paramMap.put("p_statist_period", 0); paramMap.put("p_do_it", 1); iService.addItem(paramMap); }call of the stored procedure in the ItemMapper.xml:
<update id="addItem" parameterType="java.util.Map" statementType="CALLABLE"> {call SSLS_WMS.PKG_ITEM_ADMIN.PCREATEITEM( #{p_item_desc,jdbcType=VARCHAR}, #{p_min_qty,jdbcType=INTEGER}, #{p_max_qty,jdbcType=INTEGER}, #{p_abc,jdbcType=VARCHAR}, #{p_item_weight,jdbcType=DOUBLE}, #{p_size_cl,jdbcType=INTEGER}, #{p_weight_class,jdbcType=INTEGER}, #{p_exp_period,jdbcType=DOUBLE}, #{p_code,jdbcType=VARCHAR}, #{p_um,jdbcType=VARCHAR}, #{p_conv_factor,jdbcType=DOUBLE}, #{p_vol,jdbcType=INTEGER}, #{p_info,jdbcType=VARCHAR}, #{p_pack_parent,jdbcType=INTEGER}, #{p_group_parent,jdbcType=INTEGER}, #{p_order_full,jdbcType=INTEGER}, #{p_statist_period,jdbcType=INTEGER}, #{p_do_it, jdbcType=INTEGER} ) } </update>and last is the stored procedure:
PROCEDURE pCreateItem ( p_ITEM_DESC item.ITEM_DESC%type, p_MIN_QTY item.MIN_QTY%type, p_MAX_QTY item.MAX_QTY%type, p_ABC item.ABC%type, p_ITEM_WEIGHT item.ITEM_WEIGHT%type, p_SIZE_CL item.SIZE_CL%type, p_WEIGHT_CLASS item.WEIGHT_CLASS%type, p_EXP_PERIOD item.EXP_PERIOD%type, p_CODE item.CODE%type, p_UM item_pack.UM%type, p_IMG item_pack.IMG%type, p_CONV_FACTOR item_pack.CONV_FACTOR%type, p_VOL item_pack.VOL%type, p_INFO item_pack.INFO%type, p_PackParent item_pack.pack_id%type, p_GroupParent item_pack.pack_id%type, p_order_full item.order_full%type default 0, p_statist_period item.STATIST_PERIOD%type default 3, p_do_it PLS_INTEGER DEFAULT 1 ); -
Theo over 10 yearsThank you very much, you're a life saver!