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

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
ohmetwAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
Docteur_ZCommented:
Did you manage to link your 2 DBs ?
If so, how  ?
If not, let you begin that way.
0
 
ohmetwAuthor Commented:
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
 
MikeOM_DBACommented:
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

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.