Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Using '/' as delimiter on a new line #2701

Closed
JFNGO opened this issue Mar 2, 2020 · 7 comments
Closed

Using '/' as delimiter on a new line #2701

JFNGO opened this issue Mar 2, 2020 · 7 comments

Comments

@JFNGO
Copy link

JFNGO commented Mar 2, 2020

Which version and edition of Flyway are you using?

6.2.4

If this is not the latest version, can you reproduce the issue with the latest one as well?

(Many bugs are fixed in newer releases and upgrading will often resolve the issue)

Which client are you using? (Command-line, Java API, Maven plugin, Gradle plugin)

Command-line

Which database are you using (type & version)?

Oracle DB

Which operating system are you using?

Windows

What did you do?

(Please include the content causing the issue, any relevant configuration settings, the SQL statement that failed (if relevant) and the command you ran.)
I tried to make some migrations using the '/' on a line alone as delimiter.
For example, just a "CREATE TABLE" statement, that works on Oracle SQL Developer but not using flyway command line

What did you expect to see?

I expected that the migration would be successful and the table would be created

What did you see instead?

The migration failed with an error "ORA-00922: missing or invalid option"

@Lyeeedar
Copy link
Contributor

Lyeeedar commented Mar 3, 2020

Could you please provide an example script that flyway fails to execute?

@JFNGO
Copy link
Author

JFNGO commented Mar 3, 2020

UPDATE company SET name = 'COOL' WHERE record# = 1234567
/

This statement failed. If I'm replacing the '/' by a ';', it's working as expected.

@Lyeeedar
Copy link
Contributor

Lyeeedar commented Mar 4, 2020

I made a script containing only that line and it parsed fine. Is there maybe other stuff in the file that could be causing the actual failure?

@JFNGO
Copy link
Author

JFNGO commented Mar 4, 2020

That is weird, there is nothing else. I tried with an other statement:
image
It didn't work as well.

@Lyeeedar
Copy link
Contributor

Lyeeedar commented Mar 10, 2020

So on investigation the sql as written is invalid, as it has to end with a semicolon to be 'properly ended'. Im guessing that sql developer is adding the semicolons onto the end of statements without one before it executes the sql.

@JFNGO
Copy link
Author

JFNGO commented Mar 13, 2020

I am not sure about Oracle SQL Developer adding semicolons.
According to Oracle documentation:
image

Link of the documentation:
https://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_four.htm#SQPUG014

I also tried the following statement on Oracle SQL Developer:
SELECT record# from company
/
According to the SQL history:
image
It has been executed without adding a semicolon.

@valer-crisan-sage
Copy link

valer-crisan-sage commented Mar 24, 2020

Here is another example. This script works fine in sqlplus and SQL developer against my Oracle 18c database. But it fails in flyway.

create table temp_migr(c1 number)
/
insert into temp_migr values(1)
/
insert into temp_migr values(2);
insert into temp_migr values(3);
commit;
drop table temp_migr
/

The flyway migrate output is:

Flyway Community Edition 6.0.7 by Redgate
Database: jdbc:oracle:thin:@//dev08.intacct.com:1521/dbacct (Oracle 18.0)
Successfully validated 3 migrations (execution time 00:00.179s)
Current version of schema "MIGR_OWNER_01": 0.2
WARNING: outOfOrder mode is active. Migration of schema "MIGR_OWNER_01" may not be reproducible.
Migrating schema "MIGR_OWNER_01" to version 0.3 - delimiter test
ERROR: Migration of schema "MIGR_OWNER_01" to version 0.3 - delimiter test failed! Please restore backups and roll back database and code!
ERROR:
Migration V0.3__delimiter_test.sql failed

SQL State : 42000
Error Code : 922
Message : ORA-00922: missing or invalid option
Location : ./V0.3__delimiter_test.sql (/u02/home/vcrisan/projects/db/app/migr_simple/./V0.3__delimiter_test.sql)
Line : 1
Statement : create table temp_migr(c1 number)
/
insert into temp_migr values(1)
/
insert into temp_migr values(2)

@Lyeeedar Lyeeedar self-assigned this Mar 25, 2020
@Lyeeedar Lyeeedar modified the milestones: Flyway 6.x, Flyway 6.3.3 Mar 27, 2020
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

4 participants