In many back-office applications, in Belgium and The Netherlands at least, Oracle Forms is a much seen framework. Praised for the stability and speed of development Oracle Forms was a major player in it’s time. However, today, these Oracle Forms applications get more and more outdated and end users and companies are requiring 21st century features like web service-, desktop- and social media-integration or an intuitive and feature-packed UI.

Often, companies that own such forms apps are told to migrate to another framework like Oracle ADF, JEE or .NET. I ‘d like to add another framework to the list: Eclipse RCP.

Eclipse RCP is an very modern, widely used and stable framework which fulfills all of today’s requirements for applications. Why Eclipse RCP?

During the Forms Modernization seminar organized by iAdvise, I will give a demo on how to integrate an existing Oracle Forms application inside an Eclipse RCP application allowing a very smooth and cost-effective transition from Oracle Forms to Eclipse RCP by retaining parts of the old application inside the new one.

Screenshot of an Oracle form inside an Eclipse RCP view

The demo will show:

  • Oracle Forms inside an Eclipse RCP page
  • how to use Eclipse RCP commands to control Oracle Forms (enter-query, execute-query, save,…)
  • how to pass values from Eclipse RCP to Oracle Forms and vice versa
  • master-detail views (Oracle Forms being the master)
  • how to use Oracle Forms commands to show Eclipse RCP views and dialogs

The seminar is held on the 6th of June, 2011 at Hof ter Delft (www.hofterdelft.be)

If you like to attend please register here

Today, a developer who is working on a large query to build a BIRT report came to me and asked if it was possible to specify data set parameters only once instead of having to specify a parameter value for EVERY question mark in the SQL statement even if the that same parameter is repeated a number of times in the statement.

This developer had 30+ question marks to bind, so naturally, he questioned this way of working.

There is, luckily, a workaround. Actually, there are 2 solutions: the first one is to create a stored procedure in the database and work with REF CURSOR, but the second solution is much easier and stays within BIRT; the WITH clause.

With the WITH statement you can define your parameters at the beginning of the statement and then reuse it.

Let’s suppose you have a parameter called YEAR which you use a number of times in your data set sql like so:

SELECT * FROM tab1 WHERE year = ?
UNION
SELECT *FROM tab2 WHERE year = ?

Here, we have two parameters we have to define twice in the data set parameter binding.

If we now rewrite the statement to use the WITH clause:

WITH
params AS
(SELECT ? AS year FROM dual)
SELECT * FROM tab1, params WHERE year = params.year
UNION
SELECT * FROM tab2, params WHERE year = params.year

As you can see we have moved the question mark to the WITH clause and as a result you only  have to specify and bind the parameter once. Make sure, though, that the WITH clause sql only generates 1 record otherwise you will make multiple rows.

Pfew, that’s saves a lot of monkey work…

PS: You can even create multiple paramlists like that. Suppose you have an additional filter on product consisting of 2 products to be filtered:

SELECT * FROM tab1 WHERE year = ? AND prod IN (?,?)
UNION
SELECT *FROM tab2 WHERE year = ? AND prod IN (?,?)

Rewrite it to use WITH:

WITH
params AS
(SELECT ? AS year FROM dual),
products AS
(SELECT ? AS prod FROM dual
UNION
SELECT ? AS prod FROM dual)
SELECT * FROM tab1, params WHERE year = params.year AND prod IN (SELECT prod FROM products)
UNION
SELECT * FROM tab2, params WHERE year = params.year AND prod IN (SELECT prod FROM products)