search

emacarron / mybatis

Automatically exported from code.google.com/p/mybatis
0 stars 0 forks source link

Can't run migration scripts that have PL/SQL blocks for Oracle #53

Closed GoogleCodeExporter closed 9 years ago

GoogleCodeExporter commented 9 years ago 
What version of the MyBatis are you using?
3.0.1

Please describe the problem.  Unit tests are best!
Added a migration script that contains something like this:
  declare
    ...
  begin
    ...
  end;

When submitting the statement to Oracle on Windows, the lines contain CR-LF. 
Oracle then complains with this error message:

.  Cause: java.sql.SQLException: ORA-06550: line 1, column 9:
PLS-00103: Encountered the symbol "" when expecting one of the following:

   begin function package pragma procedure subtype type use
   <an identifier> <a double-quoted delimited-identifier> form
   current cursor
The symbol "" was ignored.

So, Oracle does not like CR-LF characters. 

What is the expected output? What do you see instead?

Expected is that the PL/SQL block runs without any errors. But, 
Oracle complains about the CR character.

I've patched ScriptRunner.java so that it replaces "\r\n" with "\n" when 
calling statement.execute(). It only does this if the statement begins
with "declare". 

Please provide any additional information below.

I don't consider this a bug with iBatis Migrations, more an Oracle
JDBC driver limitation. But, probably a good idea if IBatis Migrations
would support PL/SQL blocks in scripts.

Original issue reported on code.google.com by gab...@gmail.com on 22 Jun 2010 at 10:41

Attachments:

GoogleCodeExporter commented 9 years ago 
it's the same when creating a procedure, so the it should not only be for 
statements startsWith("declare")

Original comment by kasper.h...@gmail.com on 10 Aug 2011 at 7:23

GoogleCodeExporter commented 9 years ago 
Any news on this issue?

Original comment by bjorn.mo...@gmail.com on 9 Sep 2011 at 5:29

GoogleCodeExporter commented 9 years ago 
Found possible solution with thanks of the guys at github.com

Original comment by michael....@gmail.com on 7 Nov 2011 at 10:26

Attachments:

GoogleCodeExporter commented 9 years ago 
Issue 434 has been merged into this issue.

Original comment by eduardo.macarron on 7 Nov 2011 at 1:23

GoogleCodeExporter commented 9 years ago 
Issue 297 has been merged into this issue.

Original comment by eduardo.macarron on 7 Nov 2011 at 1:23

GoogleCodeExporter commented 9 years ago 
Issue 409 has been merged into this issue.

Original comment by eduardo.macarron on 7 Nov 2011 at 1:23

GoogleCodeExporter commented 9 years ago 
Thanks Michael!

Patch applied in r4001. Please test it and provide feedback.

Original comment by eduardo.macarron on 7 Nov 2011 at 1:26

GoogleCodeExporter commented 9 years ago 
As a user of mybatis I am uncomfortable with the changes made to fix this 
ticket. It seems like there is a great potential for unwanted changes to the 
query in the approach taken.

Original comment by gllo...@gmail.com on 16 Nov 2011 at 2:37

GoogleCodeExporter commented 9 years ago 
Hi gllo...@gmail.com,

Could you please be a bit more specific about the "great potential for unwanted 
changes"? I do not understand.... :-(

It is very common (actually required) to close an Oracle PL/SQL block with 
either a "/" or a "." to indicate the end of the PL/SQL block, this has nothing 
to do with MyBatis.

If you add the "/" or "." at the end, the same code can also be used "without 
any modification" in an SQLPlus session on whatever platform (well I only use 
Unix types and Windows :-)  )
I write "without any modification" because of course for SQLPlus you will need 
to split the MyBatis SQL-file up into 2 separate files if you use the "DO" part 
and the "UNDO" in one MyBatis SQL change-file, otherwise the change will be 
applied and then undone in one go..... :-~

I do not see the "/" in your provided piece of PL/SQL and I doubt whether there 
is a CR/LF issue at all.
I personally tested PL/SQL code in several scripts with MyBatis and SQLPlus and 
can roll forward and backwards without an issue.
Could you please end your PL/SQL block with an "/" on a new line and test again?

  declare
    ...
  begin
    ...
  end;
/

Tnx in advance,
Michael

Original comment by michael....@gmail.com on 16 Nov 2011 at 4:44

GoogleCodeExporter commented 9 years ago 
Michael,

Let me try to understand you..

Are you saying that I'm able to continue using ";" as line delimiter and having 
SQL migrations with PL/SQL statement blocks working when I generate a final 
script (generated via "migrate script" command)?

Original comment by rpo...@gmail.com on 27 Nov 2011 at 2:10

GoogleCodeExporter commented 9 years ago 
Hi rpo..@gmail.com,

If I understand you properly: Yes.
:-)
The code will check whether it is an Oracle DB or not.
If it is Oracle you can use ";" (defined in the environment properties file) 
for normal SQL commands AND you can use ";" for PL/SQL command lines within an 
PL/SQL block which will end with either a "/" or  "."
The "migrate script" will generate the proper script (just tested it).

With regards,
Michael

Original comment by michael....@gmail.com on 28 Nov 2011 at 8:29

GoogleCodeExporter commented 9 years ago 
[deleted comment]
GoogleCodeExporter commented 9 years ago 
Issue 297 has been merged into this issue.

Original comment by eduardo.macarron on 5 Jan 2012 at 8:48

GoogleCodeExporter commented 9 years ago 
Hi guys. We are not confortable with adding specific vendor code in the runner 
so we would like to try another option. 

Please try runnning the last snapshot with the following settings:
runner.setSendFullScript(true)
runner.setRemoveCRs(true)

And post the results.

Original comment by eduardo.macarron on 5 Jan 2012 at 10:30

GoogleCodeExporter commented 9 years ago 
wasn't this patch applied to the last version yet?

Original comment by rpo...@gmail.com on 5 Jan 2012 at 4:30

GoogleCodeExporter commented 9 years ago 
Nop, it was planned for 3.1.0.

Original comment by eduardo.macarron on 5 Jan 2012 at 4:39

GoogleCodeExporter commented 9 years ago 
Was trying to test this resolution, and not sure where to report this, but FYI, 
the snapshots repository doesn't appear to be updating any more? The last 
snapshot posted is December 8, 2011. It would be helpful if that were updated.

Original comment by rbol...@gmail.com on 9 Jan 2012 at 9:38

GoogleCodeExporter commented 9 years ago 
Specifically, here (if this is the right repository) .. 
https://oss.sonatype.org/content/repositories/snapshots/org/mybatis/

Original comment by rbol...@gmail.com on 9 Jan 2012 at 9:39

GoogleCodeExporter commented 9 years ago 
Thanks for pointing it rbolkey!

It is fixed.

Original comment by eduardo.macarron on 12 Jan 2012 at 9:17

GoogleCodeExporter commented 9 years ago 
Issue 441 has been merged into this issue.

Original comment by eduardo.macarron on 16 Jan 2012 at 6:54

GoogleCodeExporter commented 9 years ago 
Has anybody been able to test the snapshot? Can we close this issue?

Original comment by eduardo.macarron on 26 Jan 2012 at 6:18

GoogleCodeExporter commented 9 years ago 
I've had success with the modifications to script runner. I'm using full line 
'/' delimiter for both sql and plsql statements. Never worked with send full 
script though (wasn't a requirement, just fyi for people trying to replicate).

For my use case, I had to modify the migrations package as well (I needed to 
add an environment file property to support the removeCRs property of script 
runner)

Original comment by rbol...@gmail.com on 26 Jan 2012 at 9:10

GoogleCodeExporter commented 9 years ago 
Ok thanks for the info. 

I will close the issue, will reopen it if needed. Thanks everyone.

Original comment by eduardo.macarron on 29 Jan 2012 at 12:26

GoogleCodeExporter commented 9 years ago

Original comment by eduardo.macarron on 1 Feb 2012 at 6:02

GoogleCodeExporter commented 9 years ago

Original comment by eduardo.macarron on 5 Feb 2012 at 6:56

GoogleCodeExporter commented 9 years ago 
Is it possible just configure environment.properties with the new property 
"removeCRs=true"?

Original comment by rpo...@gmail.com on 6 Feb 2012 at 12:54

GoogleCodeExporter commented 9 years ago 
Unfortunately not the last I checked.

BaseCommand#setPropertiesFromFile() needs to be modified to pull a "removeCRs" 
property from the environment file.

Original comment by rbol...@gmail.com on 6 Feb 2012 at 3:40

GoogleCodeExporter commented 9 years ago 
Thank rbolkey I have just updated it in trunk

Original comment by eduardo.macarron on 6 Feb 2012 at 3:56

GoogleCodeExporter commented 9 years ago 
Where could I download the version which is using Michael's patch? 
http://code.google.com/p/mybatis/source/detail?r=4001

Original comment by rpo...@gmail.com on 11 Sep 2012 at 9:25

GoogleCodeExporter commented 9 years ago 
Hi guys,

I think I fixed (with a workaround) this problem when running migrations on 
Windows. This issue really has to with CR-LF in Oracle PL/SQL blocks.

What I had to do was simply forcing the java property "line.separator" to be 
set to "\n" before running migrations. This is necessary because the framework 
always concatenates "CR-LF" to each line of migrations, as you can see in code, 
http://grepcode.com/file/repo1.maven.org/maven2/org.apache.ibatis/ibatis-sqlmap/
3.0-beta-9/org/apache/ibatis/jdbc/ScriptRunner.java#85

After lots of attempts to solve this I only fixed through a wrapper class that 
allowed me to set "line.separator" java property, as you can see here, 
https://gist.github.com/3700735#file_migrator.java and here 
https://gist.github.com/3700735#file_build.xml

Well, this was just a workaround :-)

Original comment by rpo...@gmail.com on 9 Oct 2012 at 8:16