Export multiple BLOB's from Oracle using SQL Developer

 My customer came to me with a question. He wanted to export some 150 PDF documents out of the Oracle database. The documents were stored in a BLOB column in a table, together with the filename. The only solution he had was to export each file manually by selecting the row and download the file, giving it the right filename. 

Now at first I did not have another solution. I thought about writing a procedure to bundle the files using APEX_ZIP, storing that in a table and the downloading this file. But we had no access to a table in which this file could be stored. 

Then while searching the internet I came across an solution from Jeff Smith (yes, that Jeff Smith). When exporting a table/query result you can choose Loader as export type. For this type the BLOB columns are exported to separate files (with technical names) and an accompanying CLT file to load the rest of the data. This way the files can be downloaded but they don't yet have the right file name. 

This can be tackled by using the following query with the italic text replaced with appropriate table and column names:

select blob_content

     , file_name

  from table_with_blob_content

Use the following parameters to export the resulting rows of the query:

The content of the resulting TABLE_EXPORT_DATA.ldr file looks like this:

filename1|TABLE_EXPORT_DATA_3c11e4a1a-0191-1000-8011-0a57605edcf0.ldr|{EOL} filename2| etc...

Do some smart editing on this file to generate the following result (for Windows). I used Notepad++ because it can insert returns in a replace action:

ren filename1 TABLE_EXPORT_DATA_3c11e4a1a-0191-1000-8011-0a57605edcf0.ldr

ren filename2 etc...

When this file is executed on Windows all the .ldr files will be given the right file names. 

Within a few minutes all 150 files were exported and renamed. 

My customer was very happy, preventing him from having a mouse arm ;-). 

Happy developing

Creating a mobile app with APEX - Part 11: Displaying form items side by side

After delivering two new presentations at Apex World 2021 and hROUG Conference 2021, I have enough material for a few extra blogposts on mobile development with APEX. I will start with displaying form items side by side on an APEX smartphone app. 

Description of the challenge

Normally all form items will be placed underneath each other in a form on a smartphone:

To save valuable screen estate, or to group two logically connected items, sometimes we would like to have two items on the same line. 

APEX has the possibility to place form items side by side by disabling the Start New Row attribute for the second item:

On the desktop the result will look like this:

This does not work when the width of the page is less than 640 px, i.e. on most mobile devices, Commission will be placed on a new line:

Analysis

This behavior is caused by CSS. A media query aimed at screens with a width smaller than 640 pixels, determines that the width of the items is 100%. This way no more than one item fits on one row. 

Solution

So we need to overrule this CSS. For this we can define this snippet of CSS on the page:

@media (max-width: 640px) {
    .col.side_by_side {
        width: 50%;
        float: left;
        clear: none;
    }
}

This CSS sets the width of the item to 50% instead of 100%, and applies float:left.

The two items that are supposed to be on one line, should both receive the class side_by_side and the second item should have the property Start new row unchecked:

If you do not want both items to take up half of the row, you can change the width in the Column Attributes. Just make sure that the total width does not exceed 100%, otherwise the items will again be placed underneath each other. 

The result of these definitions looks for the columns Salary and Commission looks like:

Happy APEXing!

New Plug-in: Reacting on changed content of APEX Autocomplete Item

Why use Text Field with autocomplete

Right now I am developing a series of mobile applications for personal use. And of course I learn a lot about developing with Oracle APEX for mobile.
I like to use auto complete items in mobile apps because to my opinion select lists are less suited for mobile use most of the time. One of the reasons is that in long lists, in contrast to in desktop applications, you cannot filter on the first character. Because you do not have a keyboard! So you have to scroll a lot. On iPhone this takes place on the bottom of the screen where you see only about three items at a time. I once checked in for a flight using the mobile app from the airline. I had to select my country (The Netherlands) from a select list of some 200 countries four times. And each time it took me about 30 secs to scroll through the 170 countries until I reached The Netherlands.
With an autocomplete item it would take 2 secs to type in a discriminating 3 or 4 characters to get to the result.
So that is why I prefer to use Autocomplete items. But I found out there is a problem with reacting on when the content of such an item changes. A Dynamic Action of type Change does not fire when the value of an Autocomplete item is selected.

Reacting on change of Text Field with autocomplete

A quest for an answer on the internet revealed, that a special event type should be used. Under the heading Component Events we can find: Update [Text Field with autocomplete].

When using this event to define a Dynamic Action, the DA fires the moment a value from the suggestion list of the autocomplete item is chosen. So contrary to the Change event the focus is still on the Autocomplete item. Furthermore there is no reaction on values entered that are not in the suggestion list.

So I started off to find a way to detect changes in the Text Field with autocomplete.
I defined a On Focus DA in which the current value is stored into an attribute old-value of the input item. In a Lose Focus DA this value is retrieved and compared to the current value of the item. If there is a difference, action can be taken. Then I selected a value form the auto complete suggestion list. This took the focus away from the Autocomplete item, and the change event was triggered.
So the Lose Focus DA does not work.
With some googling I found out that only input, select, textarea, anchor and button elements can receive focus. So I could try to implement the code in Lose Focus in the Get focus actions for the other elements. And this works!

But it is quite cumbersome to define these actions for each Autocomplete item and all the other elements we want to monitor. Besides the task would be difficult for developers who are not so familiar with JavaScript. So I decided to create a plug-in for it.

Autocomplete change detection DA Plug-in

So I have developed a plug-in for watching changes in Autocomplete items and triggering an event when a change is detected.
You can find the plug-in on apex.world. 
You can apply this plug-in on Page Load. The plug-in automatically watches all Text Fields with autocomplete on the page.

When the value of an Autocomplete item is changed, an event is fired from that autocomplete item. You can find the event in the event in the Component Event chapter of the Event list, as you can see in the DA definition below:

Happy APEXing.

APEX 20.1 nugget: Mega menu's

A few days ago I downloaded and installed APEX 20.1. I was inspired by the presentations of Shakeeb Rahman and Patrick Wolf on ODTUG Learn from home. They talked about mega menu and user friendly URL's. And I thought, I want to have this too!

So I downloaded the installation package, studied the installation/upgrade manual carefully, and followed the instructions as well as possible. In the past I have had troubles with ords and static files support after upgrading, costing me hours to fix. This time everything went smoothly and after performing all the indicated steps I fired up my new APEX 20.1, and it worked :-).
I suppose it pays to RTFM.

Mega menu's

Mega menu's provide a large menu, where you have all the menu options available at once. 

In my APEX demo application the menu's have been a constant struggle for me. I wanted all the pages to be reachable from the menu directly, but this way the menu grew too long and the options disappeared below the bottom of the screen (even on my 27" iMac!). 

So mega menu's were very promising. And indeed they did fulfill my expectations.:

The nice thing is that Mega menu's work on the same data as the original APEX side menu. So initially you do not have to change anything to start using them. Just change some setting in the User Interface and you have your own Mega Menu:

You can visit my APEX demo application https://www.speech2form.com/ords/detora/r/opfg to see the Mega Menu in action. It took me about half an hour to reconstruct the menu. Btw, do you notice the user friendly URL? 

Of course once you have got your menu, you can tweak the content to your liking. Watch this video by Shakeeb Rahman on Mega Menu's. Unfortunately I was not able to find a recording of the webinar from ODTUG Learn from Home Series in which Shakeeb demonstrates what you can do with Mega Menu's in great detail. 

Happy APEXing

Oracle APEX Geolocation 1: How to get your location

-->

Some decades ago people used to work in an office. And occasionally they went on business trips. Nowadays users are much more mobile. They can work from home, form the office or from a field location. So their location varies and it can be useful information for the applications they use.The location might be tied to a customer or a fact that should be reported. This series of blogposts will deal with determining the location in Oracle APEX, and how to use and display it:

Part 1:Getting the location

Part 2: Using Oracle Spatial

Part 3: Display location(s) on map

Part 4: Correct locations on map

Part 5: Getting location data from photos

The parts without link are not yet available.

There is an example application available at :  https://github.com/dickdral/apex_location_demo

Getting your location in the browser

In order to retrieve your GPS location in the browser some JavaScript needs to be applied:

function getLocation() {

  if (navigator.geolocation) {

    navigator.geolocation.getCurrentPosition(function(position) {

        alert(`Latitude: ${position.coords.latitude} \n` +

              `Longitude: ${position.coords.longitude}`);

    });

  } else {

    alert("Geolocation is not supported by this browser.");

  }

}

The most important line is the call to navigator.geolocation.getCurrentPosition.

 This is the function to retrieve the GPS location. As this may take several seconds the function is asynchronous.  The function to be performed after retrieval of the location is the argument for the function. There also is a check when the function is not available.

After the call JavaScript will immediately continue with others tasks. Otherwise the page would hang until the function returns.

NB The function can only be called from a https web site.

Accuracy

In ideal situations the accuracy of the location is within a few meters. In less favorable situations this might expand to tens of even hundred of meters. Especially in an urban environment with many high buildings the visiblity of the GPS sattelites is less good and the accuracy is less accordingly.

More information on GPS accuracy can be found  here

Privacy

Privacy can be an issue when you retrieve and store the location of employees, customers etc. The privacy legislation differs per country.

As of May 2018 the General Data Protection Regulation is issued by the EU. This law contains high penalties for offenders. It is wise to take notice of the privacy regulations in the countries where the application will be used.

Getting location using a APEX plug-in

For APEX developers it is not necessary to write JavaScript to retrieve the GPS location. You can use the Store location plug-in that can be found on https://apex.world.

After importing the plug-in in your application, you can reference it in a dynamic action. All you need to fill in are the APEX items to store the latitude and longitude once the location is retrieved.

To define follow-up actions a custom DA can be defined acting on the event location-retreived.

Happy apexing ;-)

Missing grid layout attributes for APEX items

Whilw preparing for a new presentation I created a new application using the APEX Create Application Wizard and started to create pages and adding items.
While doing so I noticed I missed the usual Grid layout attributes in the Layout section of the item attributes:

No column info etcetera. :-(.
I have spent at least an hour looking for a setting that governs this behavior. Without a result...
Also Google did not provide me with the right answers.

Until I created a new application with all the optional pages. When I set out to examine one of these pages I noticed that the region in the Breadcrumb position did not have the grid layout attributes either.
Going back to my other application I saw that all the regions were created in the Breadcrumb Bar position...
So moving the regions to the Content Body give me back my Grid Layout options (I really missed them).

Happy APEXing

Internet Explorer does not show content of Modal Dialog

In my current assignment I develop using either Firefox or Chrome. But the organization also uses Internet Explorer 11. 

So this morning one of the testers calls me and shows an empty Modal Dialog in Internet Explorer. When using Firefox or Chrome the Modal Dialog shows the expected content. So where is the difference. 

So I reproduce the situation in the Development environment and there the same behavior shows when using Internet Explorer: an empty modal dialog. After opening the Developer Tools the cause shows in the console window: a Javascript error. 

Invalid character

The error points to the JS code below:

$( `[headers="${id}"] input[type="checkbox"]`).each(function() {

    if ( $(this).prop('checked') != checked_status ) {

        $(this).click(); 

    }

});

In the above code snippet a template string is used enclosed with the backtick character (`). You can find more information on this technique on https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Template_literals. And at the end of the page there is a compatibility table, in which it is clear that Internet Explorer does not support this feature. 

 

It is clear that this feature is not supported by IE :-(. 

After a change of the code to: 

$( '[headers="'+id+ '"] input[type="checkbox"]').each(function() {

the modal window showed its content again in IE. 

So when coding for IE be sure not to use too modern JavaScript. Or use a tool like Babel that supplies backward compatibility. 

Pff...

But still, it is quite tricky that a JavaScript error on startup prevents the whole APEX page to be displayed. 

Happy APEXing