Solved

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

Posted on 2013-05-29
3
430 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle Query - Convert letters to numbers and display the difference 3 30
Creating Functions in phpMyAdmin 8 26
Database Design Dilemma 6 58
date show only hh:mm 2 25
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

790 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