Import/Link Oracle Table

[cst172]

I have a PostgreSQL database that needs to get data from an Oracle database on a daily basis. Is there a way to import the data directly from Oracle into PostgreSQL? Is there a way to link to an Oracle table from within PostgreSQL? Any help would be greatly appreciated. Thank You.

 

[skiflyer]

I don't know about the direct links, but that sounds like the perfect place to cron up a perl, python or php script.

-Rob

 

[prairiedog99]

cst172, I was just wondering what approach you took to 'refreshing' data in postgre from oracle. Our presentation db is currently in oracle and relies on the 'snapshot' or 'materialized view' functionality - I'm trying to figure out what we would do if our presentation layer was postgre. Right now I'm thinking we may have to do some perl process that exports from our oracle staging db into txt files and then copies into 'temporary' postgre tables and then inserts, updates and deletes from the tables where we need the data.

Do you have any words of wisdom?

 

[Leland123]

Hi prairiedog99,

If the Oracle db and postgre db had the same table structure, and maybe even if they didn't, could you copy the Oracle data to a CVS comma delinited file and then copy the comma delimited file into postgres.

You might be able to automated all this using perl, html, and a web server, or you could probably do the smae using a perl script run daily via cron, etc.

http://www.postgresql.org/docs/7.4/static/sql-copy.html

Regards,

LelandJ

Leland F. Jackson, CPA
Software - Master (TM)

http://www.smvfp.com

Nothing Runs Like the Fox