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.

Bryxx has launched!

On Tuesday, May 7 the Bryxx launch event took place. In the beautiful setting of the Flandria boat, and in the presence of a large number of customers, we revealed the services of this new venture. As a joint venture between the iAdvise and Contribute infrastructure teams, Bryxx will specifically focus on the middleware field.
In bringing together both expert middleware teams, we will focus on
  • Opening up your business critical web applications to your intranet or to the internet
  • Making sure that these applications, deployed on your middleware stack, are secure on all layers of the underlying architecture. Security from-data-to-browser
  • Streamlining and automating your process of development towards production
  • Providing you with the opportunities to outsource the maintenance of your private middleware cloud or to outsource your entire private middleware cloud
From a technical point of view Bryxx will dedicate its expertise to 4 domains:
  • Oracle Cloud Application Foundation (with web logic as the main driver)
  • Oracle Identity & Access management
  • Oracle Database Security
  • DevOps
With respect to these 4 areas of expertise, Bryxx provides strong consultancy profiles on all levels (pre-sales, infrastructure architects, senior implementation engineers, etc) to design, install, configure, maintain and monitor your middleware platform as well as to streamline the process of application development towards your preferred middleware solution.
When you add our managed services and hosted solutions offering on each of these domains to this package, with strong partnerships in the backend, we believe Bryxx has a strong and complete offering for all your middleware challenges !Our team of 14 dedicated and experienced middleware engineers is ready for you.
Want to know more?
Visit us at www.bryxx.eu or contact us at info@bryxx.eu
 bryxx1bryxx2bryxx3bryxx4

Mobile Business Development seminar in Belgium and The Netherlands(4 and 6 June)

iAdvise organises a free seminar about Mobile Business Development on 4(Antwerp, Belgium) and 6(Breda, The Netherlands) June.

In this seminar we will discuss Mobile Development and Usability(UX – User Experience) in Mobile Development.
But we also compare different solutions, give pros and cons about: APEX Mobile, ADF Mobile and Tabris.

Of course we will also show you best practices, problems and solutions,…

More information(Dutch): http://www.iadvise.eu/seminars

Docufy: Document Generation for APEX made easy!

APEX is well known for his RAD solution and the no-extra cost factor: APEX makes it possible to build on time and within budget your database application for the web. But an application is more then building pages for CRUD operations. Very often you also need to generate well looking documents. And this is standard not possible with APEX. A pity!  Within the Oracle world, Bi Publisher is by far the best solution. But from the pricing point of view, it does not really fit in the ‘cheap’ philosophy of APEX. Further, you can also look for Open Source solutions like Jasper Reports and Birt. Both have a community version that you may use for free.

Nevertheless, we decided to build our own tool. During the presentation at APEX World of last week, organized by OgH, we explained how we came to this decision. On different projects at the Flemish Government we always had the business requirement to generate output for MS-Word (.docx). We first tried to solve this requirement with Jasper Reports. This was fine for simple letters. But the end-user also wanted to change some of those documents after generation … and that was not so easy to do with the output created by Jasper.

So the idea for an own “document generator for Oracle” was born. The main objective was trying to bring some of the better features of other tools together in one solution:

  • Separation of data retrieval and document layout.  The layout is template driven: your template is a MS Word document (.docx). This leaves the possibility open to involve business users in the design and build of the desired output.
  • Simple, easy and light: we want the solution to be as much as possible in the database, in PL/SQL, with no extra software on a middle-tier
  • Metadata driven and nice user interface to manage those data
  • Integration in your application via a PL/SQL API. This application may be developed in the technology of your choice. We use it initially in APEX projects, but it can be integrated in any tool that can do a PL/SQL call (Oracle Forms, any java solution, Formspider, …)

Our custom solution evolved well: we already used it successfully in different projects. Therefor, we decided to make it available for the world and packaged the solution in a tool with the name Docufy. You can find more information on the Docufy-site or you may always send a mail to info@docufy.be.

With Docufy,
we proceed on the APEX philosophy,
there where APEX it self stops:
easy and quick generation of operational documents.

Oracle DB 12c, APEX 5.0 and APEX tech sessions at OGH APEX World

April 9th 2013, APEX World took place, one of the biggest APEX only events in the world, organized by the Dutch Oracle user group (OGH). iAdvise was gold partner of this year’s edition. Below is a view on this event from our perspective.

iAdvise stand

iAdvise booth

The event took flight with the keynote by David Peake, product manager from Oracle. He spoke about Oracle DB 12c, what it is and what’s in there for APEX developers, and some words about what to expect from APEX 5.0. Again, we had to conclude that great things are coming our way in the months to come:

  • pluggable databases,
  • new column types,
  • data redaction,
  • new IDE-like view for the page builder,
  • multiple interactive reports on 1 page,
  • multi-row edit region type,
  • enhanced builder security
  • and some more great stuff…

But as we are used to with Oracle, it’s all subject to change and might not make it into next release :-)
More in-depth details and another view on this presentation can be found on Christian Rokitta’s blog.

The only two things David was clear about were:

  • APEX 5.0 will focus on improved usability and quality rather than on new features;
  • there will be an APEX 4.2.2 release before 5.0 will become available.

Below are our highlights from the presentations we followed.

Transfer Solutions presented their “portal” solution to combine both Forms and APEX applications with one custom single sign-on. We saw some interesting approaches for common issues and found confirmation for our approach in comparable situations.

Rob van Wijk from Ciber presented his white paper “Professional software development using APEX”. Together with his colleagues, he worked out a way to version database schemes and APEX applications in Subversion when all developers are working in their own isolated environment. On top of this, he used Hudson to automate the versioning update-commit process. A must read paper if you consider versioning within your APEX environment!

Regarding responsive web design (RWD), there were two presentations of interest:

Dimitri Gielis introduced how RWD can be accomplished in APEX by using the new built-in grid system and spoke about APEX theme 25 (fully responsive and new in APEX 4.2) but also Twitter bootstrap, which can perfectly be integrated in APEX.

Later on, it was up to CB Logistics, together with iAdvise colleague Stijn Van Raes, to present how they used RWD in a recent project.

Peter Raganitsch and Matt Nolan from FOEX came all the way to Zeist to state that APEX truly is the best successor of Oracle Forms…but not out-of-the-box. In a few demos, in which they combined declarative APEX functionality together with their plug-in framework, they showed how APEX can become a perfect fit for everyone who wants to migrate good old Forms without losing much functionality, usability and maintainability. A bold statement but definitely something our APEX developers will look into.

Roel Hartman showed us what mobile web development with APEX theme 50 and jQuery mobile is all about. He walked through the new item types, lists and buttons and demonstrated how you can easily make your application look better with standard APEX functionality.

Our iAdvise colleague, Karen Van Hellemont, presented with Natasha Van Hoof from the Flemish Government how documents can be generated from the database. Throughout several years, iAdvise gained experience in generating documents with PL/SQL and developed a metadata driven solution with a simple API to re-use it in other projects. The Flemish Government is our first customer to use the complete version of this solution and therefore gave a customer testimonial on their experience with the tool.

The price for most-entertaining presentation definitely goes to John Scott (APEX Evangelists) when talking about HTML5 websockets! The concept of websockets took about 10 minutes. The remaining time was used for demos about what you can do with this. We never heard so many sounds of astonishment in 30 minutes!

For a Belgian-Dutch Oracle consultancy company, this is one of the most valuable events next to Oracle Open World and this year’s edition really showed us that APEX is far from ever going away! It has great potential for a very wide range of businesses and allows both developers and end-users to maximize their needs in every way.

OBUG CONNECT 2013: impressions from an APEX Developer

On 26 march I attended OBUG CONNECT 2013 at Kinepolis Antwerp. With around 40 sessions to pick from, picking out sessions was not an easy decision.

One session that caught the eye was the presentation about Formspider given by Yalim Gerger. While the presentation name and description did not reveal that the presentation was about Formspider, it was not hard to guess since it was given by its creator.

Yalim explained that since 2008 the landscape in the IT world changed. In 2008 developers developed for one platform, now there are many more platforms, all which require different programming skills. Yalim suggested using Formspider, a PL/SQL framework as an alternative. Formspider uses one javascript library maintained by “one” developer. This way only one developer needs to worry about security, browser compatibility and browser performance. On top of that developers only need to learn only one programming language: PL/SQL.

It must be said, Yalim made a strong case. Unfortunately we did not get the chance to see his framework in action, but it has to be said, if even a part of what he said is true, Formspider could become a strong player on the market.

Another interesting session was given by Patrick Hendrickx from Telenet. Telenet used APEX to Design some internal project management applications. He decided to go with APEX rather than buy a product or expand existing products.

He had some good arguments to do so:

-          APEX is Browser only technology

-          APEX is RAD

-          It has good and easy graph support

-          Wizard driven, so less programming skills required

-          Lastly because they already invested in Oracle, it was easy to integrate with the technology stack from Telenet

Not only did APEX fulfill the requirements, it also was a good decision from a financial perspective.

The demo Patrick showed of the different tools was also quite sexy. It is nice to see that architects are starting to see the Advantages from APEX as a development tool.

In conclusion I would have to say that, even though I could not attend all APEX sessions, it was pretty clear that APEX was strongly represented on OBUG, it shows that it is an important player on the Oracle market.

Using ADF Logging in a non-ADF project

In a previous post (Starting with ADF 11G Logging), I explained how ADF logging is simple to set up, and how it will enable you to set the logging levels at runtime, without having to restart any server. When I showed this to a colleague of mine, he immedialtely popped the question : “Can’t we use this for all of our java applications, even the ones that don’t use ADF?”. Well, the answer is yes, and it turns out to be very easy. Just add the correct jar to your project and your done.

This blog will demonstrate how to get this working. I use Eclipse Juno to create a small webproject, only containing a servlet that does the logging. In fact I will use the same servlet I used in the previous post.

So I open my Eclipse , and started with a File -> New -> Dynamic Web project. Give it a name, set ‘Dynamic web module version’ to 2.5, click the  ‘Add project to an ear’ checkbox and click finish.

dyn_wb_prj

Now Eclipse has created a web and ear module for me.

Image

Now right click the web project (ADFLogging), and select New -> Servlet, give it a name, eg. TestServlet, and click finish.

Remove the generated code in the servlet, and copy the code from the servlet ‘ExecuteLogger’ from my previous post (here) and paste it in our new serlvet.

PS. : When you copied the code from my previous blog, don’t forget to set ADFLogger.createADFLogger to our current servlet class name : TestServlet.class.

We will get compile errors on HttpServletRequest,etc… and on the ADFLogger class because they are not defined in the classpath of the project. So we’ll add them in order to get our servlet compiled.  I get the 2 jar’s from a JDeveloper installation I did on my machine. We’ll only add these jar’s in order to get the servlet compiled in Eclipse. We will NOT deploy them, as they are already available on our Weblogic server.

To add the jar’s, right click on the web project, and go to Properties. In the Properties, click on ‘Java Build Path’.

buildpath

Click on ‘Add External JARs…’ , and go to the directory where you installed your JDeveloper, which in my case is : C:\Oracle\Middleware.

In that directory , get following jar’s from the sub-directory :

\oracle_common\modules\javax.servlet_1.0.0.0_2-5.jar : contains the servlet classes like HttpServletRequest/Response,etc…

\oracle_common\modules\oracle.adf.share.ca_11.1.1\adf-share-base.jar : contains the ADFLogger classes.

Now we see the the following jar’s added :

jars_added

Click OK and return to the servlet. In the servlet use CTRL-SHIFT-O to import the neccessary classes from the jar’s we just added.

Now all compile errors should be gone.

Generate the ear file as follow : File -> Export -> Ear file

Select the ear project and enter destination of the ear file

When you examine the ear, you will notice that the folder \WEB-INF\lib is empty.

As the servlet and ADFLogger jar is already available on Weblogic, there is no need to deploy it with our application.

Now deploy the ear to the Weblogic and test the servlet with following url. :

http://localhost:7101/ADFLogging/TestServlet

It will generate following output :

output

To check the logging done by this servlet :

As I used the integrated Weblogic of JDeveloper, I will look for my logs using JDeveloper, but in a production environment,

these logs can be viewed using the enterprise manager of Weblogic. For details, see my previous blog.

In the Oracle Diagnostics Logging configuration, I see my servlet after the deployment. No message level is defined, so it will take “Warning”, as this one is defined as default by the Root Logger

logger

After te execution, I see following log lines in the log analyzer.

result

So that’s it. So the bottom line is to add the ADFLogger jar to your non-ADF project, and you are ready to go !