Solved

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

Posted on 2013-05-29
3
405 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
Comment Utility
Did you manage to link your 2 DBs ?
If so, how  ?
If not, let you begin that way.
0
 

Author Comment

by:ohmetw
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

762 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now