On 04/05/2014 11:50 AM, pg_gg@mailinator.com wrote:
> Hi
>
> We are trying to replicate data from an Oracle database to PG 9.3 (tried
> 9.2 as well, but that doesn't really make a difference) on Windows
> 2008R2 64-bit using Oracle GoldenGate (OGG), but running into an issue
> with the ODBC driver psqlodbc-09.03.0210. The problem is that timestamp
> values coming from Oracle are truncated and mili and micro second data
> is lost. For example if data is '2014-04-05 11:12:13.123456000' what is
> insertedin the PG table is '2014-04-05 11:12:13', even when the field is
> defined as timestamp or timestamp (6). We logged the queries and it
> appears that the parameter that is prepared is already truncated to
> seconds only. We only see this behaviour with OGG, and not through other
> methods of inserting data using the same ODBC connection. However, the
> ODBC driver that ships with OGG, which is from DataDirect, doesn't have
> this problem and passes the timestamp values appropriately. However we
> cannot use that driver, as it doesn't handle unicode null character,
> i.e. 0x00, which the official PG ODBC driver somehow handles. So
> basically none of the drivers work properly, but each has it's own
> separate issue. We are trying to figure out where in the psqlodbc codes
> the preparation and conversion of parameters happen to find out why the
> timestamp values are truncated if the data is coming from OGG and not
> truncated from other mechansims. Any help with this regard is greatly
> appreciated.
Would seem this is coming from the Oracle side of things, given that you
say psqlodbc does the right thing when not receiving data from OGC. Also
that the parameter is already truncated. That indicates to me the Oracle
conversion is doing the pruning before passing it to psqlodbc.
My guess is the DataDirect driver is using a setting to do the right
thing. Confirmed I believe by this:
http://knowledgebase.datadirect.com/articles/Article/8788
>
> Thank you
> A.
>
--
Adrian Klaver
adrian.klaver@aklaver.com
