Oracle, VARCHAR2 and portability

General Discussion
1

In Oracle world almost no one use VARCHAR type. VARCHAR2 is used instead.
So common Oracle equivalent for Java String type is VARCHAR2, not VARCHAR.
But there is problem, how to write portable change logs - what type should be used?
We can use VARCHAR2 and it will work fine on Oracle, but will fail on other databases.
We can use VARCHAR and it will pass OK on all databases, but we don’t want it on Oracle.
Of course we can write double changes: one for Oracle, one for other and use preconditions - but it’s error prone.

So it would be better if liquibase did automatic translation from VARCHAR to VARCHAR2. Or maybe even better would be if one can specify type “String(40)” and liquibase translated it (depending on database) to VARCHAR(40) or VARCHAR2(40).
And “String(10000)” should be translated to CLOB.

2

Another way to resolve this problem is to create “typedef” feature to Liquibase:
user could define own types (especially in database context) and then use them in changesets. For example:

Or even better:

3

You can specify “varchar” as the datatype currently and liquibase will convert it to varchar2 when it sends it to oracle.  You can also use “java.sql.Types.Varchar(20)” as the datatype in liquibase and it will convert it to the correct type per database. 

I like the typedef feature, I created a feature request for it: http://liquibase.jira.com/browse/CORE-422

Nathan

4
Originally posted by: Nathan
You can specify "varchar" as the datatype currently and liquibase will convert it to varchar2 when it sends it to oracle.  You can also use "java.sql.Types.Varchar(20)" as the datatype in liquibase and it will convert it to the correct type per database. 

Really? Sounds great!
Should be mentioned in manual…
Thanks for reply.

5

It is quickly mentioned on http://www.liquibase.org/manual/column, but does need to be more explicit.  Thanks for the suggestion.

6

But there are two different Varchar2 flavors:


Varchar2(byte)

Varchar2(char)


Liquibase (or JDBC driver) by default define varchar2(byte). How can I configure changeset for varchar2(char)? With multibyte database encoding byte version does not make sense.


Thanks,

Marcin

7

I think char or byte can be controlled by oracle database semantics setting in pfile or spfile

8

liquibase does support using the varchar2(250 char) syntax, you should be able to use that when defining your tables. Or are you looking for something more global?


Nathan