I've found that using Oracle from ASP.NET to be a great experience.... except when things go wrong. Then, suddenly - I find that I'm in some kind of "no-man's land" where other people may or may not have experienced the same thing as me. But, usually it was more than a year ago, for a different version of .NET and no real solutions. I'm going to try and start documenting my trials and tribulations in this knowledgebase, because that's what it is here for.
For this particular issue, when I had a DbCommand object that was created with an OracleClientFactory - I was getting a failure, ORA-12571: TNS:packet writer failure whenever using string procedure parameters. The code that illustrates this error is:
Dim cmd As DbCommand = Me.Factory.CreateCommand() '** this is a valid OracleFactory object
Dim param As DbParameter = Nothing
Dim sql As String = ""
sql = "SELECT ard.*" & Environment.NewLine & _
"FROM MyTable ard" & Environment.NewLine & _
"WHERE ard.Client_Nbr = :ClientNbr" & Environment.NewLine
param = Me.Factory.CreateParameter()
param.DbType = DbType.String
param.Direction = ParameterDirection.Input
param.ParameterName = "Client_Nbr"
param.Value = "Yaba"
cmd.Parameters.Add(param)
cmd.Connection = Me.Connection '** this is a valid DbConnection to an Oracle database
cmd.CommandText = sql
dr = cmd.ExecuteReader()
To fix this issue, I had to use the DbType.AnsiString instead of DbType.String:
Dim cmd As DbCommand = Me.Factory.CreateCommand() '** this is a valid OracleFactory object
Dim param As DbParameter = Nothing
Dim sql As String = ""
sql = "SELECT ard.*" & Environment.NewLine & _
"FROM MyTable ard" & Environment.NewLine & _
"WHERE ard.Client_Nbr = :ClientNbr" & Environment.NewLine
param = Me.Factory.CreateParameter()
param.DbType = DbType.AnsiString
param.Direction = ParameterDirection.Input
param.ParameterName = "Client_Nbr"
param.Value = "Yaba"
cmd.Parameters.Add(param)
cmd.Connection = Me.Connection '** this is a valid DbConnection to an Oracle database
cmd.CommandText = sql
dr = cmd.ExecuteReader()
Sadly, I don't necessarily know why this works or what reprecussions may come of this. But, it works... |
