View Oracle database data via Mysql

Asked

Viewed 1,451 times

3

I need to know how to have the data of an Oracle database in my Mysql database, a kind of view.

I do not wish to make insert, update nor delete in the Oracle bank, I need only one select of this data, to have them in my Mysql database.

I found two paths, which I’m not sure they work:

1) I have Oracle ODBC installed on my server that has Mysql, but I don’t know how to make Mysql "see" this my ODBC to create a link between the databases.

2) I have found what seems to be a good way to solve the problem: http://ftp.nchu.edu.tw/MySQL/tech-resources/articles/dbixmyserver.html

The tutorial refers to the use of a proxy server, which becomes the intermediary between Mysql database and other banks. This intermediary is the one who allows a database to give a SELECT in the other database.

I believe that the solution in item 2 really works, however, I have no knowledge in Perl development and do not know how to make the required proxy configuration to work.

mysql oracle

  • Your question makes no sense, an SGBD has no way to communicate with another SGBD. The closest you are to what you intend to do is to make an exact copy from one bank to another, even if they are from different Dbms.

  • No, I don’t want a static copy. I want a table or view that allows me to see in Mysql the same Oracle data, even if it is changed.

  • A view is a form of data visualization within a DBMS, it does not work to view data from another DBMS. Why you want to view Oracle data in Mysql, after all?

  • I have the data I need for a web application in an Oracle database (and the data changes in this database). However, my web application uses Mysql.

  • Then you need to create two connections - a Mysql and an Oracle -, pull the data from both databases and work with them in your application.

  • So the fact is that I want my application to query only in Mysql. That’s why I need the data there.

  • Then create a routine to replicate Oracle data in Mysql. But nothing you want will be possible if in no time you connect with Oracle.

  • So, from Oracle to Oracle there are ODBC connections. I would like to do the same thing, but from Oracle to Mysql.

  • 2

    No, there is not the same thing from Oracle to Mysql.

  • The only way would be to create a Trigger on the same Oracle?

  • If the application is "Web" (an application communicating with a server, or a server communicating with another server) the only way I see of doing such a process is to create a "Webservice" (for example a json) that would migrate the data as needed, the Webservice structure will have to work equivalent to a MODEL and use HTTP requests to receive commands.

  • Yeah, but from Oracle to Mysql I can do it. That is, I can through Oracle see tables I have in Mysql through the Mysql ODBC Connector.

  • @Rodrigorigotti what he wants exists yes, is a resource called Database Link (Oracle) or Linked Server (SQL Server). For web development it doesn’t really make much sense to have more than one DB chatting with another, but in other case it is completely possible.

  • @John one option could be the Federated engine, however will not suit you as it only works between mysql-mysql

  • @gmsantos cool to know this, I didn’t really know. :)

  • Maybe a communication via XML the BD Oracle saves an xml that Mysql reads , It remains to know how dynamic is the source BD , direct link I confess that never heard.

Show 11 more comments

3 answers

3

I only used it backwards, making the oracle access the tables in mysql through a DBLINK [https://hs2n.wordpress.com/2012/04/oracle-create-database-link-to-mysql-database/]

Wouldn’t it be better for you to do this? According to what changes in oracle, you update inside oracle in mysql.

I never configured, but to access a dblink you will access like this: tableName DbLink, in case if you have a dblink configured in oracle for mysql, you can already access the tables.

Accessing: [https://community.oracle.com/thread/2273134]

0

You can use Federated Tables via Perl Dbix::Myserver proxy

The link you sent (which I cited as a reference) coincidentally is the best reference there is to do this, so the best option is to study it yourself

-1

$ora_user = "usuario";
$ora_senha = "senha";
$ora_bd = "(DESCRIPTION=
      (ADDRESS_LIST=
        (ADDRESS=(PROTOCOL=TCP)
          (HOST=192.168.xxx.xxx)(PORT=1521)
        )
      )
      (CONNECT_DATA=(SERVICE_NAME=yyyyy))
 )";

if ($ora_conexao = oci_connect($ora_user,$ora_senha, $ora_bd)){
    echo 'Conexão bem sucedida';
}else{
    echo 'Conexão falhada';
}

select

$stid = oci_parse($ora_conexao, 'SELECT A.MATRICULA, 
A.NOME_FUNCIONARIO, A.APELIDO FROM VIEW_X A');
oci_execute($stid);


while ($row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_NULLS)) {
    //echo ....
}

Browser other questions tagged mysql oracle

You are not signed in. Login or sign up in order to post.