ORA-06550:第1行，第7列;PLS-00306:调用'INSERTBILL'时参数的号码或类型错
本文关键字：参数 号码 类型 INSERTBILL PLS-00306 调用 ORA-06550 1行 7列 | 更新日期: 2023-09-27 18:06:00
嗨，我在Oracle 10g服务器上调用存储过程有问题。
这是我的表:
-- Create table
create table TMOBILE_R_BILLS
(
ID VARCHAR2(50) not null,
NO VARCHAR2(30) not null,
SURNAME VARCHAR2(60) not null,
NAME VARCHAR2(60) not null,
BILL_MONTH NUMBER not null,
VPS_TIME NUMBER not null,
VPS_PRICE_WITH_DISCOUNT NUMBER not null,
VPS_PRICE_WITHOUT_DISCOUNT NUMBER not null,
TMOBILE_TIME NUMBER not null,
TMOBILE_PRICE_WITH_DISCOUNT NUMBER not null,
TMOBILE_PRICE_WITHOUT_DISCOUNT NUMBER not null,
ORANGE_TIME NUMBER not null,
ORANGE_PRICE_WITH_DISCOUNT NUMBER not null,
ORANGE_PRICE_WITHOUT_DISCOUNT NUMBER not null,
O2_TIME NUMBER not null,
O2_PRICE_WITH_DISCOUNT NUMBER not null,
O2_PRICE_WITHOUT_DISCOUNT NUMBER not null,
INTER_TIME NUMBER not null,
INTER_PRICE_WITH_DISCOUNT NUMBER not null,
INTER_PRICE_WITHOUT_DISCOUNT NUMBER not null,
ROAMING_TIME NUMBER not null,
ROAMING_PRICE_WITH_DISCOUNT NUMBER not null,
ROAMING_PRICE_WITHOUT_DISCOUNT NUMBER not null,
GPRS_COUNT NUMBER not null,
GPRS_PRICE_WITH_DISCOUNT NUMBER not null,
GPRS_PRICE_WITHOUT_DISCOUNT NUMBER not null,
LM_TIME DATE not null,
TOTAL_TIME NUMBER not null,
TOTAL_PRICE_WITH_DISCOUNT NUMBER not null,
TOTAL_PRICE_WITHOUT_DISCOUNT NUMBER not null
)
这里是存储过程:
CREATE OR REPLACE PROCEDURE INSERTBILL(
Id in varchar2, No in varchar2, Surname in varchar2, Name in varchar2,BillMonth in number,
VpsTime in number, VpsPriceWithDiscount in number,VpsPriceWithoutDiscount in number,
TmobileTime in number,TMobilePriceWithDiscount in number, TmobilePriceWithoutDiscount in number,
OrangeTime in number, OrangePriceWithDiscount in number, OrangePriceWithoutDiscount in number,
O2Time in number, O2PriceWithDiscount in number, O2PriceWithoutDiscount in number, InterTime in number,
InterPriceWithDiscount in number, InterPriceWithoutDiscount in number, RoamingTime in number,
RoamingPriceWithDiscount in number, RoamingPriceWithoutDiscount in number,
GprsTime in number,GprsPriceWithDiscount in number, GrpsPriceWithoutDiscount in number, LmTime in date,
TotalTime in number, TotalPriceWithDiscount in number, TotalPriceWithoutDiscount in number)
AS
BEGIN
INSERT INTO TMOBILE_R_BILLS (ID,NO,SURNAME,NAME,BILL_MONTH,
VPS_TIME,VPS_PRICE_WITH_DISCOUNT,VPS_PRICE_WITHOUT_DISCOUNT,
TMOBILE_TIME, TMOBILE_PRICE_WITH_DISCOUNT,TMOBILE_PRICE_WITHOUT_DISCOUNT,
ORANGE_TIME, ORANGE_PRICE_WITH_DISCOUNT,ORANGE_PRICE_WITHOUT_DISCOUNT,
O2_TIME, O2_PRICE_WITH_DISCOUNT, O2_PRICE_WITHOUT_DISCOUNT,
INTER_TIME, INTER_PRICE_WITH_DISCOUNT, INTER_PRICE_WITHOUT_DISCOUNT,
ROAMING_TIME, ROAMING_PRICE_WITH_DISCOUNT, ROAMING_PRICE_WITHOUT_DISCOUNT,
GPRS_COUNT, GPRS_PRICE_WITH_DISCOUNT,GPRS_PRICE_WITHOUT_DISCOUNT,
LM_TIME,
TOTAL_TIME, TOTAL_PRICE_WITH_DISCOUNT, TOTAL_PRICE_WITHOUT_DISCOUNT)
VALUES (Id,No,Surname, Name,BillMonth,
VpsTime,VpsPriceWithDiscount,VpsPriceWithoutDiscount,
TmobileTime,TMobilePriceWithDiscount,TmobilePriceWithoutDiscount,
OrangeTime,OrangePriceWithDiscount,OrangePriceWithoutDiscount,
O2Time, O2PriceWithDiscount, O2PriceWithoutDiscount,
InterTime,InterPriceWithDiscount,InterPriceWithoutDiscount,
RoamingTime,RoamingPriceWithDiscount,RoamingPriceWithoutDiscount,
GprsTime,GprsPriceWithDiscount,GrpsPriceWithoutDiscount,
LmTime,TotalTime,TotalPriceWithDiscount,TotalPriceWithoutDiscount);
END;
这是我的c#代码，我使用SQL批量插入:
public void InsertBills(List<CellPhoneBill> bills, int month)
{
var billsAsArrays = new BillDataAsArray(bills,month);
using (var conn = new OracleConnection(GenerateConnectionString()))
{
var cmd = new OracleCommand
{
Connection = conn,
CommandText = "INSERTBILL",
CommandType = CommandType.StoredProcedure,
ArrayBindCount = billsAsArrays.Ids.Count(),
};
cmd.Parameters.Add("Id", OracleDbType.Varchar2, billsAsArrays.Ids, ParameterDirection.Input);
cmd.Parameters.Add("No", OracleDbType.Varchar2, billsAsArrays.Numbers, ParameterDirection.Input);
cmd.Parameters.Add("Surname", OracleDbType.Varchar2, billsAsArrays.Surnames, ParameterDirection.Input);
cmd.Parameters.Add("Name", OracleDbType.Varchar2, billsAsArrays.Names, ParameterDirection.Input);
cmd.Parameters.Add("BillMonth", OracleDbType.Decimal, billsAsArrays.BillMonth, ParameterDirection.Input);
cmd.Parameters.Add("LmTime", OracleDbType.Date, billsAsArrays.LmTimes, ParameterDirection.Input);
cmd.Parameters.Add("VpsTime", OracleDbType.Decimal, billsAsArrays.VpsTimes, ParameterDirection.Input);
cmd.Parameters.Add("VpsPriceWithDiscount", OracleDbType.Decimal, billsAsArrays.VpsPriceWithDiscounts, ParameterDirection.Input);
cmd.Parameters.Add("VpsPriceWithoutDiscount", OracleDbType.Decimal, billsAsArrays.VpsPriceWithoutDiscounts, ParameterDirection.Input);
cmd.Parameters.Add("TmobileTime", OracleDbType.Decimal, billsAsArrays.TmobileTimes, ParameterDirection.Input);
cmd.Parameters.Add("TmobilePriceWithDiscount", OracleDbType.Decimal, billsAsArrays.TmobilePriceWithDiscounts, ParameterDirection.Input);
cmd.Parameters.Add("TmobilePriceWithoutDiscount", OracleDbType.Decimal, billsAsArrays.TmobilePriceWithoutDiscounts, ParameterDirection.Input);
cmd.Parameters.Add("OrangeTime", OracleDbType.Decimal, billsAsArrays.OrangeTimes, ParameterDirection.Input);
cmd.Parameters.Add("OrangePriceWithDiscount", OracleDbType.Decimal, billsAsArrays.OrangePriceWithDiscounts, ParameterDirection.Input);
cmd.Parameters.Add("OrangePriceWithoutDiscount", OracleDbType.Decimal, billsAsArrays.OrangePriceWithoutDiscounts, ParameterDirection.Input);
cmd.Parameters.Add("O2Time", OracleDbType.Decimal, billsAsArrays.O2Times, ParameterDirection.Input);
cmd.Parameters.Add("O2PriceWithDiscount", OracleDbType.Decimal, billsAsArrays.O2PriceWithDiscounts, ParameterDirection.Input);
cmd.Parameters.Add("O2PriceWithoutDiscount", OracleDbType.Decimal, billsAsArrays.O2PriceWithoutDiscounts, ParameterDirection.Input);
cmd.Parameters.Add("InterTime", OracleDbType.Decimal, billsAsArrays.InterTimes, ParameterDirection.Input);
cmd.Parameters.Add("InterPriceWithDiscount", OracleDbType.Decimal, billsAsArrays.InterPriceWithDiscounts, ParameterDirection.Input);
cmd.Parameters.Add("InterPriceWithoutDiscount", OracleDbType.Decimal, billsAsArrays.InterPriceWithoutDiscounts, ParameterDirection.Input);
cmd.Parameters.Add("RoamingTime", OracleDbType.Decimal, billsAsArrays.RoamingTimes, ParameterDirection.Input);
cmd.Parameters.Add("RoamingPriceWithDiscount", OracleDbType.Decimal, billsAsArrays.RoamingPriceWithDiscounts, ParameterDirection.Input);
cmd.Parameters.Add("RoamingPriceWithoutDiscount", OracleDbType.Decimal, billsAsArrays.RoamingPriceWithoutDiscounts, ParameterDirection.Input);
cmd.Parameters.Add("GprsTime", OracleDbType.Decimal, billsAsArrays.GprsTimes, ParameterDirection.Input);
cmd.Parameters.Add("GprsPriceWithDiscount", OracleDbType.Decimal, billsAsArrays.GprsPriceWithDiscounts, ParameterDirection.Input);
cmd.Parameters.Add("GprsPriceWithoutDiscount", OracleDbType.Decimal, billsAsArrays.GprsPriceWithoutDiscounts, ParameterDirection.Input);
cmd.Parameters.Add("TotalTime", OracleDbType.Decimal, billsAsArrays.TotalTimes, ParameterDirection.Input);
cmd.Parameters.Add("TotalPriceWithDiscount", OracleDbType.Decimal, billsAsArrays.TotalPriceWithDiscounts, ParameterDirection.Input);
cmd.Parameters.Add("TotalPriceWithoutDiscount", OracleDbType.Decimal, billsAsArrays.TotalPriceWithoutDiscounts, ParameterDirection.Input);
try
{
conn.Open();
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
}
问题是，如果我在PL/SQL开发人员中测试过程，一切都很好。但是如果我尝试从c#代码调用这个过程，我得到这个错误:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'INSERTBILL'
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'INSERTBILL'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
抱歉我发布了旧的和坏的代码，"LmTime:参数是在c#代码中。但误差是一样的。我认为参数类型有问题。在oracle中所有是数字，在c#代码中如果value是数字所有是十进制。我如何识别代码的问题部分?
谢谢你的建议
你发布的c#代码中参数的顺序是否正确?在参数定义中，它是第六个参数。在程序参数表中，倒数第4位。这将解释它，如果你确实张贴的代码是你得到的错误。
如果没有，那么我将如何做呢?修改我的过程，为参数列表的后半部分提供默认值，并将它们从c#代码中删除。如果仍然失败，则为其余参数的后半部分提供默认值，将它们从代码中删除，然后再次运行。重复，直到问题被发现。
在c#调用中设置了29个参数，存储过程有30个参数。因此wrong number or types of arguments in call to 'INSERTBILL' .
编辑:您的c#代码中缺少LmTime参数。