Oracle PL/SQL with Schema Migrations

[Michael Chambliss <em...@mchambliss.com>]

Hello,

I just wanted to follow up on a question that was asked back in October:

http://www.mail-archive.com/user-java@ibatis.apache.org/msg14957.html

In summary, PL/SQL terminates lines with a semicolon, which causes 
problems for the line delimiter logic in the migrations tool.  Something 
like this:

DECLARE
   sometime date;
BEGIN
   select sysdate into sometime from dual;
END;

would fail on line 2.  In a more realistic case, simply creating a 
function or trigger would trip things up.  Was this scenario later 
compensated for via environment configuration options, or has anyone 
found a good work around?

Thanks!
Mike

---------------------------------------------------------------------
To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
For additional commands, e-mail: user-java-help@ibatis.apache.org

Re: Oracle PL/SQL with Schema Migrations

[Michael Chambliss <em...@mchambliss.com>]

Thanks, Clinton.  That does work, with a few potential problems.  In 
order for the DDL to execute properly with a full_line_delimiter set, 
you have to remove the semicolons at the end of single statement blocks 
(IE, create, insert, alter, etc).  If the tool you used to generate the 
bootstrap DDL produced a '/' at the end of PL/SQL blocks, those will 
also need to be removed, but the semicolon at the end of the PL/SQL 
block should be left in tact.

When you create migration scripts, you'll likely want to continue to use 
the full line delimiter for ongoing support of PL/SQL.  The potential 
downside of this is that if you use the "script" option to generate SQL 
for an environment that is not using the migrations tool, it will not 
produce valid SQL due to the delimiter and modifications made above.

This can of course be worked around with some regexes, but it might not 
be as clean as originally intended.

Thanks again for the help!
Mike

Clinton Begin wrote:
> You can configure the line terminator in the environment properties.  
> Try a full line delimiter like "GO"
>
> On Wed, Jan 20, 2010 at 12:29 PM, Michael Chambliss 
> <email@mchambliss.com <ma...@mchambliss.com>> wrote:
>
>     Hello,
>
>     I just wanted to follow up on a question that was asked back in
>     October:
>
>     http://www.mail-archive.com/user-java@ibatis.apache.org/msg14957.html
>
>     In summary, PL/SQL terminates lines with a semicolon, which causes
>     problems for the line delimiter logic in the migrations tool.
>      Something like this:
>
>     DECLARE
>      sometime date;
>     BEGIN
>      select sysdate into sometime from dual;
>     END;
>
>     would fail on line 2.  In a more realistic case, simply creating a
>     function or trigger would trip things up.  Was this scenario later
>     compensated for via environment configuration options, or has
>     anyone found a good work around?
>
>     Thanks!
>     Mike
>
>     ---------------------------------------------------------------------
>     To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
>     <ma...@ibatis.apache.org>
>     For additional commands, e-mail: user-java-help@ibatis.apache.org
>     <ma...@ibatis.apache.org>
>

-- 
Michael Chambliss
email@mchambliss.com

Re: Oracle PL/SQL with Schema Migrations

[Clinton Begin <cl...@gmail.com>]

You can configure the line terminator in the environment properties.  Try a
full line delimiter like "GO"

On Wed, Jan 20, 2010 at 12:29 PM, Michael Chambliss <em...@mchambliss.com>wrote:

> Hello,
>
> I just wanted to follow up on a question that was asked back in October:
>
> http://www.mail-archive.com/user-java@ibatis.apache.org/msg14957.html
>
> In summary, PL/SQL terminates lines with a semicolon, which causes problems
> for the line delimiter logic in the migrations tool.  Something like this:
>
> DECLARE
>  sometime date;
> BEGIN
>  select sysdate into sometime from dual;
> END;
>
> would fail on line 2.  In a more realistic case, simply creating a function
> or trigger would trip things up.  Was this scenario later compensated for
> via environment configuration options, or has anyone found a good work
> around?
>
> Thanks!
> Mike
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
> For additional commands, e-mail: user-java-help@ibatis.apache.org
>
>