Solved

need to update mySQL table with data from Oracle table.  need code in mySQL procedure or script

Posted on 2013-05-29
3
425 Views
Last Modified: 2013-07-09
I need to run a scheduled job to update data in a mySQL database, and must query an ORACLE database to get values for the update.

I am very comfortable coding pl/SQL and would like to just create a procedure in mySQL to query the ORACLE table....basically in this form

UPDATE mySQLdb.table
SET <mySQLdb.table.fieldtoupdate> = (
      SELECT <ORACLEdb.table.fieldtoget>
      FROM  <ORACLEdb.table>
      WHERE <ORACLEdb.table.id> = <mySQLdb.table.id>)
WHERE mySQLdb.table.fieldtoupdate is NULL;

can I connect to another db from within a mySQL procedure, or do I need to use some different method.  Looking for some expert guidance and examples if possible :-)
*Note, I know it's possible to create a database link in ORACLE but there's a lot of red tape for me to embed code in ORACLE as opposed to just querying from mySQL or external
0
Comment
Question by:ohmetw
3 Comments
 
LVL 7

Expert Comment

by:Docteur_Z
ID: 39204401
Did you manage to link your 2 DBs ?
If so, how  ?
If not, let you begin that way.
0
 

Author Comment

by:ohmetw
ID: 39204556
ok, I do not know how to do that, and keep in mind I can not embed script in the ORACLE side 'database links'  I can only operate from the mySQL side
0
 
LVL 29

Accepted Solution

by:
MikeOM_DBA earned 500 total points
ID: 39205986
SQL Server -- or -- mySQL?
On WinDoze?

1) Install Oracle client on your mySQL server.
2) Create ODBC data source
3) Code program or use some utility to query oracle table and update your mwSQL db.

For SQL Server use SSIS.

Here are some useful links:
http://help.jitterbit.com/entries/20964761-How-should-I-connect-to-Oracle-using-ODBC
http://www.cloveretl.com/  <== Free community edition!
:p
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
exp/imp 25 73
PL SQL Search Across Columns 4 35
UPDATE query not working in mysqli php 8 50
Creating Functions in phpMyAdmin 8 13
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question