Execution against Oracle returns ORA-00922: missing or invalid option

[twerthi]

Describe the bug
I'm attempting to use grate against an Oracle container (gvenzl/oracle-xe) and am constantly running into ORA-00922: missing or invalid option. The research that I've done on that error indicates that there is something wrong with the syntax of the statement I'm trying to run, however, I've manually executed it using Oracle SQL Developer as well as DBUp successfully. Here's one of the statements I'm trying to run that fails

CREATE TABLE actor (
actor_id numeric NOT NULL ,
first_name VARCHAR(45) NOT NULL,
last_name VARCHAR(45) NOT NULL,
last_update DATE NOT NULL,
CONSTRAINT pk_actor PRIMARY KEY (actor_id)
);

To Reproduce
Add the following to a file called script0001.tables.sql in the up folder

CREATE TABLE actor (
actor_id numeric NOT NULL ,
first_name VARCHAR(45) NOT NULL,
last_name VARCHAR(45) NOT NULL,
last_update DATE NOT NULL,
CONSTRAINT pk_actor PRIMARY KEY (actor_id)
);

Expected behavior
The execution should successfully complete and create a table called actor

Screenshots
If applicable, add screenshots to help explain your problem.

Desktop (please complete the following information):

• OS: Windows Server 2019
• Version 1.4.0

Additional context
Add any other context about the problem here.

[wokket]

G'day @twerthi,

There's definitely something funny going on here, thanks for the report.

Our unit tests are run against the oracleinanutshell/oracle-xe-11g:latest container, and a repro on the 1.4 codebase above is giving ORA-00911: invalid character which matches with your syntax issue, but I'm not sure if that's something to do with my repro test, I'm not an oracle guy in any way!

Would you mind trying the scenario on your side against that container and let us know if you still get ORA-00922, ORA-00911, or something else entirely?

[twerthi]

Hey there @wokket! Apologies for the delay, I was out on holiday :) I switched my container to oracleinanutshell/oracle-xe-11g:latest and am getting the same ORA-00911 you are.

Running grate v1.4.0.0 against Oracle2:1521/xe - . 

Looking in . for scripts to run. 

================================================================================ 

Setup, Backup, Create/Restore/Drop 

================================================================================ 

================================================================================ 

Grate Structure 

================================================================================ 

================================================================================ 

Versioning 

================================================================================ 

 Migrating  from version 0.0.0.0 to 1.0.22357.183215. 

 Versioning  database with version 1.0.22357.183215. 

================================================================================ 

Migration Scripts 

================================================================================ 

Skipping 'BeforeMigration', beforeMigration does not exist. 

Skipping 'AlterDatabase', alterDatabase does not exist. 

Skipping 'Run After Create Database', runAfterCreateDatabase does not exist. 

Skipping 'Run Before Update', runBeforeUp does not exist. 

 

Looking for Update scripts in ".\up". These should be one time only scripts. 

-------------------------------------------------------------------------------- 

  Running 'script0001.tables.sql'. 

Error running script "script0001.tables.sql": ORA-00911: invalid character 

Skipping 'Permissions', permissions does not exist. 

Skipping 'AfterMigration', afterMigration does not exist. 

Unhandled exception: grate.Exceptions.MigrationFailed: Migration failed due to errors: 

 * ORA-00911: invalid character 

 ---> Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-00911: invalid character 

   at OracleInternal.ServiceObjects.OracleConnectionImpl.VerifyExecution(Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, SqlStatementType sqlStatementType, Int32 arrayBindCount, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone) 

   at OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteNonQuery(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, Int32 longFetchSize, Int64 clientInitialLOBFS, OracleDependencyImpl orclDependencyImpl, Int64[]& scnFromExecution, OracleParameterCollection& bindByPositionParamColl, Boolean& bBindParamPresent, OracleException& exceptionForArrayBindDML, OracleConnection connection, Boolean isFromEF) 

   at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteNonQuery() 

   at System.Data.Common.DbCommand.ExecuteNonQueryAsync(CancellationToken cancellationToken) 

--- End of stack trace from previous location --- 

   at grate.Migration.AnsiSqlDatabase.ExecuteNonQuery(DbConnection conn, String sql, Nullable`1 timeout) in /home/runner/work/grate/grate/grate/Migration/AnsiSqlDatabase.cs:line 664 

   at grate.Migration.AnsiSqlDatabase.ExecuteNonQuery(DbConnection conn, String sql, Nullable`1 timeout) in /home/runner/work/grate/grate/grate/Migration/AnsiSqlDatabase.cs:line 664 

   at grate.Migration.AnsiSqlDatabase.RunSql(String sql, ConnectionType connectionType, TransactionHandling transactionHandling) in /home/runner/work/grate/grate/grate/Migration/AnsiSqlDatabase.cs:line 464 

   at grate.Migration.DbMigrator.RunTheActualSql(String sql, String scriptName, MigrationType migrationType, Int64 versionId, ConnectionType connectionType, TransactionHandling transactionHandling) in /home/runner/work/grate/grate/grate/Migration/DbMigrator.cs:line 249 

   at grate.Migration.DbMigrator.RunTheActualSql(String sql, String scriptName, MigrationType migrationType, Int64 versionId, ConnectionType connectionType, TransactionHandling transactionHandling) in /home/runner/work/grate/grate/grate/Migration/DbMigrator.cs:line 270 

   at grate.Migration.DbMigrator.<>c__DisplayClass28_0.<<RunSql>g__LogAndRunSql|0>d.MoveNext() in /home/runner/work/grate/grate/grate/Migration/DbMigrator.cs:line 93 

--- End of stack trace from previous location --- 

   at grate.Migration.DbMigrator.RunSql(String sql, String scriptName, MigrationType migrationType, Int64 versionId, GrateEnvironment environment, ConnectionType connectionType, TransactionHandling transactionHandling) in /home/runner/work/grate/grate/grate/Migration/DbMigrator.cs:line 152 

   at grate.Migration.GrateMigrator.Process(DirectoryInfo root, MigrationsFolder folder, String changeDropFolder, Int64 versionId, ConnectionType connectionType, TransactionHandling transactionHandling) in /home/runner/work/grate/grate/grate/Migration/GrateMigrator.cs:line 310 

   at grate.Migration.GrateMigrator.LogAndProcess(DirectoryInfo root, MigrationsFolder folder, String changeDropFolder, Int64 versionId, ConnectionType connectionType, TransactionHandling transactionHandling) in /home/runner/work/grate/grate/grate/Migration/GrateMigrator.cs:line 285 

   at grate.Migration.GrateMigrator.Migrate() in /home/runner/work/grate/grate/grate/Migration/GrateMigrator.cs:line 124 

   --- End of inner exception stack trace --- 

   at grate.Migration.GrateMigrator.Migrate() in /home/runner/work/grate/grate/grate/Migration/GrateMigrator.cs:line 161 

   at grate.Commands.MigrateCommand.<>c__DisplayClass0_0.<<-ctor>b__0>d.MoveNext() in /home/runner/work/grate/grate/grate/Commands/MigrateCommand.cs:line 48 

--- End of stack trace from previous location --- 

   at System.CommandLine.NamingConventionBinder.CommandHandler.GetExitCodeAsync(Object returnValue, InvocationContext context) 

   at System.CommandLine.NamingConventionBinder.ModelBindingCommandHandler.InvokeAsync(InvocationContext context) 

   at System.CommandLine.Invocation.InvocationPipeline.<>c__DisplayClass4_0.<<BuildInvocationChain>b__0>d.MoveNext() 

--- End of stack trace from previous location --- 

   at System.CommandLine.Builder.CommandLineBuilderExtensions.<>c__DisplayClass17_0.<<UseParseErrorReporting>b__0>d.MoveNext() 

--- End of stack trace from previous location --- 

   at System.CommandLine.Builder.CommandLineBuilderExtensions.<>c__DisplayClass12_0.<<UseHelp>b__0>d.MoveNext() 

--- End of stack trace from previous location --- 

   at System.CommandLine.Builder.CommandLineBuilderExtensions.<>c__DisplayClass19_0.<<UseTypoCorrections>b__0>d.MoveNext() 

--- End of stack trace from previous location --- 

   at System.CommandLine.Builder.CommandLineBuilderExtensions.<>c.<<UseSuggestDirective>b__18_0>d.MoveNext() 

--- End of stack trace from previous location --- 

   at System.CommandLine.Builder.CommandLineBuilderExtensions.<>c__DisplayClass16_0.<<UseParseDirective>b__0>d.MoveNext() 

--- End of stack trace from previous location --- 

   at System.CommandLine.Builder.CommandLineBuilderExtensions.<>c.<<RegisterWithDotnetSuggest>b__5_0>d.MoveNext() 

--- End of stack trace from previous location --- 

   at System.CommandLine.Builder.CommandLineBuilderExtensions.<>c__DisplayClass8_0.<<UseExceptionHandler>b__0>d.MoveNext()

[wokket]

Thanks mate,

I looks like it's the trailing semicolon causing the issues... I'd assume Oracle would be fine with a statement separator but the apparently not?? https://renenyffenegger.ch/notes/development/databases/Oracle/errors/ORA-00922_missing-or-invalid-option/

Again I'm not an oracle guy by any stretch so I'll defer to @erikbra for whether this is a bug (because other tools are handling this ok) or considered an input script error (because the DBMS says no).

[erikbra]

Thanks for investigating, @wokket . I would vote for this being an error in the script, in the same way that GO in SQL server scripts is not a valid SQL command, but a command for SQL server management studio to split batches. There is, however, some logic in BatchSplitter and **Syntax.StatementSeparatorRegex, which is supposed to handle this. I think there might be an error in the separator regex for OracleSyntax, perhaps.

I see that we have only tests for statement splitting for SQL server, here: https://github.com/erikbra/grate/tree/main/grate.unittests/Basic/Infrastructure/SqlServer/Statement_Splitting

If you are fluent with Oracle Syntax, @twerthi , do you think you can compose some similar tests to these, only for Oracle, so that we can have some tests fail, and then implement the fixes to make it work? Or, at least provide the rules that should apply for Oracle. I haven't used Oracle in ages, and don't remember all the syntax rules for it

[twerthi]

I tried running this

  CREATE TABLE "SYSTEM"."TABLE1" 
   (	"COLUMN1" VARCHAR2(20 BYTE)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 

I made sure there wasn't a trailing ;, however, I still receive the ORA-00911 running against oracleinanutshell/oracle-xe-11g:latest. I too am not an Oracle expert, know just enough to get those other solutions to function, but not much more than that. What is odd is that it would seem the statement actually worked, yet still reported the error. Using Oracle Sql Developer, I can see table is indeed created.

[twerthi]

Scratch that, I had forgotten I had another script file in the views folder, that's where the error occurred this time. The above table creation statement completed successfully. So it does seem to be the trailing ; causing it.

[twerthi]

Tried running the initial statement of

CREATE TABLE actor (
  actor_id numeric NOT NULL ,
  first_name VARCHAR(45) NOT NULL,
  last_name VARCHAR(45) NOT NULL,
  last_update DATE NOT NULL,
  CONSTRAINT pk_actor PRIMARY KEY  (actor_id)
)

But it's failing with
ServerTasks-102633.log.txt

This file is what I use for Flyway and it works, so I don't think it's syntax, but I very well could be wrong. I realize it's not apples-to-apples in that Flyway is Java-based and Grate is .NET

FlywayOracle.txt

Other than being a test runner, my knowledge of Oracle is quite limited. I was attempting to add Grate as an example of doing Oracle deployments with Octopus Deploy, but am unable to get past this hurdle.

I have it working for MariaDB, MySQL, PostgreSQL, and Microsoft SQL Server 😁
☝️ (Not meant to plug the product, just to show you what my end goal was. If those links are inappropriate, please let me know and I will remove them)

[erikbra]

Hi again, @twerthi ! I really don't mind the "product placement" at all. Octopus Deploy is a wonderful tool, and I've used it a lot in the past, from the time when it was a one-man shop with Paul Stovell as the only guy on-board :)

I'd appreciate if grate were part of these examples as well, and I do think there might be grate-related problems here. The Oracle codebase hasn't got much love, and it's been ages since I used Oracle professionally myself. But, we'll get it to work anyways, won't we? :)

To make the debugging easier, since this is an "example deployment" database, would you be able to share the exact Oracle scripts that you use for the pipelines, so that I can test them here myself, and step through a bit, to see if I can pin-point the error?

[twerthi]

Certainly! Here is the code for the step template that it runs. I've attached the package that it's using for deployment as well as what I was basing it off of, the SQL I used for Flyway.
sakila.oracle.grate.db.1.0.23003.235231.zip
Flyway.sql.txt

At the moment, most of the grate SQL has been commented out as I was attempting to get the first statement to work properly before moving on.

[twerthi]

Hey there @erikbra! Were you able to repro the issue? Or was I holding it wrong?

[Dabeto83]

Hi @twerthi, @erikbra I'm trying to use grate with oracle too and I have run into the same issue. Do you have any Idea how to solve or avoid this issue?

This is the script I'm running
0001_CreateTables.txt

[Dabeto83]

Hi again @twerthi, @erikbra I've made a change in my script I've just removed the ';' and I've added an empty line at the end of the file and it works.

0001_CreateTables_fixed.txt