I was faced with the need to access reports for some data that lives in Oracle and other data that is in the SQL Server 2000 database. For various reasons, they live on different sides of the firewall. Now we are engaged in export / import from the sql server to oracle, and I would like some tips on the best way ... The procedure should be fully automated and work at night to exclude the use of SQL developer tools. I also cannot establish a direct connection between the databases on our (oracle) side, as the firewall is in the way. Data must be transformed in the process from a stellar circuit to a de-normalized table, ready for communication.
I am thinking of writing a monstrous query for SQL Server (which I basically already have) that will denormalize and read data from SQL Server into a flat file, using SQL server equivalent to sqlplus, as a scheduled task, dump to a well-known location. then on the oracle side there is a cron job that copies the file and loads it using the sql loader and restores indexes, etc.
This is all doable, but very manual. Is there one or a combination of FOSS or standard oracle / SQL Server tools that could automate this for me? Irreducible complexity is querying on the one hand and building indexes on the other, but I really would like not to write the details of the CSV reset or the SQL script loader, just say that this view will come out on the CSV on the one hand, and on the other truncate and paste to this table from CSV and don’t worry about the names of the matching columns and all other Arcane sqlldr voodoo ...
best practics? thoughts? comments?
edit: I have about 50+ columns of all different types and lengths in my dataset, so I would rather not write how to create and map each individual column ...