ORA-00922: missing or invalid option

ORA-00922: missing or invalid option

Postby Dennis Wanke » Tue 25 Mar 2014 12:49

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

Code: Select all
ExecuteStoreCommand(CreateDatabaseScript())


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
Dennis Wanke
 
Posts: 57
Joined: Tue 11 Mar 2014 07:49

Re: ORA-00922: missing or invalid option

Postby Shalex » Thu 27 Mar 2014 12:13

Shalex
Devart Team
 
Posts: 7530
Joined: Thu 14 Aug 2008 12:44

Re: ORA-00922: missing or invalid option

Postby Dennis Wanke » Thu 27 Mar 2014 12:41

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?
Dennis Wanke
 
Posts: 57
Joined: Tue 11 Mar 2014 07:49

Re: ORA-00922: missing or invalid option

Postby Shalex » Fri 28 Mar 2014 16:50

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.
Shalex
Devart Team
 
Posts: 7530
Joined: Thu 14 Aug 2008 12:44

Re: ORA-00922: missing or invalid option

Postby Dennis Wanke » Sat 29 Mar 2014 09:16

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?
Dennis Wanke
 
Posts: 57
Joined: Tue 11 Mar 2014 07:49

Re: ORA-00922: missing or invalid option

Postby Shalex » Wed 02 Apr 2014 10:27

Please try using a code like
Code: Select all
var commands = createDatabaseScriptText.Split('/');
foreach (string sql in commands) {
  // execute sql
}
Shalex
Devart Team
 
Posts: 7530
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for Oracle