Statement separator for Oracle client provider

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!!
--
Thanks in advance,

Juan Dent, M.Sc.

v-kevy
Thu May 26 00:59:57 CDT 2005

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."

Juan_Dent
Thu May 26 13:18:01 CDT 2005

Hi,

When you say "since you cannot submit multiple statements..." do you mean
that it is not possible to place more than one statement in the UpdateCommand
of an OracleDataAdapter or do you mean "since you have not been able to
successfully submite multiple statements..."?

Thanks again.
--
Thanks in advance,

Juan Dent, M.Sc.


"Kevin Yu [MSFT]" wrote:

> 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."
>
>

v-kevy
Fri May 27 03:09:53 CDT 2005

Hi Juan,

I mean "It is not possible to place more than one statement in the
UpdateCommand of an OracleDataAdapter".

As far as I know, it is not supported since MDAC 2.5. You can check the
following link for more information:

http://support.microsoft.com/default.aspx?scid=kb;en-us;244661

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