Execution against Oracle returns ORA-00922: missing or invalid option #283
Comments
|
G'day @twerthi, There's definitely something funny going on here, thanks for the report. Our unit tests are run against the 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? |
|
Hey there @wokket! Apologies for the delay, I was out on holiday :) I switched my container to |
|
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). |
|
Thanks for investigating, @wokket . I would vote for this being an error in the script, in the same way that 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 |
|
I tried running this I made sure there wasn't a trailing |
|
Scratch that, I had forgotten I had another script file in the |
|
Tried running the initial statement of But it's failing with 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 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 😁 |
|
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? |
|
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. 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. |
|
Hey there @erikbra! Were you able to repro the issue? Or was I holding it wrong? |
|
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 |
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.sqlin theupfolderCREATE 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
actorScreenshots
If applicable, add screenshots to help explain your problem.
Desktop (please complete the following information):
Additional context
Add any other context about the problem here.
The text was updated successfully, but these errors were encountered: