Export to CSV Date format

[MichalisDBA]

Hello.

When i export from Oracle to CSV and the Oracle table that has a column date with the value ie. 2023-02-10, then at the sink CSV is formatted as, 2023-02-10 00:00:00.0

Is there a way or some parameter to specify the date to be a specific format at the sink table? Like only YYYY-MM-DD OR YYYY-MM-DD HH24:MI:SS without adding SELECT TO_CHAR(COLUMN, 'YYYY-MM-DD HH24:MI:SS') because we want to keep the SELECT *

Thank you.

[MichalisDBA]

I have forked the project and changed from the OracleManager.java

stmt.executeUpdate("ALTER SESSION SET NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SS.FF' ");

to this

stmt.executeUpdate("ALTER SESSION SET NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SS' ");

I compiled it but when i run replicadb again and export to a CSV file it still keeps the .FF part.

I tried with sqlplus to connect alter session and query the table and the results are ok.

Without ALTER SESSION SET NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SS'

ORDER_CODE
--------------------
ORDER_TIMESTAMP
---------------------------------------------------------------------------
351DCT08565501
09/01/23 16:18:30,006000

With ALTER SESSION SET NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SS'

ORDER_CODE
--------------------
ORDER_TIMESTAMP
---------------------------------------------------------------------------
351DCT08565501
2023-01-09 16:18:30

But when i run replicadb with the modified forked i made stmt.executeUpdate("ALTER SESSION SET NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SS' "); the result inside .csv file is:

ORDER_CODE,ORDER_TIMESTAMP
351DCT08565501,2023-01-09 16:18:30.006

I created a .config file that does a SELECT * FROM V$NLS_PARAMETERS and at the output .csv file the nls_parameters are correct.

PARAMETER,VALUE
NLS_LANGUAGE,AMERICAN
NLS_TERRITORY,AMERICA
NLS_CURRENCY,$
NLS_ISO_CURRENCY,AMERICA
NLS_NUMERIC_CHARACTERS,".,"
NLS_CALENDAR,GREGORIAN
NLS_DATE_FORMAT,YYYY-MM-DD HH24:MI:SS
NLS_DATE_LANGUAGE,AMERICAN
NLS_CHARACTERSET,EL8ISO8859P7
NLS_SORT,BINARY
NLS_TIME_FORMAT,HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT,YYYY-MM-DD HH24:MI:SS
NLS_TIME_TZ_FORMAT,HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT,YYYY-MM-DD HH24:MI:SS
NLS_DUAL_CURRENCY,$
NLS_NCHAR_CHARACTERSET,AL16UTF16
NLS_COMP,BINARY
NLS_LENGTH_SEMANTICS,BYTE
NLS_NCHAR_CONV_EXCP,FALSE

Any idea why this is happening?

[osalvador]

HI @MichalisDBA ,

At the moment the only solution is to add SELECT TO_CHAR(COLUMN, 'YYYYY-MM-DD HH24:MI:SS') to the custom query.

Anyway, in your fork it should work for you. Pass me the following data so I can check it:

1. Source table DDL
2. ReplicaDB configuration options-file.
3. Source Oracle DB version

Regards!

[MichalisDBA]

I ended up using SELECT TO_CHAR as you said.

What i did was to clone the project and modify this function from OracleManager.java:

    public void oracleAlterSession(Boolean directRead) throws SQLException {
        // Specific Oracle Alter sessions for reading data
        Statement stmt = this.getConnection().createStatement();
        stmt.executeUpdate("ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '.,'");
        stmt.executeUpdate("ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS' ");
        stmt.executeUpdate("ALTER SESSION SET NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SS.FF' ");
        stmt.executeUpdate("ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT='YYYY-MM-DD HH24:MI:SS.FF TZH:TZM' ");
        stmt.executeUpdate("ALTER SESSION ENABLE PARALLEL DML");

        // The recyclebin is available since version 10
        DatabaseMetaData meta = this.getConnection().getMetaData();
        if ( meta.getDatabaseMajorVersion() >= 10 ) stmt.executeUpdate("ALTER SESSION SET recyclebin = OFF");

        if (directRead) stmt.executeUpdate("ALTER SESSION SET \"_serial_direct_read\"=true ");

        stmt.close();
    }

to this

    public void oracleAlterSession(Boolean directRead) throws SQLException {
        // Specific Oracle Alter sessions for reading data
        Statement stmt = this.getConnection().createStatement();
        stmt.executeUpdate("ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '.,'");
        stmt.executeUpdate("ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS' ");
        stmt.executeUpdate("ALTER SESSION SET NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SS' ");
        stmt.executeUpdate("ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT='YYYY-MM-DD HH24:MI:SS' ");
        stmt.executeUpdate("ALTER SESSION ENABLE PARALLEL DML");

        // The recyclebin is available since version 10
        DatabaseMetaData meta = this.getConnection().getMetaData();
        if ( meta.getDatabaseMajorVersion() >= 10 ) stmt.executeUpdate("ALTER SESSION SET recyclebin = OFF");

        if (directRead) stmt.executeUpdate("ALTER SESSION SET \"_serial_direct_read\"=true ");

        stmt.close();
    }

Removed any traces of .FF

The source table is an Oracle Database and the sink is a CSV file. Default settings.