Change the owner at runtime in Apex

One of our clients needed an application for the follow-up of the production in their plants. The main feature of the application was to show all the data of the current year, but the client also wanted to be able to consult the historical data. Unfortunately the historical data was not in the same schema as the current data.

The two schemas:

  • SCHEMA_A
  • SCHEMA_A_HISTORY

As you could already guess, the second schema contained the historical data. Each year, the old data was moved from SCHEMA_A to SCHEMA_A_HISTORY.

As SCHEMA_A was our parsing schema, we had all the privileges on the tables with the current data, but we didn’t have access to the historical data. We could have created separate screens for the historical data, but we decided to switch owner at runtime when the user wanted to see the historical data.
For this, we also need the select privilege on schema_a_history

In APEX we can do this with just a single line of code:

apex_application.g_flow_owner := 'SCHEMA_A_HISTORY';

This line is best placed under “shared components > security > database session > initialization code”; this way it is fired every time APEX takes up a database session from the session pool:

Knipsel

In older versions of APEX this field was called “Virtual Private Database”.

As you can see in the screenshot, we have used an application item. When the users asks to see the historical data, we fire a process that changes the value of this item from ‘SCHEMA_A’ to ‘SCHEMA_A_HISTORY’.

That’s it, just a quick tip that can save you a lot of time!

Doxxy 1.2 has been released

Today we have great news for you: Docufy becomes Doxxy !
And not only the name improved!

Doxxy 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 very straight forward. The tool comes as a packaged application for APEX 4.x.

The main concepts and principles are still the same:

  • Simple architecture and installation
  • User-friendly RAD-tool
  • Gathering data via MS Word templates
  • Datasets via SQL statements
  • Generation of DOCX documents
  • Easy integration with the development software of your choice
  • Master-detail structures possible

On the occasion of APEX World of last month, we released Doxxy 1.2. This version includes some interesting new features.

What is new?

First of all, Doxxy 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. Until now, the reporting engine generated a .DOCX file as printable document. In version 1.2 there is an extra option available which makes it possible to have a PDF-document as output.

Other new features we added to the product are:

  1. Possibility to add some PL/SQL logic at the beginning or at the end of the generation process.
    Possible use-cases can be:
    a) set an Oracle context with a language indicator at the beginning of a report, or preparing your data in temporary tables to make the querying more easy.
    b) At the end you may use it for updating a print-status or – flag on given records.
  2. Performance optimalization for documents with a lot of content or with a lot of IF-statements
  3. The export –and import mechanism is XML based. It is now also possible to export/import multiple documents from a given folder in one run.
  4. Easy search-box to quickly find a document in the object tree
  5. Template visualization and validation: when you do an upload of a template, the system does some basic validations on the ‘formal’ content of the template, especially on the names of the tags.
    From within the Doxxy-UI you may also visualize the formal structure of your templates. Errors are visually emphasised in red.
  6. Simplified mechanism to include images (coming from a BLOB-column) into the report-output.
  7. Extra page to maintain your doxxy-specific private synonyms.

Give Doxxy a try and request a free trial,

Follow @doxxyNews on twitter.
Website: www.doxxy.eu.

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!

APEX 5.0: Modal dialogs have never been so easy!

Like all of us, I have had the pleasure to take a look at APEX 5.0 EA1. One of the most anticipated features was build-in support for modal dialogs. In this blog we will examine this feature more closely.

An easy way to investigate this feature is by creating two simple pages: report & form using the wizard.

0Modaldialogs

Follow the wizard and base the report & form on the demo_customer table. When you come to the page where you are creating the form then you will notice the option “Page Mode”. This mode will determine the type of page you use.

1wizard

As you can see there are three modes:
-          Normal: used by normal pages
-          Modal: when this is selected your page will show as a modal dialog
-          Non – Modal:  in this mode the page will act as a normal pop-up allowing you to continue working in both windows. Note that the original window that called the pop-up can still interact with this pop – up

We select the modal mode, and continue the wizard. We have now created two pages with one being a modal dialog. So we see here that the modal dialog is a separate page, and not some region on the page that calls it. This will allow better re-usability, and easier validations. You might notice there is no code anywhere that actually calls the modal page. That is because any page that branches, links or redirects to a page with mode “Modal”, will call that page as a modal dialog automatically.

Apart from the mode difference between the two pages, APEX also created a “Close Dialog” process, and a dynamic action that fires when the cancel button is pressed on our modal page. Both of these will make sure our modal dialog is closed when the end-user presses a button.

2Close dialog

Lastly we might want our report to refresh when the dialog is closed. To do this we go to our report page and right click on our report to create a dynamic action. As event select “Dialog Closed” and region “Customers”.

3DA event4DA action

Then right click on your Dynamic action and create a Refresh action with affected item our Customers region. Now our Customers region will be refreshed when our modal dialog closes.

Time to test our page!

5Dialog

As you can see our modal dialog renders nicely.

There is one last note I would like to add, for the developers who like to write the code for their modal dialog themselves, the APEX developers have enabled us to do so. When you go to the theme for our modal page, then in the bottom you will notice these codes:

6Dialog init code

It basically means that, should you wish to, you can modify this code here, or just replace it with your own, allowing for maximum flexibility!

In conclusion I think it is safe to say that the APEX developers have done an ACE job implementing this feature, thumbs up! If you would like to try the EA and modal dialogs for yourself then you can do that here. If you want to read up on the full list of APEX EA1 features you can do that here.

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.

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!