可以使用企业库从 .Net 调用 Oracle FUNCTION 吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/411820/
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
Possible to call Oracle FUNCTION from .Net using Enterprise Library?
提问by tbone
I have the following Oracle function:
我有以下 Oracle 函数：
function get_job_no return number is
V_job_no number;
begin
select appwork.tlm_corphier_job.nextval into V_job_no from dual;
return V_job_no;
end get_job_no;
PLEASE NOTE:
1) This is a FUNCTION, not a procedure
2) This is returning a NUMBER, not a VARCHAR
3) I happen to be using System.Data.OracleClient rather than Oracle.DataAccess.Client (due to another issue I was having) but a solution with either would be fine.
请注意：
1）这是一个函数，而不是一个过程
2）这是返回一个数字，而不是一个 VARCHAR
3）我碰巧使用的是 System.Data.OracleClient 而不是 Oracle.DataAccess.Client（由于另一个问题，我是有），但有一个解决方案就可以了。
I am trying to call this from VB.Net using the MS enterprise library like so:
我正在尝试使用 MS 企业库从 VB.Net 调用它，如下所示：
Imports Microsoft.Practices.EnterpriseLibrary.Data
Imports System.Data.OracleClient
Public Function GetNextJobNumber() As Object
Dim db As Database = DatabaseFactory.CreateDatabase()
Dim myresult As Object = Nothing
Using cmd As DbCommand = db.GetStoredProcCommand("CORPHIER_PKG.get_job_no")
Dim retval As New Data.OracleClient.OracleParameter("retval", GetType(System.Data.OracleClient.OracleNumber))
retval.Direction = ParameterDirection.ReturnValue
cmd.Parameters.Add(retval)
db.ExecuteNonQuery(cmd)
myresult = retval.Value
End Using
Return myresult
End Function
I get the following error:
我收到以下错误：
System.InvalidCastException: Failed to convert parameter value from a RuntimeType to a Decimal. ---> System.InvalidCastException: Object must implement IConvertible. at System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider) at System.Data.OracleClient.OracleParameter.CoerceValue(Object value, MetaType destinationType) --- End of inner exception stack trace --- at System.Data.OracleClient.OracleParameter.CoerceValue(Object value, MetaType destinationType) at System.Data.OracleClient.OracleParameter.SetCoercedValueInternal(Object value, MetaType metaType) at System.Data.OracleClient.OracleParameterBinding.PrepareForBind(OracleConnection connection, Int32& offset) at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, Boolean needRowid, OciRowidDescriptor& rowidDescriptor, ArrayList& resultParameterOrdinals) at System.Data.OracleClient.OracleCommand.ExecuteNonQueryInternal(Boolean needRowid, OciRowidDescriptor& rowidDescriptor) at System.Data.OracleClient.OracleCommand.ExecuteNonQuery() at Microsoft.Practices.EnterpriseLibrary.Data.Database.DoExecuteNonQuery(DbCommand command) at Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteNonQuery(DbCommand command)
System.InvalidCastException：无法将参数值从 RuntimeType 转换为 Decimal。---> System.InvalidCastException：对象必须实现 IConvertible。在 System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider) at System.Data.OracleClient.OracleParameter.CoerceValue(Object value, MetaType destinationType) --- 内部异常堆栈跟踪结束 --- 在 System.Data。 OracleClient.OracleParameter.CoerceValue(Object value, MetaType destinationType) at System.Data.OracleClient.OracleParameter.SetCoercedValueInternal(Object value, MetaType metaType) at System.Data.OracleClient.OracleParameterBinding.PrepareForBind(OracleConnection connection, Int32& offset) at System.Data .OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior 行为, Boolean needRowid,
回答by tbone
Ok, this seems to work:
好的，这似乎有效：
Public Function GetNextJobNumber() As Object
Dim db As Database = DatabaseFactory.CreateDatabase()
Using cmd As DbCommand = db.GetStoredProcCommand("CORPHIER_PKG.get_job_no")
db.AddParameter(cmd, "retval", DbType.Int32, 0, ParameterDirection.ReturnValue, True, 0, 0, String.Empty, DataRowVersion.Current, Convert.DBNull)
db.ExecuteNonQuery(cmd)
Return db.GetParameterValue(cmd, "retval")
End Using
End Function
