BI Publisher caching results problem

If you are experiencing behavior that indicates that your report is being cached and you can’t refresh it then it could be that a reporting setting is indicating that it can cache your result set.

To check this setting go to “Edit Report” and look at the bottom in the list of options. You will find a check-box with the label “Enable document cache”. Unchecking this check-box will probably solve your problem.

By default, all Oracle Enterprise Repository reports uses Document Caching to reduce database roundtrips on the generation of report data. While the data is cached, the rendered report may not always show the most recent activities in Oracle Enterprise Repository. Setting the cache expiration changes depending on how frequently BI Publisher needs to refresh the data for the second and subsequent rendering of a report.

It is possible to change the default cache expiration (30 minutes) under Admin -> Server configuration -> Cache Section.

More information can be found in the BI Publisher.

New Patch available for ApEx 4.0

ApEx 4.0.1 is available for download, if you are running ApEx 4.0 then I suggest you upgrade as soon as possible.

- If you are already running ApEx 4.0 then download the patch from Oracle support, look for patch nr 9976149.
– If you are still running and older version then 4.0 then download ApEx from apex.oracle.com.

The new version nr of APEX is 4.0.1.00.03.

BI Publisher showing TOPLINK-7001 error on Scheduled Reports

When you have the next error in BI Publisher Scheduled Reports:


Exception [TOPLINK-7001] oracle.toplink.exceptions.ValidationExceptionException Description: You must login to the ServerSession before acquiring ClientSessions.

You might want to check if the tablespace containing the BI Publisher schema has enough space. There is no procedure to physically remove scheduled reports once they have been run. You can manually delete records from the database (bipub schema) but don’t forget to reclaim the lob size. If you delete records containing LOB’s the size doesn’t automatically free up.

A way to do this manually by using the next command:
ALTER TABLE “table” MODIFY LOB (“column”) (SHRINK SPACE);
* command available from 10gR2

Delete history from the table ‘XMLP_SCHED_OUTPUT’ and then run the above command for all the lob columns: “XML_DATA”, “MESSAGE” ,”STATUS_DETAIL” and “DOCUMENT_DATA” .

Once you have done that, restart BI Publisher using Enterprise Manager and the error will be gone.

ApEx 4.0: ORA-001821 randomly showing up

This week, I had a problem with ApEx 4.0 where ORA-001821 was randomly showing up, not only in the applications but also in the ApEx Builder.

ORA-1821

The problem was that we queried a table across a database link but to a database version prior to 10gR1. Please note that other applications will also suffer from this one application where you used this database link.

Oracle describes the problem as:

In Application Express 4.0, the internal applications were modified to have an application date format of ‘DS’. The ‘DS’ date format was first introduced in database version 10gR1.

With the application date format of ‘DS’, each page view has the database session parameter NLS_DATE_FORMAT set to ‘DS’ by the APEX engine. Then, when a query is performed against the remote database (9iR2 or earlier), the remote database is unable to handle the request and says that ‘DS’ is an invalid value for NLS_DATE_FORMAT.

You can already download a patch that solves the problem, in Oracle support search for “note 1155453.1 – ORA-1821 When Querying a Table Across A Database Link After 4.0 Upgrade

ApEx 4.0: Map not showing data points correctly

In ApEx 4.0, when working with a map region (World without Greenland) (Map world/world_wo_gr.amap) you might experience that not all data points are correct.
For example: when you work with the map “World without Greenland” and you choose a query with Series Type of Bubble, Switzerland is positioned in America while Spain is in the ocean.

ApEx 4.0 data points not showing correctly

Oracle recognizes the bug (9950531) and will supply a fix in upcoming patch 4.0.1.
Meanwhile as a workaround the next action is suggested: setting “Series Type” to “Map”, and the countries will be correctly labeled

APEX 4.0: Export feedback, take it to the next level

Team development is one of the new features of APEX 4.0, one option to use with this feature is Feedback. In short, Feedback allows users to easily report “bugs” for an application. (Like we APEX developers can program bugs… NOT!).

There are already 2 blog posts that cover the way you can add a feedback page to your application and how to access your feedback as developer.

This blog post will describe how you would export your feedback from a production system to the development system where you as developer try to make/solve things. After changes are made you want to inform your users what has been modified…

1. Setting your environment variables
The first thing you have to check before you want to export/import feedback, is the name of your workspace. If you use the same name for a workspace in both environments then we will have to distinct the development environment from the production environment. If you don’t do this, the export/import feature for feedback will not work. You can distinct the environments by altering the Feedback Synchronization Source Identifier, this will default be the name of your workspace. Changing the identifier can be done by using the Internal workspace -> Manage Workspace -> Workspace Details -> Edit Workspace Information.

Continue reading

ApEx 4.0: Date Picker Item Minimumdate and Maximumdate

One of the big improvements of ApEx 4.0 in my opinion is the rebirth of the date picker Item. The old version of the date picker was a popup that opened a calender, it was ok but a lot of developers already used something more sexy like the jQuery Ui Datepicker. This required some custom development and was’t out of the box functionality.

In ApEx 4.0 the date picker got a new look and feel based on the jQuery Datepicker. It opens a lot faster and is overall more simple to use. You don’t have to create a validation anymore to check if the user has specified a valid date, this is done automatically.

Some new properties are available for the date picker:

Continue reading