Here is some text from PeopleBooks that might be relevant:
Important! Never issue a SQL Commit or Rollback statement manually from within a SQLExec function. Let the Component Processor issue these SQL commands.
You might also try FieldChange as the commit problem might be event-specific. DDL and commits close open cursors, so I don't know if that is why, but RowInit might be a bad spot for a commit.
When I run into things that won't work like this, I sometimes create PL/SQL packages/functions to handle the scenario. For example, a function with an autonomous transaction can select data into variables, and then close the db link with an alter session inside the autonomous transaction without impacting the main transaction: https://docs.oracle.com/en/cloud/paas/atp-cloud/atpug/experienced-database-users.html#GUID-791E7112-07F7-46F0-BD81-777C8….
Another option I've used is JDBC direct from PeopleCode, which basically gives you a second session.
Hi - Is there any specific reason you would like to close the DB link?
If you can explain the reason we can recommend the different approach.
1. Commit is to test the POC. but it didnt help.
2. Event specific - DBlink is getting called from many events. Need to test it.
3. Tried SQL function - surprisingly. its not closing the DBLINK!!!
4. JDBC - will try it..
create or replace function GET_ROWS
for select '1V' as GRADE from DUAL@ABC
select '1' from DUAL;
execute immediate 'ALTER SESSION CLOSE DATABASE LINK ABC';
select GET_ROWS() from DUAL;
select * from V$DBLINK;
Scenario - DBlink from PS/Oracle -> DB2 Database via Oracle gateway.
if i dont explicitly close the DBlink, after the idle timeout setting (either on Oracle or on DB2 side), Oracle is throwing an Error - "ORA-28511: lost RPC connection to heterogeneous remote agent using SID" which stops pages.
Oracle says - dont timeout - which is not possible in my system.
Your function doesn't include "PRAGMA autonomous_transaction." This is critical. Try this before the JDBC route. It basically lets you set up and commit a transaction within a transaction. You may not even need a commit, based on the SQL I see above.
If you use autonomous_transaction, it is critical that you have an error handler that rolls back. Autonomous transactions have to commit or rollback before exiting. We can't just bubble up the error. I've heard Tom Kyte say he wished they didn't exist because they cause a lot of problems when done wrong. But done right, they are amazing. And there is just one simple rule: commit or rollback before exit. That's it.
I ended up creating Oracle Function or Procedure with 'PRAGMA autonomous_transaction' which will get the data and commit and close the DB link. I can see that it's not leaving the connection open.
Nice! Thanks for updating and letting us know how it turned out!