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.

Forms 11G and DB function with result cache

Result cache… a very cool feature in the 11g database.
If you don’t know it, this is a must read: Pl/SQL function result cache in 11g

But also watch out with it in Forms 11g.
It seems there’s a bug.
When compiling a program unit that calls a function with result cache in forms, you’ll get a compile error, which look likes this:

Error 801 at line 7, column 2: internal error[*** ASSERT at file pdw1.c, line 4061; PSDGON missing. Can't get object number; XNSPC1PTEST__P[7,2]

This is logged on “My Oracle Support” as a bug.
It’s not a forms bug, but a PL/SQL Client bug.
And this “bad” version of the PL/SQL client is used in forms11g(or in general FMW11g).
It works with older and newer versions of the PL/SQL Client(Forms 10g + function result cache should work).
Hopefully there’s a patch soon, so that everybody can use result cache ;-)

ORA-01400 during refresh of MVIEW

 

When trying to refresh a materialized view based on a query that uses a mssql table (using the SqlServer Gateway), I run into the following error:

ORA-12008: error in materialized view refresh path
ORA-01400: cannot insert NULL into (%s)
ORA-02063: preceding line from MSSQLDB1
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2537
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2743
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2712

 

After some investigation I found this was caused by a “NOT NULL” constraint on the mview-table.

A small scenario:

STEP 1: Create a Materialized View

SQL> CREATE MATERIALIZED VIEW COUNTY_IS_NULL
2 REFRESH FORCE ON DEMAND
3 AS
4 SELECT a.agreementnum
5 ,a.county county_id
6 ,a.createddate agree_cre_date
7 FROM bmssa.EXU_AGREEMENTTABLE@mssqldb1 a
8 WHERE a.agreementnum = ' E00000001';

 

Materialized view created.

 

STEP 2: View Content of the Materialized View (County_Id is null!)

SQL>
SQL> SELECT * FROM county_is_null;

 

AGREEMENTN COUNTY_ID  AGREE_CRE
---------- ---------- ---------
E00000001 05-JUL-07
 

SQL>
SQL> SELECT NVL(county_id, 0) FROM county_is_null;

NVL(COUNTY
----------
0

 

 STEP 3: Refresh the Materialized View

SQL>
SQL> BEGIN
2 dbms_mview.refresh('COUNTY_IS_NULL');
3 END;
4 /
BEGIN
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-01400: cannot insert NULL into (%s)
ORA-02063: preceding line from MSSQLDB1
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2537
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2743
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2712
ORA-06512: at line 2

 

STEP 4: Disable the NOT NULL Constraint on COUNTY_ID

SQL>
SQL> SELECT constraint_name, search_condition FROM user_constraints c
WHERE c.table_name='COUNTY_IS_NULL';

CONSTRAINT_NAME SEARCH_CONDITION
------------------------------ -------------------------------------------------
SYS_C0029024 "AGREEMENTNUM" IS NOT NULL
SYS_C0029025 "COUNTY_ID" IS NOT NULL
SYS_C0029026 "AGREE_CRE_DATE" IS NOT NULL

 

SQL>
SQL> ALTER TABLE county_is_null MODIFY CONSTRAINT sys_c0029025 DISABLE;
Table altered.

 

STEP 5: Refresh the Materialized View

SQL>
SQL> BEGIN
2 dbms_mview.refresh('COUNTY_IS_NULL');
3 END;
4 /

 

PL/SQL procedure successfully completed.
SQL>

  

No Slide Zone – Tom Kyte

Today was the first time to attend a session in the ‘No Slide Zone’ which is one of the special programs presented at Oracle Open World. During these sessions no slides can be used, in other words this is no typical conference track.

The keywords for a ‘No Slide Zone’-session: ‘discussion, interaction, whiteboarding and more’.

The session I had chosen, ‘Tom Kyte presents 10 new features for Oracle Database 11g’ … and it was fun ;o)

Using different paper notes he mentioned and discussed the 11 different features which are now made available in 11g and explained which problems they solved.

Every time he mentioned a feature and explained the usage and gain of it, he threw away the card which was a really fun way of presenting.

After he had mentioned the 11 new features, the people could ask some questions … and to make sure questions were asked … books regarding 11g were given to the first 12 people asking a question. Everyone in the room had a question … this is a reminder for myself for future presentations and sessions.

So let’s stop talking and give you those features wright ;o):

  • Virtual Columns: ability to use functions instead of creating a new column for calculations => the outcome is beter cardinality which means a beter execution plan
  • Compress Data & MetaData
  • Finer Grained Dependencies => if the signature of a procedure doesn’t change (the input/output parameters), the procedure won’t be invalidated. When columns are added or dropped from tables, no invalidation occurs, if specs are added to packages, no invalidation occurs
  • Compress data for transactional data not only for read-only data => compressioning will work for conventional paths as well (CRUD-methods) which will save space and audit retrieval will be faster
  • Flash Data Archive: enew background process is used, flashback-process which will use the flashback query for data retrieval
  • Partioning is extended and now enables: partionining by virtual columpns; reference-partioning which will give the ability to partition on master-data from a child-table. The child-table doesn’t need to be denormalized anymore, so the attribute of the master-table is available in the detail-table. The last partioning-feature added is interval-partioning which will give you the ability to performed sparsed partioning
  • Encrypted tablespace => store data on block, in the index in clear data and afterwards when the data is written in clear text, the tablespace block will be encrypted
  • Cache more stuff => statement results can be cached; server-result cache which means just-in-time materialized views are used (use the buffer cache or shared pool); pl/Sql function cache which will mark the function as result cache
  • Stand-by database => for logical standby’s datatype support has been extended and for physical standby’s, active standy is now fully supported
  • Real Application Testing => catch the workload of your db 10g release 1 db and run the same workload on your db release 2 database using Real Application Testing

It was an amazing session and these are amazing features which will improve the DB’s performance and maybe, somewhere in the very, very far future DBA’s aren’t needed anymore ;o)