5 Things I wish I knew about apex when I just started (part 1)

I’ve been doing apex for about 7 years now, and along the way I discovered a few things that made my life as a developer a lot easier. I made a list of 5 things thar I wish I had known wen I just started. Those things will be explained to you in a series of 5 posts.

Subscriptions

One of the most tiresome things to do when managing multiple applications is to keep things like templates, and security up-to-date across all applications.

Apex has a nice built-in system for this called ‘subscriptions’.

If you just start with a new project, the easiest way to set-up the subscriptions is by creating a new application and call it “MASTER APPLICATION” or something similar, so you know this will be the parent application for all subscriptions. For an existing application the same method applies, but it will be a bit more work intensive to get everything right.

In this application, you define your, security scheme, authorization schemes, templates, list of values.

Once you have your basic configuration done, you are ready to start working on your new applications.

To link the new application to the MASTER application let’s say for example an authorization scheme.

You go to shared components, authorization schemes, create.

Select “create as copy”.

apex_P1_1

Copy from the master application and then select copy and subscribe.

apex_P1_2

If you edit the authorization scheme now you will see that in the subscription part there is an application referenced.

apex_P1_3

By now you are wondering: “What’s the point of all this? Couldn’t I just as easily have copy pasted the code?”

Yes, you could have, but imagine having 20 or more applications using the same authorization scheme, and then one day you may have to change something inside, you would have to modify all 20 schemes in all 20 applications again.

But because now we reference the master template this is no longer necessary. For instance let’s say we want a function to return false instead of true, what we can do now is go to the master application, edit the authorization scheme, change it, save it, and press the publish scheme button.

In the long run this could save you tons of work!

apex_P1_4

For subscribing templates in apex there is a way to do all templates at the same time.

Go to shared components, templates.

On the righthand side you have a menu called task, click the link to replace templates in this application with templates from another application.

apex_P1_5

Select the master application again, in the next screen select the template, and in the action drop down select, replace/subscribe, and press replace templates.

apex_P1_6

 

Export/Import Apex applications in SQL Developer

Using SQL Developer to export or import an Apex application is not only easy, it’s also fast!

Export

To export, follow these steps:

Open the connection to the schema where you’re application is located and search for the “Application Express” directory:

image_1

Right click on the application you want to export and choose “Quick DDL” > “Save to File…”:

image_2

When done, you’ll have the same file as you would have exported from the Apex GUI.

 

Import

To import an application, right click on the “Application Express” directory, and choose “Import Application”. You can also right click on an existing application; the next steps are the same.

Follow the wizard. When this screen appears, you can choose to do a number things:

image_3

 

  • Auto Assign Application ID
  • Use the application ID from the export
  • Specify New ID

When the ID that you want to use is already assigned in your workspace, don’t forget to check the “Overwrite” checkbox.

At the end of the wizard you see the progress of the installation:

image_4

When finished, you’re application is up and running.

Using Apex Authorization schemes in PL/SQL

The problem with using APEX authorization schemes in PL/SQL has been addressed several times in blogs and forums, but we occasionally still get questions  on how to solve this:

I have a page where users with admin roles can modify data and other users can only view it. Hiding the button to save the record is easily done with an authorization scheme:

Capture

However, now I want my items to be displayed as “Read Only” too. There is no option to select your authorization scheme, but Apex wouldn’t be Apex if there hadn’t been an easy solution.

The function “apex_authorization.is_authorized(‘authoutization_scheme’)” does the trick. It will check the authorization scheme and return a boolean. Add a small PL/SQL block in the Read Only-part of your item like this:

Capture

Now your item is read only for persons without the admin role.


Some additional information:

With this function it’s also possible to combine multiple authorization schemes:

IF apex_authorization.is_authorized('isAdmin')
   OR apex_authorization.is_authorized('isWrite')
   OR :P3000_USER = 'TEST' THEN
  RETURN FALSE;
ELSE
  RETURN TRUE;
END IF;

Attention: if you want to use this functionality prior to Apex 4.2, you need to use “apex_util.public_check_authorization“!

Apex Interactive Report: The difference between CIR and RIR

You’ve probably already used the reset functionality in an Interactive Report, but do you know the exact the difference between CIR (Clear Interactive Report) and RIR (Reset Interactive Report)?

RIR or CIR, what is it?

First, let’s explain what CIR and RIR is and how you can use it.

With CIR and RIR you can clear or reset your Interactive Report after using filters. This can be handy if you’ve applied a filter on a page, but also want to (re)use the full report.

If you want to apply a filter on an interactive report when linking from another page you can use the following operators.

Function Meaning
IREQ_<COLUMN_NAME> Equals
IR_<COLUMN_NAME > Same as IREQ
LT_<COLUMN_NAME > Less than
IRLTE_<COLUMN_NAME > Less than or equal to
IRGT_<COLUMN_NAME > Greater then
IRGTE_<COLUMN_NAME > Greater then or equal to
IRLIKE_<COLUMN_NAME > Like operator
IRN_<COLUMN_NAME > Is Null
IRNN_<COLUMN_NAME > Is not Null
IRC_<COLUMN_NAME > Contains
IRNC_<COLUMN_NAME > Not Contains

You can also use above functions in a saved report, then you have to use IR_REPORT_<ALIAS>.

Sometimes the page with the interactive report can be accessed by multiple pages and you would like to reset the filters already applied to the interactive report.

i.e. You have a page with Countries and you want to filter your existing Customer Report to all customers in selected state. But, when you click on Customers on another page you want to see all customers, not only the ones you just filtered. In this case you can use CIR or RIR.

You can simply enter these options in the URL or the Clear Cache option in your button / branch.

APEX_CIR_RIR

But what is exactly the difference between using the CIR and the RIR?

As the name suggests, CIR Clears and RIR Resets.. But isn’t that the same?

Almost. The main difference is that CIR Clears the report, clearing all breakpoints, filters and other defined actions on your report, ignoring the settings of the primary report. RIR Resets the interactive report to the primary report.

In the following table you can see which user defined modifications to an Interactive Report will be lost or kept when using the CIR/RIR clear cache option.

  CIR RIR
Main Function Clears Interactive Report Resets Interactive Report
Maintains:
Column visibility YES* NO
Primary Report NO YES
Filters NO NO
Breakpoints NO NO
Pagination NO NO
Sort YES NO
Highlight NO NO
Computation NO NO
Aggregate NO NO
Chart NO NO
Group by NO NO

* Please note that when a CIR has been given, the columns displayed are still the columns of the primary report, but ‘stripped’. But if you alter the shown columns as a user it will display these columns.

For a demonstration click HERE.

Conclusion

In conclusion, use CIR to clear all filters and other settings set by the user or in the primary report. Use RIR if you want to reset to the primary report keeping all filters and columns of the primary report.

OGH APEX World 2014

Last week we attended the the 5th annual APEX World event in Zeist. As every year it was very nice to meet the growing APEX community in the Benelux, combined with some excellent APEX international and dutch presentations.
The  keynote was given  by Joel Kallman about APEX 5.0 followed by 18 very interesting sessions about customer business cases, technical developments and international presentations by APEX specialist from all over the world.

APEX 5.0

The key focus in the new APEX 5.0 is improved developer productivity.oracle apex page designer
The page builder is completely new. Through this interface developers will be able to do more in less time and most important, in fewer clicks. With a properties sidebar on the right side of the screen it will be possible to quickly change elements and regions on a page, even multiple elements at the same time!  Regions and items can be created through drag and drop which increases the development speed.

Other new features

Tabs
Improved tab navigation. The current tab system isn’t user friendly enough, so it’s better to use lists. Now you can create new pages and define their hierarchy in the application. When this is done, an automatic tab will be created with dropdown submenus to display the hierarchy.

Interactive reports
Two important improvements for interactive reports. First and foremost it’s possible to have multiple interactive reports on one page, something we’ve all been waiting for since APEX 4.x. And secondly there is a new format function to pivot your report. Joel Kallman presented this feature: in a couple of clicks he created a nice pivoted table on the screen.

jQuery Mobile integration
With jQuery Mobile your SQL reports will have the possibility to be responsive. You have the option to:
a) only display the most important columns on a small screen, or
b) to switch to some kind of single record view. The result is something similar to what you can see here: http://elvery.net/demo/responsive-tables/

Modal popup
Instead of using a plugin to let your pages open in a modal window, users can now set this feature as a property of the page. Whenever the user navigates to this page, it will open in a modal window.

Be sure to take a look at the APEX early adaptor: apexea.oracle.com

 

Presentations

After the APEX 5.0 demonstration, there were 3 parallel tracks, all with very different and interesting sessions.  Read our impressions …

Going public with your APEX application
FOEX brought this presentation very well. Their problem scenario was the following one: If you want to make a public APEX application, you are always stuck with the typical APEX URL like “apex/f?p=100:1:5039230103::::”. During the demo they showed how to create a nice and readable URL like “apex/demo/customers”. To accomplish this they used aliases, REST services, PL/SQL and a few lines of javascript.

The best of both worlds: going hybrid with your mobile APEX application
Roel Hartman gave a presentation about Phonegap in combination with APEX. He showed a nice demo on how to sync the contacts from a database with the ones from his cell phone through a Phonegap App. It was surprising how easily this could be setup without too much code and in-depth knowledge. He used REST services to sync the data between APEX and his cellphone.

Using AngularJS in oracle applications express
Dan McGhan of Enkitec (USA) brought a technical session about combining AngularJS and APEX. He showed us a single page application containing a to do list with advanced calendar features. The end result was very nice and the demo illustrated the power of AngularJS, but it certainly requires some time to understand this framework. Maybe an interesting idea is to include AngularJS natively in APEX 6.0?

A B2B weboracle apex b2b webshop - tuur hendrickxshop with APEX!
iAdvise did two presentations. The first one dealt with a B2B webshop we developed in APEX for Billiet. Justine Ghekiere gave a brief introduction about the core business of her company, Biliet. Our colleague Tuur Hendrickx showed a lot of features he implemented in the webshop with APEX. Topics he show-cased were:  special advertisements, restricted products for different customers, the use of a shopping cart and a stunning layout were demonstrated.

APEX & HTML5
We also attended a nice presentation of Martin Giffy D’Souza about APEX and HTML5. He showed the advantages of HTML5 and the typical use cases in APEX. During a live demo he showed how to record a video within APEX and stream the feed to another frame in the same screen. Really impressive!  Also nice to see was how easily it is to implement voice recognition by using HTML5.

Dutch immigration services (IND) monitor xml messages with oracle apex
A department of the Dutch government has built an application which provides residence permits to immigrants or refugees. Before they could start building the APEX application there was a lot of effort necessary in the Oracle database for dealing with all the XML files. It was not just a problem with the size of the XML files, but there were also issues with differences between Oracle 10.2 and 11.2 in the way the database handles XML files.

Reporting solutions for oracle APEX – choose your weapons
During this session Dietmar Aust gave us an overview of possible reporting solutions  for APEX applications. Many solutions were covered in an objective way:  BI Publisher, Jasper Reports, Apache FOP, APEX PDF printing, PL/PDF, … Dietmar even demonstrated our own tool Doxxy (www.doxxy.eu). Nice to hear that he likes Doxxy! He also showed us his own solution for typical problems related to exporting data from interactive report to MS Excel, especially regarding the proper data types: OPAL:XP (for eXPorting to MS Excel).

Single-click deployment in APEX development
One of the last tracks we visited was about single-click deployment of APEX applications in OTAP areas. They talked about the use of bamboo, in combination with GIT and APEX. It was nice to see how they solved the problem of continuous integration with APEX.

A logistic data portal with APEX!oracle apex data portaal - menno hoogendijk
In the second iAdvise customer case Robert Esseling explained why Bas Logistics needed a data portal. Those requirements where then demonstrated by Menno Hoogendijk.
The portal has an admin module to manage the data import and mapping settings. In the very straight-forward  front-end, users drill down from dashboards to detailed data.

 

Thanks to the organization for hosting this great event, really one of the best conferences in the benelux!
See you at APEX World 2015!

Browser and Session value in APEX

For those of us who have been developing in APEX for a while will understand that the value of an item in APEX is not always what it seems. That’s right; an item has two values in APEX. The first value is the session value that we can store in the database (server side), the second value is the value the end user sees on his screen in the browser (client side). On some occasions these values are the same. On others they are not. The difference in value can lead to some confusion, especially for the new APEX developers.

Let’s start with an example so we understand the problem. This is actually based on a real use case. I changed the item names and queries to make it more universal, but the principle remains the same. The developer in question had a page with two Select lists containing the following queries:


SELECT 1 d, 1 r
FROM dual

And

SELECT 1 d, 1 r
FROM DUAL
UNION
SELECT 2 d, 2 r
FROM DUAL

The second select list (called P55_SELECT_LIST_2), also had a default value of 2.

When the user selects a different value on the select list then the second select list had to change values accordingly.

In order to achieve this the developer had created a Dynamic Action. As event he had a change Event of the P55_SELECT_LIST item. The first action was a set value containing the following query (again made simple for the example):

SELECT 1
FROM dual
WHERE  1 = :P55_SELECT_LIST

And page items to submit he had selected the item P55_SELECT_LIST, and affected items he put P55_SELECT_LIST_2

The last action he set was a refresh action of our P55_SELECT_LIST_2 item.

Our page setup now looks like this:

1pagesettup

The developer tried his application, and when he changed the first select list he saw the second select list being refreshed. Yet, instead of displaying the correct value (1), it displayed the default value.

2items

So what went wrong?

Let’s see what exactly happened to figure that out:

  1. The page was loaded. The first select list had nothing selected, the second select list had “2” selected in the browser. Both items were empty in the session3session
  2. The value of the first select list was changed by the end user
  3. This triggered the change event defined in the dynamic action
  4. The first action “Set value” was executed. The value in the browser was changed. The session value of the item P55_SELECT_LIST  was set to one, because it was set with items to submit. An AJAX request was made to set this value. The value item P55_SELECT_LIST_2 remains NULL.4session
  5. The refresh action was executed; an AJAX request was made to get new values for the select list. Since the SELECT_LIST_2 item has no value in the session, the default value is taken, which is 2!                                                                                                                          5network

The solution was simple. Just remove the refresh action. Then the value is set in the browser correctly.  This is handled by APEX with JavaScript/jQuery. The session value of the item will remain NULL until we submit the item to the database by a page submit, a dynamic action, or by changing the value in an AJAX Callback process.

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.