Re: exporting table for load into oracle

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