java.sql.SQLSyntaxErrorException: ORA-00911: invalid character

[sudhakar890]

Hi, I'm using mybatis-migrations-3.3.2-SNAPSHOT and Oracle database. If the SQL code contains the special characters , mybatis is throwing the java.sql.SQLSyntaxErrorException: ORA-00911: invalid character error.
In development.properties file, script_char_set=UTF-8 is defined.

SQL code -
##special characters
v_Field_Temp := REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(v_Field_Temp,'ç,''),'ħ,''),'ŧ,''),'Ƨ,''),'ǧ,''),'ȧ,''),'ɧ,''),'ʧ,''),'ԧ,''),'է,''),'֧,'');

Mybatis output -
. Cause: java.sql.SQLSyntaxErrorException: ORA-00911: invalid character

---

-- MyBatis Migrations FAILURE
-- Total time: 4s
-- Finished at: Wed Nov 29 13:15:30 UTC 2017
-- Final Memory: 27M/479M

ERROR: Error executing command. Cause: org.apache.ibatis.jdbc.RuntimeSqlException: Error executing:
v_Field_Temp := REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(v_Field_Temp,'?,''),'?,''),'?,''),'?,''),'?,''),'?,''),'?,''),'?,''),'?,''),'?,''),'?,'');

[harawata]

Hi @sudhakar890 ,

It seems that each literal is not closed correctly. Have you verified that the syntax is valid?
And make sure to save the file in UTF-8.

[svscorp]

Hi @harawata

I think it has to do with GH formatting. I checked his script in source and it is:
v_Field_Temp := REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(v_Field,'Æ','A'),'æ','ae'),'ã','a'),'Ã','A'),'å','a'),'Å','A'),'á','a'),'Á','A'),'à','a'),'À','A'),'ä','a'),'Ä','A'),'â','a'),'Â','A');

Looks like no issues (from literals perspective).

[harawata]

To narrow down the problem, try some simpler statement with a special character.

insert into table1 (name) values ('Æ');

If it fails, please post oracle version, driver version, database character set (it worked on my environment: Oracle 11.2.0.2.0, ojdbc7-12.1.0.2, NLS_CHARACTERSET=AL32UTF8, NLS_NCHAR_CHARACTERSET=AL16UTF16).

If it works, encoding issue is unlikely and we may need to see the entire script or simple example that reproduces the exception.

[svscorp]

@sudhakar890 can you pls close the issue? It was resolved and the reason was not related to encoding at all.

Actually, the error message was taken from Jenkins output (console) and looks like it's pretty normal to see ? ? ? in the trace. The original issue was due to mess with delimiters and single line comments (mybatis/mybatis-3#1116). Huge 30k line PLSQL scripts :\

@harawata Thank you for support and great tool!

P.S> What do you think is the best in developing/delivering big plsql package / trigger / ... and automating it throughout DTAP? In fact, even a small one-liner change is a new 30k migration file that contains the entire "create or replace", "package body", etc...
P.P.S> Design wise there is an obvious question about the reasons for that size constructions, but we've got what we've got.

[harawata]

Hi @svscorp ,

Good to know it wasn't a Migrations bug. :)

What do you think is the best in developing/delivering big plsql package / trigger / ... and automating it throughout DTAP? In fact, even a small one-liner change is a new 30k migration file that contains the entire "create or replace", "package body", etc...

Assuming your migration scripts are maintained in a Git repository, how about this?

Here is an example directory structure.

BASE_DIR
├── drivers
├── environments
├── hooks
└── scripts
    ├── 001_create_changelog.sql
    ├── 002_create_pkg1.sql
    ├── 003_update_pkg1.sql
    └── pkgs
        └── actual.sql

The file actual.sql is the one that contains the actual package declaration.
When there is a change, you edit this file and commit it to the repo just like a Java source file.

In a migration script, instead of CREATE OR REPLACE PACKAGE..., you write a commit ID (SHA-1) and the path to the actual.sql.
It would look as follows.

commit:fbaad47732929d:scripts/pkgs/actual.sql

-- //@UNDO

commit:12345678abcdef:scripts/pkgs/actual.sql

It would be obvious, but the idea is to let Migrations read the specified version of actual.sql when performing migration.

In version 3.3.2, we can create a custom FileMigrationLoader (#107) that makes it possible.

I kind of liked the idea, so wrote the custom FileMigrationLoader.
https://github.com/harawata/git-migration-loader
Here is an example project that I used for testing.
https://github.com/harawata/git-migration-loader-example

See if it meets your requirement!

p.s.
This could get messy if there is a conflict when pushing the commits to upstream.

[harawata]

I just remembered there was another user who had a similar requirement (#59).
I wrote a hook script that watches files' checksum and execute the SQL when they are modified.
To make it work with down command, his schema requires relatively complex directory structure.