Oracle 中的 UPDATE 语句
我们正在构建一个客户端程序，其中在具有 Oracle 后端的 Web 服务器中存储的参数在 .Net 客户端程序中设置，并通过 Web 服务作为数据集上传。
在 Web 服务代码中，从数据集中读取数据并将其添加到 Web 服务器（Oracle 后端）上的 UPDATE 语句中。
因为服务器将在防火墙后面的客户 LAN 上运行，并且由于所涉及参数的动态特性，所以不使用存储过程 - SQL 字符串是在逻辑中内置的。
下面是一个示例字符串：
UPDATE WorkOrders
SET TravelTimeHours = :TravelTimeHours,
TravelTimeMinutes = :TravelTimeMinutes,
WorkTimeHours = :WorkTimeHours,
WorkTimeMinutes = :WorkTimeMinutes,
CompletedPersonID = :CompletedPersonID,
CompletedPersonName = :CompletedPersonName,
CompleteDate = :CompleteDate
WHERE WorkOrderNumber = :WorkOrderNumber
在 VS 2010 中调试代码并单步执行服务器代码时，我们收到以下错误：
ORA-01036: illegal variable name/number
在目标 Oracle 计算机上执行 SQL 命令时，系统提示我们输入绑定 上述语句的变量，并且只要我们使用正确的日期格式，UPDATE语句 工作正常。
问题：
1) 当月份格式错误时，oracle 是否有可能抛出 ORA-01036 错误？
2) 为什么我们不必转换Oracle 机器上运行的 ASP.net 网站的日期格式？ Oracle 是否有排除绑定变量输入屏幕的默认转换例程？
3) 如果日期格式不是问题，ORA-1036 到底意味着什么以及我如何发现 哪个变量有非法名称/编号？
这是一个函数片段，该函数采用数据集类型 (WOName) 并返回适当的 SQL 字符串。 许多案例存在，但为了可读性已被删除。
Private Function GetMainSQLString(ByVal WOName As String) As String
Dim Result As String = ""
Select Case WOName
Case "Monthly Site Inspection"
Dim sb As New StringBuilder
sb.Append("UPDATE WorkOrders SET ")
sb.Append("CompletedPersonID = :CompletedPersonID, CompletedPersonName = :CompletedPersonName, CompleteDate = :CompleteDate, ")
sb.Append("SupervisorID = :SupervisorID, SupervisorName = :SupervisorName ")
sb.Append("WHERE WorkOrderNumber = :WorkOrderNumber")
Result = sb.ToString
End Select
Return Result
End Function
这是一个函数的片段，它采用 Oracle 命令对象 byRef 并向其添加所需的参数， 取决于从客户端程序接收到的可能的 15 种数据集（WOName）中的哪一种。 许多案例存在，但为了可读性已被删除。
然后，更新后的 Cmd 对象返回到主程序逻辑，并在其中调用 ExecuteNonQuery()。
以下参数的测试值如下：
dr.Item("CompletedPersonID") 21
dr.Item("CompletedPersonName") Pers Name
dr.Item("CompleteDate") #8/16/2010#
dr.Item("SupervisorID") 24
dr.Item("SupervisorName") Sup Name
dr.Item("WorkOrderNumber") 100816101830
Private Function addMainCmdParams(ByVal WOName As String, ByRef cmd As OracleCommand, ByVal dr As DataRow) As OracleCommand
Select Case WOName
Case "Monthly Site Inspection"
cmd.Parameters.Add(":CompletedPersonID", Oracle.DataAccess.Client.OracleDbType.Int32).Value = dr.Item("CompletedPersonID")
cmd.Parameters.Add(":CompletedPersonName", Oracle.DataAccess.Client.OracleDbType.Varchar2).Value = dr.Item("CompletedPersonName")
cmd.Parameters.Add(":CompleteDate", Oracle.DataAccess.Client.OracleDbType.Date).Value = dr.Item("CompleteDate")
cmd.Parameters.Add(":SupervisorID", Oracle.DataAccess.Client.OracleDbType.Int32).Value = dr.Item("SupervisorID")
cmd.Parameters.Add(":SupervisorName", Oracle.DataAccess.Client.OracleDbType.Varchar2).Value = dr.Item("SupervisorName")
cmd.Parameters.Add(":WorkOrderNumber", Oracle.DataAccess.Client.OracleDbType.Varchar2).Value = dr.Item("WorkOrderNumber")
End Select
Return cmd
End Function
今天运行时，这个精确的代码是成功的；但另一个类似的案例则不然。我仍然不信任 Oracle 执行的任何隐式类型转换（如果有的话）——而且我特别怀疑 Oracle 如何处理通过 dbNull.value 传递的任何这些参数——而且我知道这将会发生。所以如果这是问题我就必须解决它。有太多的可选参数和列并不总是能够获取传入的值，从而使系统在出现空值时中断。
We are building a client program where parameters for storage in a web server with Oracle backend are set in the .Net client program and uploaded as a dataset via webservice.
In the webservice code, data is read from the dataset and added to UPDATE statements on the web server (Oracle backend).
Because the server will run on the customer's LAN behind a firewall and because of the dynamic nature of the parameters involved, no sprocs are being used - SQL strings are built in the logic.
Here is an example string:
UPDATE WorkOrders
SET TravelTimeHours = :TravelTimeHours,
TravelTimeMinutes = :TravelTimeMinutes,
WorkTimeHours = :WorkTimeHours,
WorkTimeMinutes = :WorkTimeMinutes,
CompletedPersonID = :CompletedPersonID,
CompletedPersonName = :CompletedPersonName,
CompleteDate = :CompleteDate
WHERE WorkOrderNumber = :WorkOrderNumber
When debugging code in VS 2010 and stepping into the server code, we receive the following error:
ORA-01036: illegal variable name/number
when executing the SQL command on destination oracle machine, we were prompted to enter the bind
variables for the above statement, and as long as we used the correct date format, the UPDATE statement
worked correctly.
QUESTIONS:
1) is it possible that oracle threw the ORA-01036 error when the month format was wrong?
2) why don't we have to convert the date format from the ASP.net website running on the Oracle machine?
does Oracle have a default conversion routine that excludes the bind variable entry screen?
3) if the date format was not the problem, what precisely does ORA-1036 mean and how do I discover
WHICH variable had an illegal name/number?
This is a snippet of a function that takes the type of the dataset (WOName) and returns the appropriate SQL string.
Many Cases exist but have been removed for readability.
Private Function GetMainSQLString(ByVal WOName As String) As String
Dim Result As String = ""
Select Case WOName
Case "Monthly Site Inspection"
Dim sb As New StringBuilder
sb.Append("UPDATE WorkOrders SET ")
sb.Append("CompletedPersonID = :CompletedPersonID, CompletedPersonName = :CompletedPersonName, CompleteDate = :CompleteDate, ")
sb.Append("SupervisorID = :SupervisorID, SupervisorName = :SupervisorName ")
sb.Append("WHERE WorkOrderNumber = :WorkOrderNumber")
Result = sb.ToString
End Select
Return Result
End Function
This is a snippet of a function that takes the Oracle command object byRef and adds the required parameters to it,
depending upon which of the possible 15 types of dataset(WOName) is received from the client program.
Many Cases exist but have been removed for readability.
The updated Cmd object is then returned to the main program logic, where ExecuteNonQuery() is called.
The test values of params below are as follows:
dr.Item("CompletedPersonID") 21
dr.Item("CompletedPersonName") Pers Name
dr.Item("CompleteDate") #8/16/2010#
dr.Item("SupervisorID") 24
dr.Item("SupervisorName") Sup Name
dr.Item("WorkOrderNumber") 100816101830
Private Function addMainCmdParams(ByVal WOName As String, ByRef cmd As OracleCommand, ByVal dr As DataRow) As OracleCommand
Select Case WOName
Case "Monthly Site Inspection"
cmd.Parameters.Add(":CompletedPersonID", Oracle.DataAccess.Client.OracleDbType.Int32).Value = dr.Item("CompletedPersonID")
cmd.Parameters.Add(":CompletedPersonName", Oracle.DataAccess.Client.OracleDbType.Varchar2).Value = dr.Item("CompletedPersonName")
cmd.Parameters.Add(":CompleteDate", Oracle.DataAccess.Client.OracleDbType.Date).Value = dr.Item("CompleteDate")
cmd.Parameters.Add(":SupervisorID", Oracle.DataAccess.Client.OracleDbType.Int32).Value = dr.Item("SupervisorID")
cmd.Parameters.Add(":SupervisorName", Oracle.DataAccess.Client.OracleDbType.Varchar2).Value = dr.Item("SupervisorName")
cmd.Parameters.Add(":WorkOrderNumber", Oracle.DataAccess.Client.OracleDbType.Varchar2).Value = dr.Item("WorkOrderNumber")
End Select
Return cmd
End Function
While running this today, this precise code WAS successful; but another similar case was not. I still distrust any implicit typecasting performed by Oracle (if any) - and I'm especially suspicious of how Oracle handles any of these parameters that are passed with a dbNull.value - and I know it's going to happen. so if that's the problem I'll have to work around it. There are too many optional parameters and columns that don't always get values passed in for this system to break on nulls.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
Oracle 可能导致此错误的一个“陷阱”是，默认情况下，Oracle 按顺序（而不是按名称）将参数映射到查询中的参数符号。如果参数的数量/类型不匹配，您会收到类似这样的错误。
解决方案是告诉 Oracle 按名称绑定：
在不深入了解代码细节的情况下，这可能是也可能不是您特定问题的答案，但此设置应该是默认设置，并且应该是任何命令设置的一部分使用参数。看到这一声明解决了一些晦涩的问题，真是令人惊奇。
编辑：这假设您正在使用 Oracle 的数据访问提供程序。在 .NET 中，您应该使用它，而不是 Microsoft 的 Oracle 提供程序。
One Oracle "gotcha" that can cause this error is the fact that, by default, Oracle maps parameters to parameter symbols in the query by sequence, not by name. If the number/type of parameters does not match, you get an error like this one.
The solution is to tell Oracle to bind by name:
Without diving into the details of your code, this may or may not be the answer to your specific problem, but this setting should be the default, and should be part of any command setup that uses parameters. It's rather amazing to watch this one statement fix some obscure problems.
EDIT: This assumes that you're using Oracle's data access provider. In .NET, you should be using this, not Microsoft's Oracle provider.
该错误与日期格式无关，这意味着语句中的变量未绑定。
可能就像拼写错误一样简单（如果 Oracle 在错误消息中包含变量名称那就太好了）。
您能否使用创建、绑定和执行语句的周围代码更新您的问题？
The error has nothing to do with date formats, it means that a variable in the statement was not bound.
Could be as simple as a spelling mistake (would be nice if Oracle included the variable name in the error message).
Can you update your question with the surrounding code that creates, binds, and executes the statement?
这是一个函数片段，它采用数据集的类型 (WOName) 并返回适当的 SQL 字符串。
许多案例存在，但为了可读性已被删除。
这是一个函数的片段，它采用 Oracle 命令对象 byRef 并向其添加所需的参数，
取决于从客户端程序接收到的可能的 15 种数据集（WOName）中的哪一种。
许多案例存在，但为了可读性已被删除。
然后，更新后的 Cmd 对象返回到主程序逻辑，并在其中调用 ExecuteNonQuery()。
以下参数的测试值如下：
今天运行时，这个精确的代码是成功的；但另一个类似的案例则不然。我仍然不信任 Oracle 执行的任何隐式类型转换（如果有的话）——而且我特别怀疑 Oracle 如何处理通过 dbNull.value 传递的任何这些参数——而且我知道这将会发生。所以如果这是问题我就必须解决它。有太多的可选参数和列并不总是能够获取传入的值，从而使系统在出现空值时中断。
This is a snippet of a function that takes the type of the dataset (WOName) and returns the appropriate SQL string.
Many Cases exist but have been removed for readability.
This is a snippet of a function that takes the Oracle command object byRef and adds the required parameters to it,
depending upon which of the possible 15 types of dataset(WOName) is received from the client program.
Many Cases exist but have been removed for readability.
The updated Cmd object is then returned to the main program logic, where ExecuteNonQuery() is called.
The test values of params below are as follows:
While running this today, this precise code WAS successful; but another similar case was not. I still distrust any implicit typecasting performed by Oracle (if any) - and I'm especially suspicious of how Oracle handles any of these parameters that are passed with a dbNull.value - and I know it's going to happen. so if that's the problem I'll have to work around it. There are too many optional parameters and columns that don't always get values passed in for this system to break on nulls.