Statement separator for Oracle client provider

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I have a large portion of an application (actually some sort of middleware)
that has been developed first for SqlServer and now I am in the process of
making it accessible to Oracle as well.

The problem is that I use DataSets where, for instance, the UpdateCommand
contains an UPDATE sql statement followed by a semicolon and a SELECT sql
statement, all of this in the same CommandText. In this way, and in one
single round-trip to the server, I can modify and obtain a fresh copy of the
record.

With Oracle provider, this is not working at all since Oracle dislikes the
';' and throws an exception (invalid character...).

I have tried this in ORACLE SQL Plus Worksheet and works fine if one places
a newline after the ';', but my hopes were seriously affected when I tried it
in ORACLE SQL Plus, where it does not accept the semicolon regardless of a
following newline.

Can somebody shed some light on this, please!!
 
Hi Juan,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you cannot submit multiple statement in
one OracleCommand. If there is any misunderstanding, please feel free to
let me know.

To refresh the data in from an Oracle data source, since you cannot submit
multiple statements, you have to trap the RowUpdated event and then post
the new identity value.

Here is an example:

Private Sub da_Handle_OracleRowUpdated(ByVal sender As Object, ByVal e As
OracleRowUpdatedEventArgs)
If e.Status = UpdateStatus.Continue And e.StatementType =
StatementType.Insert Then
e.Row("eimkey") = GetOracleSequence(e.Command.Connection)
e.Row.AcceptChanges()

'use this if you do not want to AcceptChanges for each row.
'e.Status = UpdateStatus.SkipCurrentRow
End If
End Sub

Private Function GetOracleSequence(ByRef cnn As OracleConnection) As
Integer
Dim oCmd As New OracleCommand("SELECT SEQ_EIMHDR_EIMKEY.CURRVAL FROM
DUAL", cnn)
Dim x As Object = oCmd.ExecuteScalar()
Return CInt(x)
End Function

HTH.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Back
Top