ORA-01458 invalid length inside variable character string Cause: An attempt was made to bind or define a variable character string with a buffer length less than the minimum requirement. Action: Increase the buffer size or use a different type.
这是我写的存储过程:CREATE OR REPLACE PROCEDURE PROC_AIRLINE_SEATSRATE (cAreaCode char:='00',vcProvince varchar2:='00',vcCity varchar2:='00',vcArea varchar2:='00',cOutCity char:='000',cInCity char:='000',vcStartTime VARCHAR2:='0000-00-00',vcEndTime VARCHAR2:='0000-00-00',nHUProductionRate out number,iTotalSeated out number,iTotalSale out number ) --参数说明:全国区域代码、省、城市、城市区域 、出港城市代码、进港城市代码、开始时间,结束时间,(输出参数)该航线上的产品投放率,(输出参数)在选择的区域中销售该航线的总销售的座位数,(输出参数)在选择的区域中销售该航线的总销售金额 AS EXECSQL VARCHAR2(2000):=''; EXECSQL_PRODUCTION VARCHAR2(2000):=''; iHUProduction number:=0; --该航线上HU的产品投放 iTotalProduction number:=0; --该航线上总的产品投放 sTime DATE; eTime DATE;
begin --检查输入日期判断是否合法 SELECT TO_DATE(vcStartTime,'YYYY-MM-DD'),TO_DATE(vcEndTime,'YYYY-MM-DD') INTO sTime,eTime FROM DUAL;
--计算 出港城市-----入港城市 这条航线上 从开始时间到结束时间 这个时段上的产品投放率 EXECSQL_PRODUCTION:='select sum(tbPlaneSeats.NPLANESEATS),sum(decode(tbFlightSchedule.CAIRLINECODE,''HU'',tbPlaneSeats.NPLANESEATS)) from tbPlaneSeats,tbFlightSchedule where 1<>0'; EXECSQL_PRODUCTION:=EXECSQL_PRODUCTION||' AND tbFlightSchedule.VCFLIGHTDATE>='''||vcStartTime||''' AND tbFlightSchedule.VCFLIGHTDATE<='''||vcEndTime||''''; EXECSQL_PRODUCTION:=EXECSQL_PRODUCTION||' AND tbFlightSchedule.CUPLOCATION='''||cOutCity||''' AND tbFlightSchedule.CDISLOCATION='''||cInCity||''''; EXECSQL_PRODUCTION:=EXECSQL_PRODUCTION||' AND tbFlightSchedule.VCPLANETYPE=tbPlaneSeats.VCPLANETYPE'; EXECUTE IMMEDIATE EXECSQL_PRODUCTION INTO iTotalProduction,iHUProduction;
--得到该区域的销售总票数及销售总额 EXECSQL:='select sum(tbAgentSellRecord.ITICKETNUM),sum(tbAgentSellRecord.ITICKETFARE) from tbAgentSellRecord,tbCityAreaPart,tbAgentBasicInfo WHERE 1<>0'; EXECSQL:=EXECSQL||' AND tbAgentSellRecord.VCFLTDATE>='''||vcStartTime||''' AND tbAgentSellRecord.VCFLTDATE<='''||vcEndTime||''''; if cAreaCode<>'00' then EXECSQL:=EXECSQL||' AND tbCityAreaPart.CAREACODE='''||cAreaCode||''' '; end if; if vcProvince<>'00' then EXECSQL:=EXECSQL||' AND tbCityAreaPart.VCPROVINCE='''||vcProvince||''' '; end if; if vcCity<>'00' then EXECSQL:=EXECSQL||' AND TBCITYAREAPART.VCCITY='''||vcCity||''' '; end if; EXECSQL:=EXECSQL||' AND tbCityAreaPart.ICITYAREAID=tbAgentBasicInfo.ICITYAREAID'; EXECSQL:=EXECSQL||' AND tbAgentSellRecord.CAGENTCODE=tbAgentBasicInfo.CAGENTCODE';
EXECUTE IMMEDIATE EXECSQL INTO iTotalSeated,iTotalSale;
EXCEPTION --异常处理 WHEN OTHERS THEN --运行时发生意外错误 SELECT -1,-1,-1 into nHUProductionRate,iTotalSeated,iTotalSale FROM DUAL;
END PROC_AIRLINE_SEATSRATE ; /设置参数执行完以后的SQL是:select sum(tbPlaneSeats.NPLANESEATS),sum(decode(tbFlightSchedule.CAIRLINECODE,'HU',tbPlaneSeats.NPLANESEATS)) from tbPlaneSeats,tbFlightSchedule where 1<>0 AND tbFlightSchedule.VCFLIGHTDATE>='2002-11-01' AND tbFlightSchedule.VCFLIGHTDATE<='2002-11-01' AND tbFlightSchedule.CUPLOCATION='PEK' AND tbFlightSchedule.CDISLOCATION='WUH ' AND tbFlightSchedule.VCPLANETYPE=tbPlaneSeats.VCPLANETYPE select sum(tbAgentSellRecord.ITICKETNUM),sum(tbAgentSellRecord.ITICKETFARE) from tbAgentSellRecord,tbCityAreaPart,tbAgentBasicInfo WHERE 1<>0 AND tbAgentSellRecord.VCFLTDATE>='2002-11-01' AND tbAgentSellRecord.VCFLTDATE<='2002-11-01' AND tbCityAreaPart.CAREACODE='ZN ' AND tbCityAreaPart.VCPROVINCE='广东' AND tbCityAreaPart.ICITYAREAID=tbAgentBasicInfo.ICITYAREAID AND tbAgentSellRecord.CAGENTCODE=tbAgentBasicInfo.CAGENTCODEnHUProductionRate=0.5 iTotalSeated=25014 iTotalSale=21517614
Cause: An attempt was made to bind or define a variable character string with a buffer length less than the minimum requirement.
Action: Increase the buffer size or use a different type.
OracleCommand myCommand=new OracleCommand();
OracleConnection myConnection=new OracleConnection(strCon);
myCommand.Connection=myConnection;
myCommand.Connection .Open();
myCommand.CommandText = "PROC_AIRLINE_SEATSRATE" ;//存储过程名 根据查询的条件,检索出符合条件的记录列表
myCommand.CommandType = CommandType.StoredProcedure ;//命令类型OracleParameter cAreaCode=new OracleParameter("cAreaCode",OracleType.Char ,2,ParameterDirection.Input ,true,0,0,"",DataRowVersion.Default ,Convert.DBNull );
cAreaCode.Value ="ZN";//p_cAreaCode;
myCommand.Parameters.Add(cAreaCode);OracleParameter vcProvince=new OracleParameter("vcProvince",OracleType.VarChar ,10,ParameterDirection.Input ,true,0,0,"",DataRowVersion.Default ,Convert.DBNull );
vcProvince.Value ="广东";//p_vcProvince;
myCommand.Parameters.Add(vcProvince);OracleParameter vcCity=new OracleParameter("vcCity",OracleType.VarChar ,10,ParameterDirection.Input ,true,0,0,"",DataRowVersion.Default ,Convert.DBNull );
vcCity.Value ="00";//p_vcCity;
myCommand.Parameters.Add(vcCity);OracleParameter vcArea=new OracleParameter("vcArea",OracleType.VarChar ,10,ParameterDirection.Input ,true,0,0,"",DataRowVersion.Default ,Convert.DBNull );
vcArea.Value ="00";//p_vcArea;
myCommand.Parameters.Add(vcArea);OracleParameter cOutCity=new OracleParameter("cOutCity",OracleType.Char ,3,ParameterDirection.Input ,true,0,0,"",DataRowVersion.Default ,Convert.DBNull );
cOutCity.Value ="PEK";//p_cOutCity;
myCommand.Parameters.Add(cOutCity);OracleParameter cInCity=new OracleParameter("cInCity",OracleType.Char ,3,ParameterDirection.Input ,true,0,0,"",DataRowVersion.Default ,Convert.DBNull );
cInCity.Value ="WUH";//p_cInCity;
myCommand.Parameters.Add(cInCity);OracleParameter vcStartTime=new OracleParameter("vcStartTime",OracleType.VarChar ,10,ParameterDirection.Input ,true,0,0,"",DataRowVersion.Default ,Convert.DBNull );
vcStartTime.Value ="2002-11-01";//p_vcStartTime;
myCommand.Parameters.Add(vcStartTime);OracleParameter vcEndTime=new OracleParameter("vcEndTime",OracleType.VarChar ,10,ParameterDirection.Input ,true,0,0,"",DataRowVersion.Default ,Convert.DBNull );
vcEndTime.Value ="2002-11-01";//p_vcEndTime;
myCommand.Parameters.Add(vcEndTime);OracleParameter nHUProductionRate=new OracleParameter("nHUProductionRate",OracleType.Number);
nHUProductionRate.Direction =ParameterDirection.Output ;
myCommand.Parameters.Add(nHUProductionRate);OracleParameter iTotalSeated=new OracleParameter("iTotalSeated",OracleType.Number);
iTotalSeated.Direction =ParameterDirection.Output ;
myCommand.Parameters.Add(iTotalSeated);
OracleParameter iTotalSale=new OracleParameter("iTotalSale",OracleType.Number );
iTotalSale.Direction =ParameterDirection.Output;
myCommand.Parameters.Add(iTotalSale);myCommand.ExecuteNonQuery() ;
myCommand.Dispose ();
myConnection.Close ();
string temp=nHUProductionRate.Value.ToString () ;
string temp1=iTotalSeated.Value.ToString () ;
string temp2=iTotalSale.Value.ToString () ;
执行到myCommand.ExecuteNonQuery() ;就报错了
是我传递参数的方法不对么?我不知道out参数number如何设置参数的大小?
(cAreaCode char:='00',vcProvince varchar2:='00',vcCity varchar2:='00',vcArea varchar2:='00',cOutCity char:='000',cInCity char:='000',vcStartTime VARCHAR2:='0000-00-00',vcEndTime VARCHAR2:='0000-00-00',nHUProductionRate out number,iTotalSeated out number,iTotalSale out number )
--参数说明:全国区域代码、省、城市、城市区域 、出港城市代码、进港城市代码、开始时间,结束时间,(输出参数)该航线上的产品投放率,(输出参数)在选择的区域中销售该航线的总销售的座位数,(输出参数)在选择的区域中销售该航线的总销售金额
AS
EXECSQL VARCHAR2(2000):='';
EXECSQL_PRODUCTION VARCHAR2(2000):='';
iHUProduction number:=0; --该航线上HU的产品投放
iTotalProduction number:=0; --该航线上总的产品投放
sTime DATE;
eTime DATE;
begin
--检查输入日期判断是否合法
SELECT TO_DATE(vcStartTime,'YYYY-MM-DD'),TO_DATE(vcEndTime,'YYYY-MM-DD') INTO sTime,eTime FROM DUAL;
--计算 出港城市-----入港城市 这条航线上 从开始时间到结束时间 这个时段上的产品投放率
EXECSQL_PRODUCTION:='select sum(tbPlaneSeats.NPLANESEATS),sum(decode(tbFlightSchedule.CAIRLINECODE,''HU'',tbPlaneSeats.NPLANESEATS)) from tbPlaneSeats,tbFlightSchedule where 1<>0';
EXECSQL_PRODUCTION:=EXECSQL_PRODUCTION||' AND tbFlightSchedule.VCFLIGHTDATE>='''||vcStartTime||''' AND tbFlightSchedule.VCFLIGHTDATE<='''||vcEndTime||'''';
EXECSQL_PRODUCTION:=EXECSQL_PRODUCTION||' AND tbFlightSchedule.CUPLOCATION='''||cOutCity||''' AND tbFlightSchedule.CDISLOCATION='''||cInCity||'''';
EXECSQL_PRODUCTION:=EXECSQL_PRODUCTION||' AND tbFlightSchedule.VCPLANETYPE=tbPlaneSeats.VCPLANETYPE';
EXECUTE IMMEDIATE EXECSQL_PRODUCTION INTO iTotalProduction,iHUProduction;
nHUProductionRate:=ROUND(iHUProduction/iTotalProduction,6);
--得到该区域的销售总票数及销售总额
EXECSQL:='select sum(tbAgentSellRecord.ITICKETNUM),sum(tbAgentSellRecord.ITICKETFARE) from tbAgentSellRecord,tbCityAreaPart,tbAgentBasicInfo WHERE 1<>0';
EXECSQL:=EXECSQL||' AND tbAgentSellRecord.VCFLTDATE>='''||vcStartTime||''' AND tbAgentSellRecord.VCFLTDATE<='''||vcEndTime||'''';
if cAreaCode<>'00' then
EXECSQL:=EXECSQL||' AND tbCityAreaPart.CAREACODE='''||cAreaCode||''' ';
end if;
if vcProvince<>'00' then
EXECSQL:=EXECSQL||' AND tbCityAreaPart.VCPROVINCE='''||vcProvince||''' ';
end if;
if vcCity<>'00' then
EXECSQL:=EXECSQL||' AND TBCITYAREAPART.VCCITY='''||vcCity||''' ';
end if;
EXECSQL:=EXECSQL||' AND tbCityAreaPart.ICITYAREAID=tbAgentBasicInfo.ICITYAREAID';
EXECSQL:=EXECSQL||' AND tbAgentSellRecord.CAGENTCODE=tbAgentBasicInfo.CAGENTCODE';
EXECUTE IMMEDIATE EXECSQL INTO iTotalSeated,iTotalSale;
EXCEPTION --异常处理
WHEN OTHERS THEN
--运行时发生意外错误
SELECT -1,-1,-1 into nHUProductionRate,iTotalSeated,iTotalSale FROM DUAL;
END PROC_AIRLINE_SEATSRATE ;
/设置参数执行完以后的SQL是:select sum(tbPlaneSeats.NPLANESEATS),sum(decode(tbFlightSchedule.CAIRLINECODE,'HU',tbPlaneSeats.NPLANESEATS)) from tbPlaneSeats,tbFlightSchedule where 1<>0 AND tbFlightSchedule.VCFLIGHTDATE>='2002-11-01' AND tbFlightSchedule.VCFLIGHTDATE<='2002-11-01' AND tbFlightSchedule.CUPLOCATION='PEK' AND tbFlightSchedule.CDISLOCATION='WUH ' AND tbFlightSchedule.VCPLANETYPE=tbPlaneSeats.VCPLANETYPE
select sum(tbAgentSellRecord.ITICKETNUM),sum(tbAgentSellRecord.ITICKETFARE) from tbAgentSellRecord,tbCityAreaPart,tbAgentBasicInfo WHERE 1<>0 AND tbAgentSellRecord.VCFLTDATE>='2002-11-01' AND tbAgentSellRecord.VCFLTDATE<='2002-11-01' AND tbCityAreaPart.CAREACODE='ZN ' AND tbCityAreaPart.VCPROVINCE='广东' AND tbCityAreaPart.ICITYAREAID=tbAgentBasicInfo.ICITYAREAID AND tbAgentSellRecord.CAGENTCODE=tbAgentBasicInfo.CAGENTCODEnHUProductionRate=0.5
iTotalSeated=25014
iTotalSale=21517614
我把out参数的类型在过程中都从number换成varchar类型,输出varchar类型就可以了执行了number类型的out参数如何设置大小呢?