Bug in OracleClient?

[Dave Cantrell]

I've got a bug that I'd like to report regarding the OracleClient, but
can't see any way to do so on the Microsoft Support site.

Since Microsoft reps listen here, I'll post the bug and it's
workaround here.

Thanks...
-dave

--- Bug Report Follows ---

PROBLEM:
In the Microsoft OracleClient data provider namespace for .NET,
reading in an anonymous PL/SQL block from a text file and then
executing causes an ORA-6550 error.

CAUSE:
The Oracle PL/SQL engine apparently cannot handle carriage-return
characters, but the .NET OracleClient data provider passes the
carriage-returns to the engine anyway.

IMPACT:
Operation fails.

WORK-AROUND:
Strip all carriage-return characters (ASCII code 13) from the PL/SQL
block before submitting to the Oracle database PL/SQL engine.

LONG-TERM SOLUTION:
OracleClient objects (OracleCommand?) should handle this translation
automatically.

SAMPLE CODE:
The following code illustrates the problem and work-around. I replaced
CR/LF characters with strictly line-feed characters (ASCII code 10)
for readability, but blank spaces work fine as well. I also hard-coded
a very basic PL/SQL block with vbCrLfs to simulate reading in from a
text file.

Public Sub TestOracleClientProblem()
dim conn as New OracleClient.OracleConnection( yourConnectionString
)
conn.Open()

dim sql as String = "begin" & vbCrLf & "null;" & vbCrLf & "end;"
'failing to uncomment the next line results in an Oracle error
'sql = sql.Replace( vbCrLf, Chr( 10 ) )
dim cmd as New OracleClient.OracleCommand( sql, conn )

MsgBox( sql )

Try
cmd.ExecuteNonQuery()
Catch ex as OracleClient.OracleException
MsgBox( "FAILURE:" & vbCrLf & ex.Message )
Finally
cmd = Nothing
conn.Close()
conn = Nothing
End Try
End Sub

 

[Christian Boult]

Yes that one was a bit anoying.
I was using Environment.Newline (SQlServer version) but had to change it to
'\n' since for Oracle. Note that Oracle's version of the provider does the
same thing. The problem isn't with MS it's with Oracle I think.

Chris.