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.