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
- Dozens more answers to tough Oracle questions from Brian Peasland are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.