Loading data with dates in Oracle throws DatabaseException

General Discussion
1

Loading data with dates in Oracle throws the following exception:

liquibase.exception.DatabaseException: java.sql.SQLException: ORA-01861: literal does not match format string

The simplified create statement (PostgreSQL) of table Usr is as follows:

CREATE TABLE “public”.usr_specs (

usr_specs_id

serial NOT NULL,

usr_id

integer NOT NULL,

usr_name

varchar(255) NOT NULL,

usr_fullname

varchar(100) NOT NULL,

descr

varchar(255) ,

blocked

integer ,

d_beg

date NOT NULL,

d_end

date ,

dt_rec

timestamp NOT NULL,

dt_sup

timestamp ,

mut_usr_id

integer NOT NULL,

sup_usr_id

integer ,

email

varchar(255) ,

tel

varchar(40) ,

CONSTRAINT pk_usr_specs PRIMARY KEY ( usr_specs_id )

);

This is the contents of usr_specs.csv:

usr_specs_id,usr_id,usr_name,usr_fullname,descr,blocked,d_beg,d_end,dt_rec,dt_sup,mut_usr_id,sup_usr_id

1,1,admin,Administrator,0,2000-01-01 00:00:00,NULL,2000-01-01 00:00:00,NULL,1,NULL

Loading the same data into a PostgreSQL database, everything works fine.

Any idea why Liquibase is throwing this exception using a Oracle database?

2

The problem must be in the format of either some or all the various date and timestamp fields. Oracle is probably more stringent about those than postgres is. The create table statement says that d_beg and d_end are both of type ‘date’, and that dt_rec and dt_sup are both ‘timestamp’, but the data for all four fields follows the same format of ‘yyyy-mm-dd hh:MM:ss’. You’d have to check the oracle docs for what the correct format is for each of those datatypes.

Steve Donie
Principal Software Engineer
Datical, Inc. http://www.datical.com/

3

You’re right, I had to use the date format of ‘DD-MM-RR’. This I found out by doing the following query:

SELECT value FROM v$nls_parameters WHERE parameter =‘NLS_DATE_FORMAT’;

The documentation says that Liquibase will parse dates in CSV files, but that seems to be false.

Though, it would be very useful if Liquibase parses dates, because else it won’t be possible to use the same CSV for Oracle as for PostgreSQL or any other supported database.

Bart Weber

Software Engineer

Procura B.V.

4

Ok, found out how to do this:

<loadData

encoding=“UTF-8”

file=“liquibase/initdata/changelog_SNAPSHOT_1/usr_specs.csv”

tableName=“usr_specs”>

By specifying the date fields, Liquibase parses the date values with the pattern as specified in the documentation: ‘YYYY-MM-DD' or 'hh:mm:ss’ or ‘YYYY-MM-DDThh:mm:ss’.

Now when I think about this matter, it is quite logical that you have to specify to Liquibase what kind of field it has to deal with. But when quick referencing the documentation it was not so quite clear to me. Please, could you add to the documentation an example of loadData with date fields like above?

Bart Weber
Software Engineer