Oracle Reports Generation with Docufy

In a previous blog post we explained already why we decided to build our own report generation tool. In this post we will explain the main principles and components the tool consists of.

First of all, Docufy is a tool for developers: for APEX developers … surely, but in fact for anyone who is developing against an Oracle database and who needs a printable output. In the current version, the printable document is a .DOCX file.

How does it work?
Docufy consists of two major components:
1) a front-end application (UI), used for defining the metadata that describes the documents. The actual UI is written in APEX and is available in all 4.x versions. We are also working on a Eclipse-RCP version of the UI.
2) a PL/SQL engine, which contains all the intelligence and generates the desired output

1) The APEX UI
The main objective of APEX UI is to give the developer an easy interface to configure his document. There are different screens where you can enter the metadata of your ‘printable’ report. This metadata consists of:
- one or more SQL statements
- minimal 1 Word-template (DOCX)
- and optionally input parameters

Following screenshot gives you a general idea of how the UI looks like:

APEX-UI

2) The PL/SQL Engine
The PL/SQL Engine retrieves the metadata which is defined via the UI as input for a given document. It executes the SQL statement(s), binds them with the parameter(s) and gets the specified DOCX-template.
Thereafter, this template is unzipped. As you may know, a DOCX-document is an amalgam of different XML-files. The engine replace all data tags in the document.xml file by the corresponding business data.
At the end all XML-files are packed again in a DOCX-output-document, which is returned as a BLOB variable to the calling program.

Following picture gives a schematic overview of the concept:

So, summarizing:
Docufy is a RAD-tool for generating operational reports. With its intuitive APEX UI, you easily configure your documents by adding DOCX-templates and SQL-queries. The engine is written in PL/SQL, which makes installation, integration and maintenance a walk in the park. The tool comes as a packaged application for APEX 4.x.

Follow @docufy on twitter
www.docufy.be

Are You The Smartest Contest (AYTS) 2013

This battle for all the Oracle Partners in Belgium and the Netherlands is organized by Oracle (Yves Van Looy(BE) and Charles Billar(NL)). All employees of these Oracle Partner companies can compete to be the Oracle smartest of their company for that year. Just like last year, the contest was also aiming to provide an in-depth architectural knowledge about seven different technology tracks.
Like previous editions of AYTS, iAdvise joined this year’s  AYTS contest.

ayts
This year, 17 people represented iAdvise, and tried to win the prestigious price: a business travel to the Oracle HQ in San Francisco and Las Vegas.
Six out of seven tracks were covered by the iAdvise team: Security, Data Integration, BI & Applications, HW & Software combination, Service Integration and Database & options.

This year, iAdvise also improved the oraevents site which is used for this contest. Many new features and functionalities were added to improve the user experience of this tool.  The usability and new functionalities were clearly appreciated by all the contenders, all credits goes to the iAdvise apex team who really made this a very nice tool to work with!

Of course we also had a top 3 for iAdvise: Tuur made it as ‘nummero uno’ for the security track, second place went to David for the BI track and on the third place Kristof who also followed the security tracks. Congratulations to all three!

So we had some high scores on different tracks, which ones again proves that iAdvise is very proficient in several technologies.
But even more important was that Tuur got a gigantic high score of 39/40, something that nobody ever achieved before in the AYTS history and it will likely take some time before someone will achieve an equal or higher score.
He is without question the very convincing winner of this edition, and will enjoy his trip to the States! Congratulations Tuur!

Next year will be without question very exciting, since there are a whole lot of new technologies coming up our way and of course iAdvise will be one of the participating companies!

Checkboxes in editable reports in APEX

We have all been there, we need to create an editable report and one of the columns contains a checkbox. So how should you handle this?

If you are using one of the recent APEX versions the easiest way is a tabular form. Just edit the column attributes of your checkbox column and at display as select “Simple Checkbox”. At the list of values definition type “Y,N”, where Y is the value the column will get when the checkbox is checked.

Tabular form checkbox

But what if you have multiple editable reports that have this requirement on one page? Then it starts to get interesting, since you can no longer use tabular forms.

With multiple editable reports we will be making our own editable report by using the API APEX_ITEM. You can read more about the APEX_ITEM API here.

We first create a report, and in our query we add our “active” column. We create two items there using the APEX_ITEM API: a checkbox and a hidden item. The parameter p_idx is the number  that apex uses to identify the items and write them in an APEX collection when the page is submitted. This has to be unique on the page. We set the value of both items to the id of the column. Why we need these will become clear later on.

SELECT APEX_ITEM.HIDDEN(p_idx =>1, p_value => id)

             ||APEX_ITEM.CHECKBOX(p_idx => 2, p_value => id , p_attributes => DECODE(active,’Y’,’checked=”checked”‘, NULL)) active

FROM MYTABLE

Next we go to report attributes, edit our active column and set display as Standard Report Column. This will allow APEX to render this properly.

Column attributes for APEX_ITEM API

Before we precede let me explain how checkboxes work. In HTML a checkbox that is not checked has no value it is considered NULL. This is something you will have noticed when you create a checkbox page item in APEX in a form. So if we loop over our APEX collection containing the checkboxes we will only loop over the checkboxes that have a value. This is no issue when you only need it to delete rows, but let me show you what happens if you try to use it to update rows. Let’s assume we have two columns, one contains our ID, and one contains our Checkbox with value Y.

APEX_ITEM.HIDDEN(p_idx => 1, p_value => id) APEX_ITEM.CHECKBOX(p_idx => 2, p_value => ‘Y’)
1 Checked
2 Not checked
3 Checked
4 Not checked

Assume we then loop over our first collection and do an update statement in our table:

FOR i in 1..APEX_APPLICATION.G_F01.COUNT LOOP

UPDATE MYTABLE SET active=NVL(APEX_APPLICATION07.G_F02(i),’N’)

WHERE id = APEX_APPLICATION.G_F01(i);

END LOOP;

Looks correct doesn’t it? Well it isn’t. When our process goes over the first row it will update correctly. When he tries to update the 2nd row he will update it wrongly to ‘Y’. And the 3rd row will give an error. That is because our 2nd APEX collection only contains two rows. It does not contain the rows that are not checked.

So now that I explained the problem let’s have a look at the solution.

DECLARE

l_yesno VARCHAR2(1);

TYPE t_checkboxes IS TABLE OF VARCHAR2(1);

l_checkboxes t_checkboxes := t_checkboxes();

BEGIN

FOR i IN 1..APEX_APPLICATION.G_F01.COUNT LOOP

FOR j IN  1..APEX_APPLICATION.G_F02.COUNT LOOP

IF APEX_APPLICATION.G_F01(i) = APEX_APPLICATION.G_F02(j) THEN

l_yesno := ‘Y’;

END IF;

END LOOP;

l_yesno := NVL(l_yesno,’N’);

l_checkboxes.EXTEND;

l_checkboxes(i) := l_yesno;

l_yesno := ‘N’;

END LOOP;

 

FORALL i IN INDICES OF APEX_APPLICATION.G_F01

UPDATE MYTABLE

SET ACTIVE = l_checkboxes(i)

WHERE id = APEX_APPLICATION.G_F01(i);

END;

We start by looping over our APEX collection containing our ID, inside that same loop we loop over the APEX collection with our checkboxes. Both contain as value our ID. If the values match, then the checkbox containing that ID has value ‘Y’. We insert this into a PL/SQL collection that we made for this purpose.

Lastly we do an update in our table, to set our new values. Notice how we only did one update statement using FORALL, and by doing so we limited our context switch to just one, and boosted our performance.

I now hope that everyone got a better idea of how they can deal with checkboxes rather easy, using only PL/SQL and APEX API’s.