Problem solve Get help with specific problems with your technologies, process and projects.

Reflecting Oracle database changes in SQL Server

Whenever data manipulation occurs through a frontend application, I want manipulated data in the Oracle database to be reflected in a SQL Server database. How this can be achieved?

First, I will write a trigger, through which I will write a stored procedure that will establish a connectivity to servlet. If the connectivity is established, I can write a servlet program which will make a connection with SQL Server and perform the data manipulation in that server. Is this the right method? How else can this be achieved?

You have a couple of things at your disposal to help you out. You can write such a servlet as you have suggested.

Another option is to use Transparent Gateways. You can install the Transparent Gateway for SQL Server. Then, using a SQL Server ODBC driver on your Oracle server, you can create a database link to the SQL Server instance. This then tricks Oracle into thinking SQL Server is another Oracle database. You can then write a trigger which can inserts into this remote database just like it would insert into a remote Oracle instance.

Another method uses Oracle's Java Stored Procedures. With Java Stored Procedures, you can use JDBC to connect to a remote database. Have a trigger fire when data is modified in the Oracle database which calls your Java Stored Procedure to insert into a different database through JDBC.

For More Information

Dig Deeper on Oracle database export, import and migration

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.