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.

Oracle Discoverer to Oracle BI Enterprise Edition

Tuesday I went to a session regarding migrating Oracle Discoverer to OBIEE which was a very interesting resume on do’s and don’ts when migrating.

First of all only metadata is migrated, not the reports itself. When you think about that is a logical approach as well because you don’t want all your front-end reports and logic to be delivered in OBI. OBI has a lot of more features which you wouldn’t be using when migrating as well metadata as reports.

Tip: Use different rpd-files when you have loads of data to resolve performance issues because BI Server reads through the whole rpd-file when starting up.

How to start migrating:

  • Export ‘End User Layer’ in discoverer to a .eex file
  • The migrate tool itseld is a windows based tool which you can use command-line to migrate the .eex file to an .rpd file. You can change the properties of the migration tool in the file.
  • Copy the .rpd-file to your BI Server/Repository folder
  • Define which .rpd-file to start-up with in the NQSConfig.INI file
  • Update the .rpd-file online
  • Login to your OBI-environment and open up the Answers-tab to look through the Subject Area and data that has been created using the .rpd file. From now on you start creating your own reports as a business user.

What isn’t migrated when opening the rpd-file in OBI EE:

  • You have to reestablish the connection to your database
  • You have to define a password for your Administrator user otherwise your BI Server won’t come up on your linux environment. This is a very important tip: because it takes a while to find out which error occured when you’ve migrated your discoverer metadata to an .rpd file and your BI Server crashes.

PL/SQL Developer, Quiz thyself

This morning I went to a session of Steven Feuerstein: PL/SQl Developer, quiz thyself. A very interactive and fun presentation where the attendees had to answer questions regarding SQL and PL/SQL.

First we played a game of mastermind in which you can sharpen your deductive skills as Steven mentioned. Playing mastermind improves your skills to debug your code and eliminate factors out.

You can find all the material of his presentations and the quizes in the file which is on his website (have a look at training, seminars).

Some items I’ve noted during the session:

How to continue past exceptions

  • Use format_error_stack to display you errors instead of SQLERR using the DBMS_UTILITY package
  • Use DBMS_ERRLOG.create_error_log to create an error log table based on your table on which your performing the DML on
  • Surpress exceptions at row level => only possible way is when using log_errors()
  • Exception raising is expensive in PL/SQL it’s better to trap exceptions

Performing bulk processing:

  • Use forall to perform bulk processing instead of looping over cursors and performing DML within the loop

Ask for improvements for 12G release: Use the website ‘I love PL/SQL and …’ to add your suggestions regarding functionality to add in 12g release.

  • plitblm holds all functions you can use when working with associative arrays
  • format_error_backtrace gives you the possibility to get the line number where the error was raised. Have a look at Quest Error Manager to get the error-log framework

The entire session was very interesting, especially because it was a fun and practical game where you can teach thyself.

Oracle Open World 2010

Me and my colleagues arrived saturday afternoon in San Francisco, we rushed to our hotel and after a refreshing shower after a whole day trip we went to our first event: drinking and eating ;o)

It was a fun event with a lot of benelux partners but we didn’t keep up very late because of the jet lag.

The next morning we went on a sailing trip with the Oracle Partners, had a nice lunch and then went to pick up our badges at Moscone West. As expected there are a lot of people attending Open World.

Regarding the Jet Lag, my internal clock is still running in the Belgium timezone … hopefully that’ll sort itself out asap ;o)

Today we’re going to start quizing ourselves with Steven Feuerstein …

To all, have a wonderfull Open World and don’t miss our session regarding Forms Modernisation Wednesday !

Forms modernisation at OOW

For the people, partners, customers attending Open World and interested in Forms and fusion technologies I’ve made a little resume of the interesting sessions you could attend:

  • S315945 : Oracle Forms in the Middle of Middleware with Oracle Product Management – Wednesday, September 22, 13:00 | Marriott Marquis, Salon 9
  • S317234 : Moving from Oracle Forms to Java and Oracle Application Development Framework – Tuesday, September 21, 09:30 | Hotel Nikko, Carmel
  • S313982 : Forms2Future: Journey into the Future for Organizations on the Oracle Platform – Tuesday, September 21, 13:00 | Hotel Nikko, Golden Gate
  • S313280 : PL/SQL Developer, Quiz Thyself! – Monday, September 20, 10:00 | Hotel Nikko, Bay View

Sessions regarding Fusion Middleware, Enterprise Architecture, Upgrading to 11g:

  • S313466 : Oracle Fusion Middleware as an Enabler for Transformation and Innovation – Monday, September 20, 15:30 | Moscone West L3, Rm 3018
  • S317629 : Best Practices in Enterprise Architecture: Case Studies – Tuesday, September 21, 17:00 | Moscone South, Rm 301
  • S316135 : From Oracle Forms to a Service-Oriented Architecture with Oracle SOA Suite 11g – Tuesday, September 21, 17:00 | Marriott Marquis, Salon 9
  • S317403 : Oracle Internet Application Server 10g to Oracle Fusion Middleware 11g R1 – Wednesday, September 22, 10:00 | Marriott Marquis, Salon 8
  • S315685 : Stay Away If You Are Technical: This Is Oracle Fusion Middleware for Business – Sunday, September 19, 16:30 | Moscone West L2, Rm 2010
  • S317474 : Oracle Fusion Middleware Application Server Roadmap – Monday, September 20, 11:00 | Marriott Marquis, Salon 9
  • S316409 : Oracle Fusion Middleware Architecture: Choices, Choices, Choices – Monday, September 20, 17:00 | Marriott Marquis, Golden Gate B
  • S316855 : Oracle Fusion Development Platform: Oracle JDeveloper and Oracle ADF Overview – Tuesday, September 21, 11:00 | Marriott Marquis, Salon 9
  • S316075 : Telenet: The SOA Challenge – Tuesday, September 21, 14:00 | Marriott Marquis, Salon 4
  • S316906 : Adding Web 2.0 Interfaces to Your Enterprise Applications: The Oracle Fusion Way – Thursday, September 23, 09:00 | Moscone West L3, Rm 3016
  • S316615 : Migrate: Oracle Application Server Containers for J2EE to Oracle WebLogic Server – Sunday, September 19, 15:30 | Moscone West L2, Rm 2010

If you’ve got other interesting sessions to add, please comment so no one misses out on interesting tips, tricks during Oracle Open World.

Of course besides these sessions there are enough things to see, do and visit up and around San-Francisco such as the cable car, fisherman’s warf, bicking (preferably with a tandem … hilarious) on golden gate bridge, eat a delicious Cioppino, visit Sausalito, …

Hope to see you all there !

Forms Modernization, Part 2: Possible approaches

Forms modernization has a different angle for each customer or developer talking about it but for each angle the approach will always be the same: Think Big, Start Small.

When choosing forms modernization you should choose one topic at a time and define a short- and long-term planning with quick-wins as starting points.

Possible topics could be:

  1. UI modernization
  2. Service-enable existing functionality
  3. Integrate with existing applications, services
  4. Upgrade to enable latest event-driven functionality and entering the fusion ages

Let’s say the customers’ focus for modernization is service-enabling existing functionality, e.g. publish reusable pl/sql functionality as a service.

The approach to accomplish this would be an iterative, gradual approach in which the customer will go through one or more of the following steps:

  • Create a portfolio of the existing pl/sql functionality
  • Categorize those functionalities that could be externalized to other applications, partners, customers
  • Rank those would-be services on impact, development time and business urgency
  • Start with easy, highly visible services to show fast win
  • Find the appropriate tooling

Performing these steps and using jdeveloper to externalize PL/SQL functionality as a service, the existing knowledge and investment in forms and pl/sql is kept whilst uplifting customer and developer satisfaction.

In other words: extending and uplifting existing forms applications to be trully in the middle of middleware is no myth it’s reality.

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