ORA-00922: missing or invalid option

[Dennis Wanke]

Executing the following simple command inside an ObjectContext causes ORA-00922: missing or invalid option:

Code: Select all

ExecuteStoreCommand(CreateDatabaseScript())

[/b]

I noticed the ORA-00922 error is raised if no delimiter is used between single statements in the script. A script generated by CreateDatabaseScript() uses slashes (/) as delimiters, however (but not after the last statement). That delimiters are probably removed when executing the script using ExecuteStoreCommand().

This issue may also be related: http://forums.devart.com/viewtopic.php?f=1&t=29232

[Shalex]

Please refer to http://forums.devart.com/viewtopic.php?t=29216.

[Dennis Wanke]

Ok, I see. The problem is: we use an ORM (in this Entity Framework) in our product to go as much database- and provider-agnostic as possible. And dotConnect/Oracle is only of the several database/provider options that our product supports. We just cannot afford us to tailor product code for every provider we use.

What I don't quite understand is why do you need to have two classes to execute multi-statement commands? Why can't OracleCommand handle them just like SqlCommand does?

[Shalex]

What I don't quite understand is why do you need to have two classes to execute multi-statement commands? Why can't OracleCommand handle them just like SqlCommand does?

SQL Server and Oracle are two different database management systems.
OracleCommand (of dotConnect for Oracle or ODP.NET or System.Data.OracleClient) executes only one SQL statement. This is a limitation of Oracle server.

[Dennis Wanke]

I tested this with ODP.NET and found it behaves the same way. So, at least the behavior of dotConnect is consistent with that of native Oracle provider.

We probably will have to implement a workaround by splitting a script generated with CreateDatabaseScript() on the known statement delimiter (a slash, as stated in http://forums.devart.com/viewtopic.php?f=1&t=29216). Could you suggest a better solution? Is there any helper methods in dotConnect for doing this? If not, could it be provided in the next releases?

[Shalex]

Please try using a code like

Code: Select all

var commands = createDatabaseScriptText.Split('/');
foreach (string sql in commands) {
  // execute sql
}