7 Replies Latest reply on Jul 22, 2020 11:12 PM by Jim Marion

    Close DBlink in PeopleCode

    user12921829

      Hi, Anyone ever tried closing a DBlink explicitly in component/page PeopleCode?

      i am using the oracle recommended command ALTER SESSION CLOSE DATABASE LINK <dblinkname>. we need to issue a commit/rollback before this command.

      Even after trying both, we are getting DBlink in use error.

       

      sample rowinit code.

      SQLExec("SELECT 'X' FROM DUAL@ABC", &Test);

      Local number &Num = 0;

      SQLExec("SELECT count(*) FROM V$DBLINK ", &Num);

      MessageBox(0, "", 0, 0, "DB Link Executed-" | &Num);

      SQLExec("commit");

      If &Num > 0 Then

         SQLExec("ALTER SESSION CLOSE DATABASE LINK ABC");

         MessageBox(0, "", 0, 0, "End");

      End-If

       

      Any recommendations?

        • 1. Re: Close DBlink in PeopleCode
          Jim Marion

          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.

          • 2. Re: Close DBlink in PeopleCode
            Velu Angusamy

            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.

             

            -Velu

            • 3. Re: Close DBlink in PeopleCode
              user12921829

              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..

               

              Thanks.

               

              create or replace function GET_ROWS

                return sys_refcursor

              as 

                L_RC sys_refcursor;

              begin

                open L_RC

                 for select '1V' as GRADE from DUAL@ABC

                      union

                      select '1' from DUAL;

                return L_RC;

                commit;

              execute immediate 'ALTER SESSION CLOSE DATABASE LINK ABC';

              end;

              select GET_ROWS() from DUAL;

              select * from V$DBLINK;

              • 4. Re: Close DBlink in PeopleCode
                user12921829

                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.

                • 5. Re: Close DBlink in PeopleCode
                  Jim Marion

                  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.

                  • 6. Re: Close DBlink in PeopleCode
                    user12921829

                    Thanks Jim.

                    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.

                    • 7. Re: Close DBlink in PeopleCode
                      Jim Marion

                      Nice! Thanks for updating and letting us know how it turned out!