liquibase generateChangeLog for Oracle includes invalid SQL

General Discussion
1

Hi,
    I tried to copy the schema from an existing database to a new one using generateChangeLog on the old database followed by migrate/update (is there difference or are they just aliases?) on the new one.

The update failed with  "ORA-01401: inserted value too large for column"

After a little digging I identified part of the problem the sql produced by updateSQL is:

CREATE TABLE BT0004 (B0004TIMESTAMP VARCHAR2(26 BYTE) DEFAULT
’(TO_CHAR(SYSDATE, ‘‘YYYY-MM-DD-HH24.MI.SS’’))       ’ NOT NULL,  

If I replace '(TO_CHAR(SYSDATE, ‘‘YYYY-MM-DD-HH24.MI.SS’’))       ’ with say 'foo’
the import works.

From SQLPlus the default is:

SQL> select data_default from user_tab_columns where table_name=‘BT0004’ and column_name=‘B0004TIMESTAMP’;

DATA_DEFAULT
--------------------------------------------------------------------------------
(TO_CHAR(SYSDATE, ‘YYYY-MM-DD-HH24.MI.SS’))

I’m not exactly sure what’s gone wrong (I am relatively new to Oracle as well as liquibase).
Is this a known bug?
What is the best workaround? I am likely to encounter several more instances of this.

Regards,

Bruce.

2

update/migrate are synonyms. We started with migrate then switched to update to better follow a SVN-style terminology.


Does it help if you use VARCHAR(26) rather than VARCHAR(26 byte)?


I assume you are using liquibase 2.0.1?

Nathan

3

Sorry I should have included that in the original report. Yes I am using liquibase Version: 2.0.1

The byte is not the problem. It seems to be just a noise word.
The problem is the string generated for the to_char(sys_date()) function as produced by liquibase is too long.
38 characters rather than 26 which is suspiciously close to the length of the function definition.
I think its being output as text rather than as a function.

4

Yes, I see the problem is that it is including ’ marks around the function. I added http://liquibase.jira.com/browse/CORE-954 to track the bug.


Nathan