create or replace procedure p_warein_barcode(
v_accid in number, --账户id
v_noteno in nvarchar2, --单据号
v_barcode in nvarchar2, --条码
v_amount in number, --个数*/
v_mess out nvarchar2 --返回信息
) is
v_wareid number;
v_colorid number;
v_sizeid number;
v_id number;
v_count number;
v_wareno nvarchar2(20);
v_mess1 nvarchar2(1000);
begin
select count(*) into v_count from warebarcode where barcode=v_barcode and accid=v_accid;
if v_count=0 then
v_mess1:='0 未找到条码'+v_barcode;
v_mess:=v_mess1;
return;
end if;
select a.wareid,a.colorid,a.sizeid,b.wareno
into v_wareid,v_colorid,v_sizeid,v_wareno
from warebarcode a
where a.barcode=v_barcode and a.accid=v_accid;
select count(*) into v_count from wareinm where accid=2 and noteno=v_noteno
and wareid=v_wareid and colorid=v_colorid and sizeid=v_sizeid and rownum=1;
if v_count=0 then
insert into wareinm (accid,noteno,wareid,colorid,sizeid,amount,remark0)
values (v_accid,v_noteno,v_wareid,v_colorid,v_sizeid,v_amount,'');
v_mess1:='1 '||v_wareno||','||v_barcode;
v_mess:=v_mess1;
return;
end if;
select id into v_id from wareinm where accid=2 and noteno=v_noteno and wareid=v_wareid and colorid=v_colorid and sizeid=v_sizeid
order by id fetch first 1 row only;
update wareinm set amount=amount+v_amount where id=v_id and noteno=v_noteno and accid=v_accid;
v_mess1:='1 '||v_wareno||','||v_barcode;
v_mess:=v_mess1;
end;
在pl/sql中调用是成功的
declare
v_mess nvarchar2(1000);
begin
p_warein_barcode(2,'CG2015052600008','900061002',1,v_mess);
dbms_output.put_line(v_mess);
end;
但在c#中调提示:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512
调用部份代码如下:
String or = ConfigurationManager.ConnectionStrings["conn"].ToString();
OracleConnection oc = new OracleConnection(or);
oc.Open();
OracleCommand om = oc.CreateCommand();
om.CommandType = CommandType.StoredProcedure;
om.CommandText = "p_warein_barcode";
om.Parameters.Add("v_accid", OracleDbType.Long).Direction = ParameterDirection.Input;
om.Parameters["v_accid"].Value = long.Parse(accid);
om.Parameters.Add("v_noteno", OracleDbType.NVarchar2).Direction = ParameterDirection.Input;
om.Parameters["v_noteno"].Value = noteno;
om.Parameters.Add("v_barcode", OracleDbType.NVarchar2).Direction = ParameterDirection.Input;
om.Parameters["v_barcode"].Value = barcode;
om.Parameters.Add("v_amount", OracleDbType.Decimal).Direction = ParameterDirection.Input;
om.Parameters["v_amount"].Value = Decimal.Parse(amount);
om.Parameters.Add("v_mess", OracleDbType.Varchar2).Direction = ParameterDirection.Output;
om.ExecuteNonQuery();
如果把存储过程的返回参数v_mess去掉,则调用正常,应该是v_mess这个参数的问题,请教该如果解决呢?
------解决思路----------------------
v_wareno nvarchar2(20);
v_mess1 nvarchar2(1000);
估计是这两个里有一个参数太短了,你放大试试。
------解决思路----------------------
把这个参数再放长点试试啊。看着像是返回值的长度超了啊