?
Solved

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

Posted on 2013-05-29
3
Medium Priority
?
455 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 2000 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
In this article, we’ll look at how to deploy ProxySQL.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
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.
Suggested Courses

800 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