This content has been marked as final. Show 4 replies
you can't put the commit transaction into a pasthrough statement.
Oracle Gateway starts the transaction, so you need to write the code in a way that allows the gateway to perform the commit. When doing a commit in a passhrough statement the gateway has no control over the committed data. Thus you get the error unless you start an autonomous transaction.
Why do you need to call the update in a pasthrough statement? Why not in a PL/SQl code block with a counter that commits after 100 rows?
I wrote another procedure for batch commit and tried to call that procedure from PL/SQL block which is used to update the remote table in SQL server DB but getting same error. If I do commit after each row then its fine otherwise its giving me same error.
Only way to accomplish this If I write a separate procedure one for SQL server and one for Oracle having no link between each other. Is there any suggestion.
I got it. As I mentioned that I create separate procedure for Oracle Table Update, there I am doing batch commit lets say for every 100 rows and called that procedure from PL/SQL block which is updating SQL Server DB. I am doing commit for every row in SQL server but doing batch commit for oracle. For oracle in the procedure I did AUTONOMOUS Transaction and it went through, no error. All the transactions were updated successfully on Oracle and SQL server side.
yes, autonomous transactions is in this case the only solution to solve the issue.
BTW, if your question is answered, please mark this tread as answered.