oracle ORA-06502：PL/SQL：数字或值错误：字符到数字的转换错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20838959/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
提问by Theo
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) ?
有没有办法在mybatis resultMap 中指定一个字符串最多应该有20 个字符？或者如何设置精度，比如 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?
我可以将 int 的位数限制为仅 1，在“.”后显示 N 个元素吗？在浮点数上或将字符串大小限制为某个数字？
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:
编辑 1：每当我尝试调用存储过程在此视图中添加新项目时...我会收到此错误：
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 2
I made a class for testing purpose that tries to call the add method:
我创建了一个用于测试目的的类，试图调用 add 方法：
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:
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
);
回答by Jorge Campos
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 errorerror since the following paramters doesn't have the same type on the order you are passing it.
你的问题是你的过程有更多的参数然后你在 MyBatis 调用中传递给它，所以在缺少参数之后的任何时候你都应该有你的ORA-06502: PL/SQL: numeric or value error错误，因为以下参数在你传递它的顺序上没有相同的类型。
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:
在 MyBatis 中调用时，您正在执行以下操作：
(...)
#{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.
所以在那之后p_IMG item_pack.IMG%type,你没有传递任何未命中类型会导致你的错误。
