AJAX in APEX

AJAX is becoming important in the world of web applications. APEX has provided us a very easy way to create an AJAX process, by using dynamic actions. Using PL/SQL Actions in Dynamic Actions to communicate with the database without submitting the page will suffice in most cases, but the downside is that the code is not very re-usable, and when you want to write a plug-in you simply don’t have access to Dynamic Actions. In this blog you will learn how to code your own AJAX process.

An AJAX process in APEX consists out of three parts

  • The JavaScript code that calls the AJAX PL/SQL Process
  • The PL/SQL Process that might or might not return a value
  • The JavaScript code that catches the return value and possibly does something with it

In APEX there are three ways to create an AJAX process from JavaScript:

  • The htmldb_get() method: undocumented but this used to be the only method available (without installing external libraries)
  • jQuery.ajax(): since jQuery was added to APEX, it has been quite common to use this method. It’s well documented on the jQuery homepage, but the downside is you need to write more code
  • apex.server: this new APEX API has been recently added (I believe at APEX 4.2). It is actually a wrapper of jQuery.ajax(), so it supports the same functionality with some additional APEX specific features. It is thoroughly documented in the APEX documentation, and this is the reason I prefer this method, and I will explain how you too can use it

The first thing we do is create a test application. In our case we have a table called “JOBS” that looks like this:

jobstable

In my jobs table I just inserted one job with a salary of 2800 of an unknown currency.

In our APEX application we have an Item of the type select list where the user can select a job, and then the minimum salary will be filled in.

Our page looks like this:

page

Next we write our JavaScript code.  This includes our change event and the apex.server.process . Double click your page name to go to the page definition, and scroll down to “Execute when page loads”.

javascript call

  • AJAX_GET_MIN_SALARY is the name of our future AJAX process.
  • X01 is the variable we pass, in this case the value of our #P17_JOB_ID item
  • Finally we declare that our expected return type is plain text. If we don’t do this, then by default the function expects a JSON string returned. Furthermore we declare in this function what we do with this return data. The return data will be delivered asynchronous, meaning we will get this data from our AJAX Callback function as soon as the AJAX Callback process is ready.

Now we can create our AJAX_GET_MIN_SALARY Ajax Callback process. Just right click on Ajax Callbacks . Click “Create” and select PL/SQL. Here we can put our PL/SQL code:

ajax_process

There are two things here that are worth mentioning:

  • TO_CHAR(apex_application.g_x01): this is how we catch the variable that is passed from our JavaScript code. We use TO_CHAR to identify that it’s a character.
  • HTP.Prn(v_min_salary): here we return the minimum salary back to our page

There, all done!  Let’s test out our application, shall we? Before you do anything it’s best to open the developer toolbar in the browser. In Chrome you can do this by pressing ctrl+shift+J.  It’s  a good practice to reload the page and to check if any JavaScript errors pop up on the console. If our JavaScript code shows no errors in the console go to the ‘Network’ tab, and select a job in the application.

items

You will now see www_flow.show appear. Click it. There are two tabs here that are vital to investigating this function for debugging, if needed. The first is the header, it shows what data is send to our AJAX Callback function.

toolbar

The second tab that’s important is our Response tab. It tells us what data is send back from the PL/SQL Process. If you remember our PL/SQL Process you will notice that we did not include an exception for when no data was found. Select “null” as job and you will get an error. If you then check out the response of the AJAX call you will see it gives our ORA error.

error

If you managed to read this far then you have gained some insights on how you can create your own AJAX function using the new APEX JavaScript API, how it works and how you can debug it should not everything go as planned.

Migrate your MS Access data to an Oracle database using the ETL Tool Talend

APEX is promoted as the perfect replacement for MS Access applications. One thing you should consider though is how you migrate your data to the Oracle database. In APEX there is a handy tool called the Data Workshop that can be used for this. You first export your Excel files from the MS Access database, and then follow the data upload wizard to import the data into identical tables. Since you are not always working with a 1-1 relationship, you will most likely have to write some PL/SQL to get all the data in the right tables.

dataworkshop

The downside is that you will need to repeat this process when you go into production. This is not a big problem if you only have one table to migrate. But if you have multiple tables and/ or your users also want new data during tests and trainings, you will spend a lot of time exporting and importing Excel files.

A recent APEX project for a client required a large data migration from MS Access Databases to the Oracle database. Because we would require fresh data on several points in the development process we decided to use the ETL Open Source Tool Talend. We got impressed of how intuitive the tool is, it only took a few days before we were familiar with the tool. Once you get the hang of it, you can write (or should I say draw) migrations of tables in no time. We needed to migrate from an MS Access database but the tool supports a wide range of databases and documents to import your data from. In total we migrated around 30-40 tables to our Oracle database.

Let’s have a closer look at one of our migration jobs.

talendmsaccessoracle

At the left we see our MS Access database. Each tAccessInput component will get data from one table. After that we join the tables in our tMap_1 component. The reason we don’t just write our joins in one component, is because this way we can really see how many rows every table returns.

On the bottom we have some Oracle Database input connections. They will join the persons of our MS Access Database with the persons in our Oracle Database based on the National registration number. After that we write our data to our Oracle Database. You may notice that we have two lines going to Excel files. This is our error logging; we use this to log the rows that did not find a match. In our first Excel for example we write persons that did not find a match in our Oracle Database.

This is just one example, in total about 20 jobs were built. During the development we also had to deal with certain calculations or convert data. For most things there was a component ready to use and if there wasn’t you could always write a Java expression in the tMap items.

I hope I convinced you of the benefits of using Talend as a migration tool for APEX projects, because we will certainly use this tool again!

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

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.

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.