WbImport: Problem importing Timestamp in Oracle 12

[Franz Mayer]

Hi Thomas,

I cannot transfer timestamp fields from PostgreSQL to Oracle.

I exported a table from a PostgreSQL 9.5 database using WbExport:

WbExport -type=text
         -outputdir=C:/SVN/temp/export
         -sourceTable=T_BREAK_ARCHIVE;

This worked flawlessly and very fast!

Then I tried to import that file into a Oracle 12.1.

First I tried

WbImport -file=C:\SVN\temp\export\T_BREAK_ARCHIVE.txt
         -type=text
         -table=T_BREAK_ARCHIVE
         -stringDates=true
         -timestampFormat="yyyy-MM-dd HH:mm:ss";

But Workbench says: Unknown parameter(s): -stringDates

Then I tried it without stringDates, but then it throws an Exception: ORA-01843: not a valid month

I adjusted ValueConverter by adding Types.TIMESTAMP_WITH_TIMEZONE and then it worked.

See attached Patch - ValueConverter might miss some more SQL-Types.

Kind reagrds,
Franz

[Thomas Kellerer]

Thanks.

I'm currently reworking that anyway to properly support TIMESTAMP WITH TIME ZONE columns as the JDBC drivers pick up support for the new java.time classes to handle time zones (Postgres and Oracle for now).

That will include the ValueConverter to allow entering timestamp with a time zone.

The -stringDates parameter is for Excel files only. But thanks, I'll fix that.

Regards
Thomas

> --
> You received this message because you are subscribed to the Google Groups "SQL Workbench/J - DBMS independent SQL tool" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to sql-workbenc...@googlegroups.com <mailto:sql-workbenc...@googlegroups.com>.
> For more options, visit https://groups.google.com/d/optout.

[Franz Mayer]

Hi Thomas,

thank you. It is working now.

But in my case (Oracle 12.1), I need to change my workbench.settings file:
workbench.db.oracle.import.use.setnull=true

Otherwise I get following error:
Invalid column type: 2014

Log says:
2017-11-02 09:12:31 INFO  DataImporter.setTargetTable() Starting import for table SAPIENT_OWNER.T_BREAK_ARCHIVE
2017-11-02 09:12:31 ERROR DataImporter.processRow() Error importing row 1: Invalid column type: 2014 [SQL State=99999, DB Errorcode=17004]
2017-11-02 09:12:31 ERROR TextFileParser.processOneFile() Error sending line 1 Invalid column type: 2014 [SQL State=99999, DB Errorcode=17004]
java.sql.SQLException: Invalid column type: 2014
    at oracle.jdbc.driver.OracleStatement.getInternalType(OracleStatement.java:4188)
    at oracle.jdbc.driver.OraclePreparedStatement.setNullCritical(OraclePreparedStatement.java:4858)
    at oracle.jdbc.driver.OraclePreparedStatement.setNull(OraclePreparedStatement.java:4840)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.setNull(OraclePreparedStatementWrapper.java:1292)
    at workbench.db.compare.BatchedStatement.setNull(BatchedStatement.java:156)
    at workbench.db.importer.DataImporter.processRowData(DataImporter.java:1362)
    at workbench.db.importer.DataImporter.insertRow(DataImporter.java:1295)
    at workbench.db.importer.DataImporter.processRow(DataImporter.java:1052)
    at workbench.db.importer.TextFileParser.processOneFile(TextFileParser.java:743)
    at workbench.db.importer.AbstractImportFileParser.start(AbstractImportFileParser.java:435)
    at workbench.db.importer.DataImporter.startImport(DataImporter.java:759)
    at workbench.sql.wbcommands.WbImport.execute(WbImport.java:830)
    at workbench.sql.StatementRunner.runStatement(StatementRunner.java:574)
    at workbench.gui.sql.SqlPanel.displayResult(SqlPanel.java:3434)
    at workbench.gui.sql.SqlPanel.runStatement(SqlPanel.java:2170)
    at workbench.gui.sql.SqlPanel$4.run(SqlPanel.java:2108)

I guess the error occurs, because I have some NULL values in a column with type "TIMESTAMP(6) WITH LOCAL TIME ZONE".

Is it possible to change that setting by WbImport command? Or is it easily fixed in BatchedStatement.setNull method?

Thanks and kind regards,
Franz

[Thomas Kellerer]

> thank you. It is working now.

> But in my case (Oracle 12.1), I need to change my workbench.settings file:
> workbench.db.oracle.import.use.setnull=true
> Otherwise I get following error:
> Invalid column type: 2014

Hmm, that has been the default for several years now.

Did you change that manually to "false" previously?

Regards
Thomas

[Franz Mayer]

Sorry for pasting wrong setting. I changed it to

workbench.db.oracle.import.use.setnull=false