Page 1 of 1
ORA-00922: missing or invalid option
Posted: Tue 25 Mar 2014 12:49
by 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
Re: ORA-00922: missing or invalid option
Posted: Thu 27 Mar 2014 12:13
by Shalex
Re: ORA-00922: missing or invalid option
Posted: Thu 27 Mar 2014 12:41
by 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?
Re: ORA-00922: missing or invalid option
Posted: Fri 28 Mar 2014 16:50
by Shalex
Dennis Wanke wrote: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.
Re: ORA-00922: missing or invalid option
Posted: Sat 29 Mar 2014 09:16
by 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?
Re: ORA-00922: missing or invalid option
Posted: Wed 02 Apr 2014 10:27
by Shalex
Please try using a code like
Code: Select all
var commands = createDatabaseScriptText.Split('/');
foreach (string sql in commands) {
// execute sql
}