Solved

how can i connect to mysql db using pl/sql in my Oracle db environment which I am using toad to manage ?

Posted on 2009-05-20
22
968 Views
Last Modified: 2012-05-07
how can i connect to mysql db using pl/sql in my Oracle db environment which I am using toad to manage ?

It doesnt eventually have to me pl/sql but I cant think of anything can use to program a real time trigger from a database change to directly trigger a code to push in data to other mysql database table.

any idea?
0
Comment
Question by:joycelsq
  • 10
  • 8
  • 4
22 Comments
 
LVL 1

Expert Comment

by:Tomac_Yao
Comment Utility
Oracle Transparent Geteways maybe help you.
0
 

Author Comment

by:joycelsq
Comment Utility
but how? any steps to steps? i am a noob in this.
0
 

Author Comment

by:joycelsq
Comment Utility
can Oracle Transparent Geteways connects to mysql? i can't get any info or driver on this too.
0
 
LVL 40

Expert Comment

by:mrjoltcola
Comment Utility
There isn't a Transparent Gateway for MySQL, however, you can use the Generic ODBC Connectivity Agent. You need to use MySQL's ODBC driver in combination with Oracle, so first step would be to create an ODBC DSN on the Oracle server prior to proceeding with setting up the Oracle agent.

Then refer to this:

http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14232/admin.htm#i1007209
0
 

Author Comment

by:joycelsq
Comment Utility
is there any other method i can be using instead of PL/SQL ?
0
 
LVL 40

Expert Comment

by:mrjoltcola
Comment Utility
This has nothing to do with PL/SQL. The gateway is an Oracle server integrated option. If you want to use a trigger to update a remote table, this is your option, but there are other ways to accomplish it. You could use various other Oracle APIs, such as queues, to signal to an external program to update the MySQL database. You could use a Java stored procedure to call some MySQL code, or you could use the Oracle pipe facility to execute an external program from PL/SQL.

You could also store the Oracle table as an external table, if it works ok for your application, so then it is merely a text file update, and MySQL can read from the same text file.

The approach depends on the requirements, and with Oracle there are probably 10 ways to do it. What sort of table is this and what is the update process? Is it something you have control over to change?

What about simply using an external program to simultaneously execute the update to both databases?
0
 

Author Comment

by:joycelsq
Comment Utility
hi,

we have a Oracle DB which is the master db. It is a customer info database. Once a new customer has been created, an activation will need to pass data to a external MySQL database that will be the provisioning purposes.

Besides activation, all sort of transaction such as suspension, termination, and transitioning of plans that will require data to pass to the external db and update the Status for example.

But all this need real time updating.
0
 
LVL 40

Expert Comment

by:mrjoltcola
Comment Utility
Is the MySQL database / application too complex to port to Oracle? Consolidating on one database would simply the process, but I assume this is not an option?

I think you should pursue setting up the trigger / remote query option, as originally suggested, this will give you the easiest gateway into MySQL.

Otherwise, use some sort of program (Perl, Java) that can query the Oracle database for customer record updates and perform those changes on MySQL. The program can run on a schedule, say every 5 minutes, that would be near realtime.


0
 

Author Comment

by:joycelsq
Comment Utility
consolidating the db for the mean while it is not the solution for now. so this is not an option.

we are using the near real time solution now by having cron job to trigger a batch program.

therefore, for now we need a really real time solution for this activation. is there any other method instead of gateway? else, i have no choice.

thanks.
0
 
LVL 1

Expert Comment

by:Tomac_Yao
Comment Utility
1) desgin a web server for pass data to mysql.
2) create a trigger on custom table, access web server.

the fllow is a sample.

CREATE OR REPLACE FUNCTION call_sayHello (username  IN  VARCHAR2)
  RETURN VARCHAR2
AS
  l_service  UTL_DBWS.service;
  l_call     UTL_DBWS.call;
  l_result   ANYDATA;

  l_wsdl_url        VARCHAR2(1024);
  l_service_name    VARCHAR2(200);
  l_operation_name  VARCHAR2(200);
  l_input_params    UTL_DBWS.anydata_list;
BEGIN
  l_wsdl_url       := 'http://localhost:8888/hellows/helloService?WSDL';
  l_service_name   := 'helloService';
  l_operation_name := 'sayHello';

  l_service := UTL_DBWS.create_service (
    wsdl_document_location => URIFACTORY.getURI(l_wsdl_url),
    service_name           => l_service_name);

  l_call := UTL_DBWS.create_call (
    service_handle => l_service,
    port_name      => NULL,
    operation_name => l_operation_name);

  l_input_params(1) := ANYDATA.ConvertVarchar2(username);

  l_result := UTL_DBWS.invoke (
    call_handle  => l_call,
    input_params => l_input_params);

  UTL_DBWS.release_call (call_handle => l_call);
  UTL_DBWS.release_service (service_handle => l_service);

  RETURN ANYDATA.AccessVarchar2(l_result);
EXCEPTION
  WHEN OTHERS THEN
    RETURN NULL;
END call_sayHello;
0
 

Author Comment

by:joycelsq
Comment Utility
hi,

what is UTL_DBWS.service;? i try to create it directly in toad but i get error..

can explain line by line ?

thanks
0
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.

 
LVL 40

Expert Comment

by:mrjoltcola
Comment Utility
>>therefore, for now we need a really real time solution for this activation. is there any other method instead of gateway? else, i have no choice.

I have already explained multiple options. It seems you are not actually willing to try or discuss them.


>>we are using the near real time solution now by having cron job to trigger a batch program.

Why isn't this approach good enough? If both Oracle and MySQL are updated simultaneously from the batch program, it is near real-time.
0
 

Author Comment

by:joycelsq
Comment Utility
the user wants it real time.

I think i might try the FUNCTION from Tomac.

Can I have more info on that?
0
 
LVL 40

Expert Comment

by:mrjoltcola
Comment Utility
Be advised, taking the web service option above imposes an additional requirement on your infrastructure. You must add a web server with SOAP web service to your software stack. This is very heavy weight just to accomplish the task that could be accomplished without it. You should be able to write a similar stored procedure that calls directly into MySQL using MySQL JDBC libs, or by calling an external program, without the addition of a web server, SOAP layer, etc.

0
 

Author Comment

by:joycelsq
Comment Utility
thanks for the advice mrjoltcola. You are right on it.

how about i am using a Pl/sql to call a java? do you have example on pl/sql call java ?
0
 
LVL 40

Accepted Solution

by:
mrjoltcola earned 500 total points
Comment Utility
I would try the Java route.

Here is an example:

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/subprograms.htm#i17145

And here is the Oracle Java Dev Guide

http://download.oracle.com/docs/cd/B19306_01/java.102/b14187/toc.htm


You will need to obtain the mysql jdbc jar file and register it with Oracle JVM so you can call directly to mysql.
0
 

Author Comment

by:joycelsq
Comment Utility
let me try on it and i will get back to you. thanks
0
 
LVL 40

Expert Comment

by:mrjoltcola
Comment Utility
Good luck, sorry I could not write an actual sample but I have no MySQL in my Oracle environments right now and didn't have time to install for the purpose of this, but I am willing to help as much as possible.
0
 
LVL 1

Expert Comment

by:Tomac_Yao
Comment Utility
I THANK  THE Java route IS THE BEST WAY, Good luck,
0
 

Author Comment

by:joycelsq
Comment Utility
which do u mean java route?
0
 
LVL 1

Expert Comment

by:Tomac_Yao
Comment Utility
you can register mysql jdbc jar file to oracle jvm, so you can create a trigger using java, to pass data to mysql database.

than this is a real time solution .

0
 
LVL 40

Expert Comment

by:mrjoltcola
Comment Utility
joycelsq: I recommend that you start simple.

1) Follow the Oracle Java developer's guide and many samples online to implement a simple Java stored procedure in Oracle. It can do anything simple, just make it work. Follow the links provided above.

2) Write a standalone (outside of Oracle) Java program to update a table in MySQL


After you have done both of those, you have the building blocks you need to mix to perform the real-time update. You take the required dependencies from the standalone Java program (must probably it will simple by the mysql jdbc jar file) and register it in the jvm. Start small, build up. Ask questions here as you go.


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.

Join & Write a Comment

Suggested Solutions

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
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.

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

9 Experts available now in Live!

Get 1:1 Help Now