Toad World® Forums

Export SQL Script from Postgres and insert into Oracle

Hello,
I maintain two database one is in Postgres and second one Oracle.
In order to synchronize them I export data from Postgres as a SQL script and insert into Oracle.

My problem is that export into SQL from Postgres does not translate into a correct insert statement.
here is the example:
Postgres export:

INSERT INTO “TMSCENTRAL”.“stc_events”(“id_track”,“si_station_num”,“ev_notes”,“ev_year”,“dir_id”,“con_id”,“ev_ins_date”,“ev_rem_date”,“ev_receive_date”,“stype_id”) VALUES (5035,‘11s179r’,’’,1/1/2012,1,24,1/24/2012,1/26/2012,4/23/2012,6);

Correct Insert statement:
INSERT INTO “TMSCENTRAL”.“stc_events”
(“id_track”, “si_station_num”, “ev_notes”, “ev_year”, “dir_id”, “con_id”, “ev_ins_date”, “ev_rem_date”, “ev_receive_date”, “stype_id”)
VALUES (5035, ‘11s179r’, ‘’, TO_DATE(‘01-01-2012’,‘DD-MM-YYYY’), 1, 24, TO_DATE(‘24-01-2012’,‘DD-MM-YYYY’), TO_DATE(‘26-01-2012’,‘DD-MM-YYYY’), TO_DATE(‘23-04-2012’,‘DD-MM-YYYY’), 6);

The problems are created by dates.
Would be possible to address the problem in the future ?
I do realize that Postgres database is not your priority.
Regards,
Chris

I would handle your import a different way. Try these steps.

  1. Connect to Oracle, your target destination.
  2. Open up an Import Wizard from the Tools menu.
  3. Chooose “add Query” type.
  4. Select the Postgres connection and enter select statement for table data you want imported.
  5. Fill in the rest of the table info and let it run.

Using this method should handle the issue you are running into.

Debbie

Thank you for the info.
I will test your solution and let you know if it works.

Best,
Chris

Hi Debbie,
It did not work.
My Oracle table has auto number which created an error out.
Usually, when I insert to this table, I skip this column as Oracle takes of the auto number with a Sequence (column name: EV_ID_NUMBER).
It was not the case in the Wizard.

I tried to insert only two records.
See attached screen shots.
sc3.jpeg

Hi Debbie,
It did not work.
My Oracle table has auto number which created an error out.
Usually, when I insert to this table, I skip this column as Oracle takes of the auto number with a Sequence (column name: EV_ID_NUMBER).
It was not the case in the Wizard.

I tried to insert only two records.
See attached screen shots.

Hi Debbie,
It did not work.
My Oracle table has auto number which created an error out.
Usually, when I insert to this table, I skip this column as Oracle takes of the auto number with a Sequence (column name: EV_ID_NUMBER).
It was not the case in the Wizard.

I tried to insert only two records.
See attached screen shots.