• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1349
  • Last Modified:

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

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
joycelsq
Asked:
joycelsq
  • 10
  • 8
  • 4
1 Solution
 
Tomac_YaoCommented:
Oracle Transparent Geteways maybe help you.
0
 
joycelsqAuthor Commented:
but how? any steps to steps? i am a noob in this.
0
 
joycelsqAuthor Commented:
can Oracle Transparent Geteways connects to mysql? i can't get any info or driver on this too.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
mrjoltcolaCommented:
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
 
joycelsqAuthor Commented:
is there any other method i can be using instead of PL/SQL ?
0
 
mrjoltcolaCommented:
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
 
joycelsqAuthor Commented:
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
 
mrjoltcolaCommented:
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
 
joycelsqAuthor Commented:
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
 
Tomac_YaoCommented:
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
 
joycelsqAuthor Commented:
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
 
mrjoltcolaCommented:
>>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
 
joycelsqAuthor Commented:
the user wants it real time.

I think i might try the FUNCTION from Tomac.

Can I have more info on that?
0
 
mrjoltcolaCommented:
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
 
joycelsqAuthor Commented:
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
 
mrjoltcolaCommented:
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
 
joycelsqAuthor Commented:
let me try on it and i will get back to you. thanks
0
 
mrjoltcolaCommented:
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
 
Tomac_YaoCommented:
I THANK  THE Java route IS THE BEST WAY, Good luck,
0
 
joycelsqAuthor Commented:
which do u mean java route?
0
 
Tomac_YaoCommented:
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
 
mrjoltcolaCommented:
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
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.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

  • 10
  • 8
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now