Read Oracle View Into MySQL

[curtiss]

I am jumping the gun a little bit on this, as I have not found out for sure that the database from which I'll be pulling information is an Oracle db. However, I have heard unconfirmed allusions that it is, so I will begin working from that assumption.

Here is my situation:
Very soon, I am going to have a very high priority project hit my desk, and I need to know the best way to go about working it out.

We subscribe to a database application that holds all of the information for our students, courses, registration, etc. As I said above, I believe the database is an Oracle DB. We are granted specific "views" through the DBMS.

I will need to figure out how to set up a script that can run a query on one of our views and pull that information to a local location (when I say local, I actually mean a location on our Web server, as I plan to do all of this through cron jobs, and will hopefully not be interfering with any of it manually).

From there, I need to reformat the information and push it into our MySQL (or possibly PostGreSQL, as we have both available, although I know absolutely nothing about PostGreSQL at this point in time) database management system.

From there, I am obviously capable of writing the appropriate PHP scripts to query our MySQL datbase and build the application. However, I don't even know where to begin when trying to pull the information from their remote database and pushing it into our SQL database.

I have spoken with a few of my peers on the subject (and will most likely get some example queries, etc. when the time comes), and have got two different responses when I asked how they are doing this.

1) Two of my peers are working on a server with .NET and MS SQL server. From one of those, I got the following information:
"We use a stored procedure in SQL and a scheduled Data Transformation Services (DTS) routine to pull the information from the Query Database every morning."

2) One of my other peers is running a LAMP server, much like we will be using for these purposes. However, they are doing the brunt of the work manually. They are pulling the information from the database manually each day, pushing it into Excel, manually reformatting and exporting to CSV, then pushing it into MySQL that way.

Obviously, if I can find an automated way to accomplish this, that's what I want to do.

Any links, articles or advice you can share with me would be greatly appreciated. Unfortunately, as of yet, I have not seen any of the queries or data that is being used with this application, so I cannot provide any more specific information at this time. However, as I learn more, I will try to share it (provided it's not confidential information, obviously).

Again, though, I'm really looking for examples, articles, advice, etc. on running automated scripts to pull information from an Oracle "view" and pushing it into a MySQL database. This will be a daily process (at least), so I obviously am not looking for anything that's aimed at a one-time "migration" from Oracle to MySQL.

Thank you.

[Allen]

i wish i had some advice for you!

[zoul]

if you possess the knowledge to retrieve the information from MySQL with PHP for the purposes of writing your application -- why not just use the same logic, in a slightly reverse way ... and retrieve the information from Oracle using the oracle functions in PHP and then dump the data into MySQL using a PHP script run with PHP CLI on a cron job at whatever regular interval you need (most likely only pulling 'delta' data from oracle each time ... rather then the full batch).

Working with Oracle, as far as retrieving data ... is extremely similar to working with MySQL ... they both use SQL, thought slightly different syntax's for more interesting things outside the scope of a basic select statement (which is all you need, from the sounds of it?) ... so, just retrieve the data from Oracle, populate an associative array, then pump the data into MySQL ... quick and easy done deal.

-- David

[curtiss]

Thanks for the advice, Zoul. It sounds like it will be pretty simple, in theory. Hopefully, I will be successful in practice.