Using a database link with heterogeneous services in APEX

Last weeks I ran into some issues with APEX, so let’s take some time to write something about them.

The most recent is about heterogeneous services (also referenced as HS). Here is the case:
We have an Oracle DB instance with some APEX applications and in one of those applications I need data from a MS SQL server database. Not a very good idea, but the set-up couldn’t be changed and both databases can handle it, so I gave it a try.

Our DBA installed heterogeneous services and created a database link between the two databases. Querying the necessary views was pretty easy (I only needed a couple of synonyms for ease of maintenance) and data came in faster than expected.

The next step was to query these views with APEX.
Because I created Oracle views on the views of MS SQL server, this was also very straightforward for APEX.

After a while however, there were issues in other applications and also the APEX builder itself raised errors while uploading files and importing applications. The following error returned:

ORA-02047: cannot join the distributed transaction in progress

, usually accompanied by some other ORA error codes. Not a very descriptive error message. A colleague pointed out to me that it has something to do with database links and heterogeneous services.

So, what is the problem?
APEX starts a new database session for my application. In this session the database link to the MS SQL server database is opened and data is queried. Since APEX constantly changes and re‑uses database sessions (this technique is called session pooling), the database session is released after a while and put back in the session pool until another application needs a new/another session. Because the database link in my session is only used for querying, there is no line of code that closes the database link so it remains open, even when the session is put back into the session pool. When another application takes this session from the pool, there might be issues with specific transactions, like setting a context (e.g. setting NLS parameters), because Oracle wants to perform this operation on both the Oracle database and the linked MS SQL server database. Because an NLS parameter is set when uploading a file, the end user receives the ORA-02047 error.

What can one do to prevent this from happening? There are several solutions for this issue:

1. Don’t use database links with heterogeneous services. This is the easiest solution but isn’t always possible.
2. Only access data over a DB link with HS in a package, function, procedure,… and close the DB link at the end of your block of code, which is not always possible either.
3. Close any open database link, or only the specific one, when you put your session back into the session pool.

For the third solution, something interesting is implemented in APEX 4.1.

At application level, you can define some PL/SQL code that should be executed when initializing or returning a session from/to the session pool.

You can find this section under Shared Components  >  Security Attributes. All the way at the bottom is the section ‘Database Session’:

In earlier versions of APEX, something similar was already available for VPD (Virtual Private Database) but this only allowed execution of code at session initialization. The APEX development team has now expanded this functionality for any PL/SQL in general, so not only VPD-related, and both at session initialization and returning.

For my issue, all I had to do was write some code to close the database link when it was still open. I created a function in my application database schema which returns the number of open database links with a specified name:

CREATE FUNCTION is_dblink_open(dblink  IN VARCHAR2)
RETURN INT
AS
  l_cnt   INT;
BEGIN
  SELECT COUNT(db_link) INTO l_cnt
  FROM v$dblink
  WHERE db_link = dblink;

  RETURN l_cnt;

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN 0;
END;

Before you create this function, don’t forget to grant access to v$dblink:

GRANT select ON v_$dblink TO my_app_schema;

Execute with SYS. Note the v_$ instead of v$! (more info: http://goo.gl/euxcs).

All that was left is to use this function in the Database Session Clean Up Code:

DECLARE
  l_dblink VARCHAR2(100) := 'MY_SQL_SRV_DBLINK';
BEGIN
  IF is_dblink_open(l_dblink) > 0 THEN
    DBMS_SESSION.close_database_link(l_dblink);
  END IF;
END;

Or if you want to be sure that all open database links are closed, you could use:

BEGIN
  FOR l_db_link IN ( SELECT db_link AS name FROM v$dblink ) LOOP
    DBMS_SESSION.close_database_link(l_db_link.name);
  END LOOP;
END;

Special thanks to my colleague Jan for pointing out to the HS problem and to Patrick Wolf for helping me on this issue through the Oracle APEX forum (read the full post here: http://goo.gl/vVoeb).

This way, I hope to help out other people too when they run into this issue.