Thread: exporting table for load into oracle

hi everybody

first, it is not that i'm migrating from postgresql to oracle, it's just
that we have a payroll system (we are a software development company)
that currently runs on postgresql, but the goal is that it runs on other
databases too (for our internal needs it runs on postgresql, and two of
our clients run in postgresql too), so now i need to test it on oracle

i've done a dump of our data, but we are having troubles with the date
fields, as pg_dump just dumps dates as 'yyyy-mm-dd', but when loading
into oracle, it just can't handle dates this way, it needs an explicit
mask (to_date....) when importing the sql dump

so the question is, there is a way to instruct pg_dump to dump dates
with the to_date function included?

i've searched in the pg_dump docs, but didn't find any wat to achieve my
goal

thanks in advance for your responses, and best wishes

miguel angel rojas aquino wrote:
> hi everybody
>
> first, it is not that i'm migrating from postgresql to oracle, it's
> just that we have a payroll system (we are a software development
> company) that currently runs on postgresql, but the goal is that it
> runs on other databases too (for our internal needs it runs on
> postgresql, and two of our clients run in postgresql too), so now i
> need to test it on oracle
>
> i've done a dump of our data, but we are having troubles with the date
> fields, as pg_dump just dumps dates as 'yyyy-mm-dd', but when loading
> into oracle, it just can't handle dates this way, it needs an explicit
> mask (to_date....) when importing the sql dump
>
> so the question is, there is a way to instruct pg_dump to dump dates
> with the to_date function included?

Not a direct answer, but how's about using CSV files instead of dumps?
You can instruct SQL Loader about the format of incoming dates via the
control file (ctl.)

BTW, I just did a migration in the other direction using CSV files.  I
must say I never cease to be impressed by the robustness of PostgreSQL.
Oracle exported dates like this: "9/14/2004 6:40:21 PM".  PG took 'em
right in with no special instructions, and produced the correct
timestamp.  Hats off to the PG developers!  Thanks for all your work.

--
Guy Rouillier