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:
- Don’t use database links with heterogeneous services. This is the easiest solution but isn’t always possible.
- 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.
- 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.
Hey Bart,
thanks for that Posting. I spend a lot of time to find a solution for this problem. Now i have one and it seems to work good.
Thanks again.
Daniel
Awesome Bart, it works like a charm!
Thanks
Sudhakar