存储过程如下(在PL/SQL测试中正常,WEB页面上调用出错!):
CREATE OR REPLACE PROCEDURE SP_XT_LOGIN
(
PI_USERID0 IN XT_USERLIST.YHID00%TYPE, --VARCHAR2(10)
PI_USERPWD IN XT_USERLIST.YHMM00%TYPE, --VARCHAR2(32)
PI_XTDH000 IN XT_VERSION.XTDH00%TYPE, --CHAR(2)
PO_USERNM0 OUT XT_USERLIST.YHMC00%TYPE, --VARCHAR2(32)
PO_PWDSTAT OUT XT_USERLIST.MMZT00%TYPE, --CHAR(1)
PO_SYSNAME OUT XT_VERSION.XTMC00%TYPE, --VARCHAR2(32)
PO_CURRVER OUT XT_VERSION.ZXBBH0%TYPE, --NUMBER(4,2)
PO_SYSDATE OUT XT_USERLOG.DLRQ00%TYPE, --CHAR(8)
PO_SYSTIME OUT XT_USERLOG.DLSJ00%TYPE --CHAR(6)
)页面调用如下:
String sqlCommand = "SP_XT_LOGIN";
OracleCommand dbCommand = (OracleCommand)db.GetStoredProcCommand(sqlCommand);
OracleParameter[] orap =
{
new OracleParameter("PI_USERID0", OracleDbType.Varchar2,10, name, ParameterDirection.Input),
new OracleParameter("PI_USERPWD", OracleDbType.Varchar2,32, pwd, ParameterDirection.Input),
new OracleParameter("PI_XTDH000", OracleDbType.Char,2,symbol, ParameterDirection.Input),
new OracleParameter("PO_USERNM0", OracleDbType.Varchar2,32, ParameterDirection.Output),
new OracleParameter("PO_PWDSTAT", OracleDbType.Char,1, ParameterDirection.Output),
new OracleParameter("PO_SYSNAME", OracleDbType.Varchar2,32, ParameterDirection.Output),
new OracleParameter("PO_CURRVER", OracleDbType.Decimal, ParameterDirection.Output),
new OracleParameter("PO_SYSDATE", OracleDbType.Char,8, ParameterDirection.Output),
new OracleParameter("PO_SYSTIME", OracleDbType.Char,6, ParameterDirection.Output)
};
foreach (OracleParameter parameter in orap)
{
dbCommand.Parameters.Add(parameter);
}
try
{
db.ExecuteNonQuery(dbCommand);
objUSERNM0 = db.GetParameterValue(dbCommand, "PO_USERNM0");
objPWDSTAT = db.GetParameterValue(dbCommand, "PO_PWDSTAT");
objSYSNAME = db.GetParameterValue(dbCommand, "PO_SYSNAME");
objCURRVER = db.GetParameterValue(dbCommand, "PO_CURRVER");
objSYSDATE = db.GetParameterValue(dbCommand, "PO_SYSDATE");
objSYSTIME = db.GetParameterValue(dbCommand, "PO_SYSTIME"); if (Object.Equals(objUSERNM0, null))
return 0;
else
return 1;
}
catch (OracleException er)
{
throw er;
}
finally
{
dbCommand.Connection.Close();
}程序运行db.ExecuteNonQuery(dbCommand)语句时抛出异常:
用户代码未处理 Oracle.DataAccess.Client.OracleException
Message="ORA-20025: ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小\nORA-06512: 在\"RSGL.SP_XT_LOGIN\", line 125\nORA-06512: 在line 1"
Source="Oracle Data Provider for .NET"
ErrorCode=-2147467259
DataSource="RSGL"
Number=20025
Procedure="SP_XT_LOGIN"
CREATE OR REPLACE PROCEDURE SP_XT_LOGIN
(
PI_USERID0 IN XT_USERLIST.YHID00%TYPE, --VARCHAR2(10)
PI_USERPWD IN XT_USERLIST.YHMM00%TYPE, --VARCHAR2(32)
PI_XTDH000 IN XT_VERSION.XTDH00%TYPE, --CHAR(2)
PO_USERNM0 OUT XT_USERLIST.YHMC00%TYPE, --VARCHAR2(32)
PO_PWDSTAT OUT XT_USERLIST.MMZT00%TYPE, --CHAR(1)
PO_SYSNAME OUT XT_VERSION.XTMC00%TYPE, --VARCHAR2(32)
PO_CURRVER OUT XT_VERSION.ZXBBH0%TYPE, --NUMBER(4,2)
PO_SYSDATE OUT XT_USERLOG.DLRQ00%TYPE, --CHAR(8)
PO_SYSTIME OUT XT_USERLOG.DLSJ00%TYPE --CHAR(6)
)页面调用如下:
String sqlCommand = "SP_XT_LOGIN";
OracleCommand dbCommand = (OracleCommand)db.GetStoredProcCommand(sqlCommand);
OracleParameter[] orap =
{
new OracleParameter("PI_USERID0", OracleDbType.Varchar2,10, name, ParameterDirection.Input),
new OracleParameter("PI_USERPWD", OracleDbType.Varchar2,32, pwd, ParameterDirection.Input),
new OracleParameter("PI_XTDH000", OracleDbType.Char,2,symbol, ParameterDirection.Input),
new OracleParameter("PO_USERNM0", OracleDbType.Varchar2,32, ParameterDirection.Output),
new OracleParameter("PO_PWDSTAT", OracleDbType.Char,1, ParameterDirection.Output),
new OracleParameter("PO_SYSNAME", OracleDbType.Varchar2,32, ParameterDirection.Output),
new OracleParameter("PO_CURRVER", OracleDbType.Decimal, ParameterDirection.Output),
new OracleParameter("PO_SYSDATE", OracleDbType.Char,8, ParameterDirection.Output),
new OracleParameter("PO_SYSTIME", OracleDbType.Char,6, ParameterDirection.Output)
};
foreach (OracleParameter parameter in orap)
{
dbCommand.Parameters.Add(parameter);
}
try
{
db.ExecuteNonQuery(dbCommand);
objUSERNM0 = db.GetParameterValue(dbCommand, "PO_USERNM0");
objPWDSTAT = db.GetParameterValue(dbCommand, "PO_PWDSTAT");
objSYSNAME = db.GetParameterValue(dbCommand, "PO_SYSNAME");
objCURRVER = db.GetParameterValue(dbCommand, "PO_CURRVER");
objSYSDATE = db.GetParameterValue(dbCommand, "PO_SYSDATE");
objSYSTIME = db.GetParameterValue(dbCommand, "PO_SYSTIME"); if (Object.Equals(objUSERNM0, null))
return 0;
else
return 1;
}
catch (OracleException er)
{
throw er;
}
finally
{
dbCommand.Connection.Close();
}程序运行db.ExecuteNonQuery(dbCommand)语句时抛出异常:
用户代码未处理 Oracle.DataAccess.Client.OracleException
Message="ORA-20025: ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小\nORA-06512: 在\"RSGL.SP_XT_LOGIN\", line 125\nORA-06512: 在line 1"
Source="Oracle Data Provider for .NET"
ErrorCode=-2147467259
DataSource="RSGL"
Number=20025
Procedure="SP_XT_LOGIN"
但是:
1、我用PL/SQL Developer测试存储过程是正确的,无任何异常.(入参值跟WEB页面上同样的值)
2、WEB中调用的存储过程参数我都已经限制了长度.(除了Decimal,因为我实在不知道NUMBER(4,2) 应该如何设置Decimal的精度)
原来是Oracle Data Provider for .NET 有点小问题.直接传限制size不能像预想那样构造起来!