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

Oracle Transparent Geteways maybe help you.

but how? any steps to steps? i am a noob in this.

can Oracle Transparent Geteways connects to mysql? i can't get any info or driver on this too.

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

is there any other method i can be using instead of PL/SQL ?

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?

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.

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.

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.

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;

hi,

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

can explain line by line ?

thanks

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

the user wants it real time.

I think i might try the FUNCTION from Tomac.

Can I have more info on that?

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.

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 ?

let me try on it and i will get back to you. thanks

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.

I THANK  THE Java route IS THE BEST WAY, Good luck,

which do u mean java route?

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 .

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.