[PATCH] delimiter and delimiterType have no effect in script mode about dbdeploy HOT 9 CLOSED

Missed a file in the previous patch.

Thanks for the patch. What was the specific problem you were seeing? Is it that 
one of the database script formatters is generating the wrong script? Which 
database are you using?

The main purpose for moving to using freemarker was to avoid scripts being 
built by string concatenation, which it transpired made it much harder to 
understand what was actually being generated, and generated bugs and 
interesting cases. This patch I'm slightly nervous about as it feels like it's 
moving us back to this world.

I'm guessing you're using oracle and sqlplus. We found that it was dangerous to 
automatically include a "/" in the script generated by dbdeploy, since this can 
cause statements to be executed more than once if the creator of the script 
already included a / at the end or didn't include a PL/SQL statement. So the 
approach we take is to include a "/" on the end of any scripts that include 
PL/SQL or SP declarations, inside the script.

In fact, we got so fed up with the dreadful error handling of sqlplus (and its 
reluctance to ever return a non-zero error code) that we've switched to using 
dbdeploy's "direct to db" mode instead of generating a script.

Have you tried using the approach detailed in GeneratingAndCustomisingScripts 
to build your own templates? I'd be very happy to include a "ora-sqlplus" 
template and/or update the ora_apply.sql with the changes you need to make your 
scripts run with sqlplus.

This is an issue with Oracle and PL/SQL scripts as you surmised. When you 
create package bodies in PL/SQL they look like this...

CREATE OR REPLACE PACKAGE my_package AS

  FUNCTION my_function RETURN PLS_INTEGER;

END my_package;

If use the default delimiter of ; with the default delimiterType of normal then 
if you configure the sql-maven-plugin to execute this file it will take the ; 
characters as delimiters and fail to parse the script. The ; at the end of the 
my_function line can't be replaced by any other character so we need to have a 
different delimiter. 

The default for dbdeploy is to have a delimiter of ; and a delimiterType of 
normal IIRC so unless you override these then nothing changes in the output 
with my patch. If you do want a different delimiter in script creation mode 
then it doesn't work as you still get a commit; in the delta file and that 
won't work when the delimiter is /.

If you want to have new ora-sqlplus_apply.ftl and ora-sqlplus_undo.ftl scripts 
which would be the same as ora_apply.ftl and ora_undo.ftl but with the changes 
from my patch (and not apply the changes to any of the existing .ftl files) 
that would work for me. The other code changes would need to be applied so that 
the delimiter and delimiterType values can affect those sqlplus ftl files. I 
can create a patch to do this if you wish.

Yeah. On second thoughts, I agree with your patch - as a user of dbdeploy it 
makes a lot of sense for the delimiter settings to apply to all the generated 
scripts too. And as you say this is a non-breaking change since the defaults 
mean the scripts are just as they are.

I'll apply before a 3.0 release.

Commited patch in 
https://github.com/tackley/dbdeploy/commit/23ab556af50b02ae46c76eac41a77813c9262
0b2

And in svn r171

Hi 

Has this fix been released? I'm using version 3.0M3 and don't think the fix in 
this version as the delimiter is alawys a ';'.