Using the Apex Auto Complete Item with JQuery Mobile

For long lists mobile applications the Apex Autocomplete Item is way more suited than a Select List Item.

You know what I mean, when you ever needed to choose a country from a Select List ( > 200 items) and your pick was near the end ( The Netherlands, Unites States ). You have to scroll for 10 or 20 seconds to reach to the desired entry, and all the time you need to pay attention whether or not you have already the desired entry.

In this case it is much more user friendly to deploy the good old Auto Complete Item. Within a few key strokes, usually not more than three, the list of possibilities has been reduced to less than ten entries, and you can easily pick the right value. The only drawback for the programmer is, that the Auto Complete Item returns the description instead of the code, so the code need to be looked up before submitting the form. Not a big price for the gain is user friendliness.

The Auto Complete item is not directly available from the list of item types, you need to choose the option Show unsupported to uncover the item type. On the right side the after pressing Show unsupported is visible.

After including the item and attaching a source query to it, it is already functional:

Okay, we can choose a value, but it does not look right, does it? We cannot read the text of some entries, the pick list is transparent and the item have list style bullets.
To fix this we need to apply a bit of CSS:

.ac_results {
   background-color: white;
   border: 1px solid #cccccc;
}
.ac_results ul {
   margin-top: 0;
   padding-left: 5px;
}
.ac_results li {
   line-height: 30px;
   list-style: outside none none;
}

You can put it as Inline CSS on the page or in the template page, if you want to use it on several pages.
Now the item looks a lot better:

PS Do not use the Date Picker Classic,  also a not supported item type, with JQM. When you put such an item on a JQM page, calling the page provides you an eternal load animation, the page is never shown. 

 Happy apexing,

Change those icons in your Navigation Menu!

Just returned from the DOAG 2015 conference. I really had a good time meeting old friends, making new ones and seeing a lot of impressive and new things on Apex and Oracle.
One thing annoyed me however, and that were the icons in the Apex 5 Navigation Menu. Most of the applications had a menu looking like this, all options using the Folder icon:

While it is so easy to let the menu look like this:

By the way, does anyone know a more suitable fa icon for JSON?

In the Apex builder just navigate to Shared Components > Navigation Menu and select one of the options:

And choose a nice, explanatory icon for Image/Class. The Apex team even provided a list of values with the categorized font Awesome icons. Repeat this for all your menu entries, and you will have a much nicer menu at very little effort.

For the rest keep up the good work ;-).

Happy Apexing

Speak to your Apex application

The development of smartphone web applications hosts many challenges. For me personally the largest challenge is efficient and comfortable data input. Entering data through a small keyboard is a time consuming and unpleasant action for me. For this reason I have been designing and developing alternative ways to enter data. My latest investigations concern speech input. It started with the release of iOS8 that supported speech-to-text for a lot more languages than before. Among them was Dutch, my native language, and you know what? It does a great job!

Speech recognition on iPhone

Recognition is fast and accurate. I am able to ‘speak’ a text message or email without or just with one correction. It has a hard time recognizing names and jargon, so entering this blog post with speech would need a lot of correcting. You do need an active connection to the internet as the spoken words are sent to a server where they are detailed interpretation gets done. As you speak you will see the result of recognition by the iPhone. A fraction of a second later this is replaced by the server interpretation. The dictation function is available in the keyboard.

Any place where you can use the keyboard you can press the microphone and use dictation.
This will take care that the words you speak will be converted to characters that are input into the current input item. You can have more than one language keyboard installed. Use the Globe button to switch keyboards (and language). In iOS8 you can turn on the speech recognition in the Settings > General > Keyboards section:

I have done some tests on the speech recognition of Android devices, and this is pretty good, although it cannot match the iPhone at the moment. Speech recognition within Apex applications You can use the dictation with any Apex application out of the box. Once you have activated the dictation the microphone will be available in the keyboard and keyboard entry can be replaced by speech to text conversion.
There are a few limitations however. Some data types need conversion:

• Numbers below 10 are converted to words, not digits. Amount may contain a currency sign 
• Spoken times might not be in the right format
• Dates will most likely not be in the format expected by Apex. If the spoken text is recognized as a date the month name will be used. 

The items based on these item types will need to implement a conversion for spoken input, so the form needs to be changed.
Furthermore it is not possible to enter controls that do not expose the keyboard. Amongst others these check boxes, radio groups, select lists and HTML5 date and time controls.

Apart from the above it is not practical to enter by speech each item separately. You need 3 taps per item. In most cases keyboard entry will be faster. You will only benefit when entering large texts like remarks or descriptions.

Input of multiple items at once 

As mentioned above the entry of separate items is not efficient. Another way to use speech recognition is by creating a new input item especially for speech entry. The spoken content of this item is split into item content using stop words. This way multiple items can be input by speaking one sentence. Another advantage of this approach is that the necessary processing can be performed while analysing the sentence before placing the values into the items. So the form items them selves need not be changed.
The processing can also be extended to special cases. It enables for example the use of relative dates, like yesterday or Monday last week.

Example form 

I have built a sample Apex application for speech input. This is based on the mobile Apex application that I use on a daily base to register my expenses. To enter an expenses the date, the amount spent, the name of the shop and a description of the purchase should be input. A separate item is available for the speech input.
The values can be entered in one sentence of the following structure:
first item always is the description

• the shop name follows after the stop word at
• the amount is preceded by the stop word for. The amount is preferably entered with currency, for example two euro fifty. This results in the most accurate recognition and formatting
• the date can be entered using the stop word on or as a relative date ( yesterday, Monday last week)

 Except for the description there is no prescribed input order. An example input would be:

Bread and milk at Tesca for $4.65 yesterday 

When leaving the speech input item the sentence is analysed and the content is written to the various input items. The example would result in:

date	date one day before today
shop	Tesca
amount	4.65
description	Bread and milk

The user can check whether all the input is correctly filled. If it’s all right he can submit the data and a new record is created.
Now try it yourself by opening this link on your mobile phone.

http://apex.oracle.com/pls/apex/f?p=vi_demo

You can login with the username password combination guest/welcome. Behind the link you will find an application to enter expenses with speech input. The instructions can be found under the menu item Manual.

When is speech input useful? 

This way of entering is typically not for occasional use. You have to be used to the way the sentence is formed and which possibilities are available. After getting used to entering the form by speech you will not want to enter it through the keyboard any more. Mobile users that have to record their actions regularly could use this kind of entry. This could be for example a salesman, a service engineer or a nurse. For all these workers entering the data directly after the process or as part of the process frees them from filling in paper forms that need to be input on a desktop computer at a later time.

Future developments 

I plan to create a plugin for speech input supporting several languages. With this plugin it will be easy to enable existing Apex forms for speech input.
The current software does not support check boxes or list of values, I will be working on that too.

Please let me know whether you would think such a plugin useful and what use you see for it.

Happy Apexing

Preventing SQL Injection without using bind variables

SQL Injection can be a serious threat to your database. Depending of the setup of your database an attacker could destroy your data or get hold of sensitive data.
Many posts have been written about SQL Injection in Apex and the solution always has been to use bind variables instead of using substitution. There can be cases in which using bind variables is not an option or requires a lot more effort.

One example of this is the use of apex_util.json_from_sql :

declare 
  v_sql varchar2(4000);
begin
    v_sql := q'!select * from emp where ename like '#EMP#' !';
    v_sql := replace(v_sql,'#EMP#',apex_application.x01);
    apex_util.json_from_sql (v_sql);
end;

In the code above you can see that we need to incorporate the parameter into the query. There is no way we can use bind variables. This puts the door wide open to SQL Injection attacks.
However, if we split the parameter into an expression of which the result is equal to the parameter, no harm can be done. One possibility is to take the separate characters of the parameter and concatenate them.

parameter  := 'JON';
expression := 'J' ||'O'||'N';

The expression can be substituted in the query instead of the litteral parameter and you query is safe for SQL Injection.

The following procedure does the trick for you:

  function split_param ( p_param in varchar2) return varchar2 is
    l_return    varchar2(4000) := null;
    quote       char(1)        := chr(39);
  begin
    for i in 1..length(p_param) loop
      if l_return is not null then
        l_return := l_return || '||';
      end if;
      l_return := l_return || quote || substr(p_param,i,1) || quote;
    end loop;
    l_return := ltrim(rtrim(l_return,quote),quote);
    return(l_return);
  end;

This function accepts a string and outputs an expression that returns the same string.
We can use this function to convert the input parameter into an expression. This way the code will not be vulnerable to SQL Injection any more:

declare 
  v_sql varchar2(4000);
begin
    v_sql := q'!select * from emp where ename like '#EMP#' !';
    v_sql := replace(v_sql,'#EMP#',split_param(apex_application.x01));
    apex_util.json_from_sql (v_sql);
end;

I think it cannot be broken anymore. Do you agree or is this code still vulnerable to SQL Injection?

Love to hear from you!

Happy Apexing

Postscript: I have got a reaction on this blogpost by Carsten Czarski that notes that the dbms_assert package also contains functions to prevent SQL Injection. The function dbms_assert.enquote_literal ensures that all quotes within a string are paired, otherwise an exception is raised.
Thank you, Carsten.

Single Page Application with Oracle Apex

This post is the summary of a presentation held at KScope 2015. You will find links at the bottom to download the presentation and the example application containing all the code.

When using a web application on a smartphone a slow network can be really annoying. Especially moving to another web page can take more than a few seconds.
In mobile development often Single Page Applications are the answer to speed up the application. Because the whole application is on one page the only requests to the server are for getting data or submitting transactions.

Because my Apex smartphone application performed badly I wanted to convert it to a Single Page Application. As this is not supported by Apex the actions needed had to be coded manually in PL/SQL and JavaScript.

The Apex architecture relies strongly on page refreshes. Most of the processing is done when rendering the page or when submitting a page. All this processing can not be used in a SPA because it is rendered only once and never submitted (the Apex way). The image below shows which part of the Apex functionality is available.

There Dynamic Actions come to the rescue. They enable us to execute PL/SQL from an Apex page without having to code all the plumbing underneath ( JavaScript / Ajax / Serverside actions ).

So the way to go is to:
- create one page with all the regions we want on it
- disable all fetch processes
- replace page links with Javascript calls
- implement transactions with Dynamic Actions
- using JavaScript to handle (error)messages

The sample application contains some generic JavaScript functions for region management and messaging. On top of these functions for each region a set of specific JavaScript functions is built.

Much if not all of the code written could be generated from the Apex repository. It is possible to replace the manually coded PL/SQL performing the transactions by generic code based on the Apex repository. Also validations could be combined with this code. All the page links can be replaced by JavaScript calls. The messaging framework is already provided.

I think this way it is possible to generically create Single Page Applications with Apex, without all the manual coding described in the presentation. Only, I have not got the time to code it. Besides, it would be better if it were built into Apex, wouldn't it? Then everybody could benefit. Well Apex team, what about another challenge?

More information about the exact implementation is available in the presentation : SPA with Oracle Apex You can also download the example application ( Apex 5, only the EMP table needed ) : http://www.detora.nl/downloads/kscope2015/apex_spa.sql
Be sure to check the supported objects when importing because the application depends on a package spa_pck.sql. 

Happy Apexing,
Dick Dral

Time input by touch movements

Since the early beginnings of the smartphone era I have been thinking about time input by drawing the hands of a clock on the screen of a smartphone. For me this works a lot easier than entering the numbers on the on-screen keyboard. As for intuitive usage, most of us have learned to read the clock in school, so it is very familiar. The only difference is that you 'write' the clock instead of reading it. 

About a year ago I had the time and skills to create a prototype. It functioned but lacked the ‘looks’ and was not very intuitive to use. Lately I had some inspiration to  restyle the original design to make it prettier and more intuitive. I would like to proudly present the result to you:

The image above will not respond to your touch, but the next link will take you to a page where you can try this control.

http://www.detora.nl/tic_demo/tic_demo.html

The control is designed for touch devices. In a desktop browser you can click on the numbers in the analog clock to select the hours or minutes.
If you want to try the control on your phone or tablet you can have an email with a link to the demo page sent to you. Enter your email address below and press the 'Send link by mail'. Your mail client will open and you can send the mail.

Send to email address     Send link by mail
( Your email address is not stored )

Below is an explanation how to use the control:
Touch the clock image next to the time input field and the time input control will appear.

In the center is the analog clock in which the hands can be drawn. Above it you see the digital time with buttons for changing to AM or PM. Below it are buttons to Accept of Cancel. The active hand and the active part of the digital display are indicated by a red color.

Draw the hands starting (approximately) at the center of the clock. To enter 10:15 you start at the center of the clock and swipe towards the 10, then release. The hour in the digital time display above the clock will display 10 and the minutes will have turned red (=active). Then start in the center of the clock again moving toward the 3 this time. When you release you will notice that the digital clock displays 10:15. To change to PM touch the PM button.

If you are satisfied you can press OK to save the time. Cancel returns to the form without saving the time.

I would really like to hear whether you think this control useful. Or maybe you have  suggestions on improving it.

Happy developing,

Using a pipelined function with parameters in a view

In the Oracle database you can use pipelined functions to improve performance or decrease complexity. With the table function a pipelined functions can be called:

select * from table(pf_demo(10));

This way it is not possible to wrap the parameterised pipelined function in a view. You can only create a view for a specific parameter value:

create view pf_demo_view  as select * from table(pf_demo(10));

This is not very useful. Luckily there is a way to pass parameters to a pipelined function within a view. This solution uses package variables to store the parameter values and setter functions to set the values.
In our example we create the pipelined function in a package pf_demo_pkg. In the case of NULL-arguments the values of package variables are used. We create the view with a call to the pipelined function without parameters. The view can be queried with a call to the setter function(s) in the where clause. In the case of NULL-arguments the values of package variables are used. We create the view with a call to the pipelined function without parameters. The view can be queried with a call to the setter function(s) in the where clause. This way you can query the view from PL/SQL.

create view pf_demo_view  as select * from table(pf_demo());

begin
  pf_demo_pkg.set_dept(10);
  for r in ( select * from pf_demo_view ) loop
    ...
  end loop;
end;

Now there is also a solution to use such a view from SQL. This solution utilizes the fact that the where clause of a query is interpreted before the from clause. So when we call the setter(s) in the where-clause the view will pass these parameters to the piplined function. You can see this in action in the code below:

SQL 
SQL create  type emp_rowtype
  2   as object
  3   ( empno number
  4   , ename varchar2(10)
  5   , hiredate date
  6   , deptno number
  7  );
  8  /

Type created.

SQL 
SQL create  type emp_table_type as table of emp_rowtype;
  2  /

Type created.

SQL 
SQL 
SQL create or replace package pf_demo is
  2  
  3    function set_deptno ( p_deptno in number) return number;
  4  
  5    function emp_pf (p_deptno in number default null)return emp_table_type  pipelined;
  6  
  7  end;
  8  /

Package created.

SQL 
SQL  create or replace
  2  package body pf_demo is
  3  
  4    g_deptno    number := null;
  5  
  6    function set_deptno ( p_deptno in number) return number is
  7    begin
  8    g_deptno := p_deptno;
  9         return(p_deptno);
 10    end;
 11  
 12    function emp_pf (p_deptno in number default null)return emp_table_type  pipelined is
 13    cursor c_emp (cp_deptno number) is
 14    select empno
 15         , ename
 16         , hiredate
 17         , deptno
 18    from emp
 19    where deptno = cp_deptno
 20   ;
 21   r_emp     c_emp%rowtype;
 22   r_rec     emp_rowtype;
 23   l_empno     number := null;
 24   l_ename     varchar2(10) := null;
 25   l_hiredate  date := null;
 26   l_deptno    number := null;
 27  begin
 28   l_deptno := nvl(p_deptno,g_deptno);
 29   open c_emp(l_deptno);
 30   loop
 31     fetch c_emp into  l_empno
 32          ,  l_ename
 33          ,  l_hiredate
 34          ,  l_deptno
 35     ;
 36     exit when c_emp%notfound;
 37     r_rec := new emp_rowtype
 38           ( l_empno
 39           , l_ename
 40           , l_hiredate
 41           , l_deptno
 42           );
 43     pipe row (r_rec);
 44   end loop;
 45   close c_emp;
 46   return;
 47   end;
 48  
 49  end pf_demo;
 50  /

Package body created.

SQL 
SQL 
SQL select * from table(pf_demo.emp_pf(10));

     EMPNO ENAME      HIREDATE      DEPTNO                                      
---------- ---------- --------- ----------                                      
      7782 CLARK      09-JUN-81         10                                      
      7839 KING       17-NOV-81         10                                      
      7934 MILLER     23-JAN-82         10                                      

SQL 
SQL select * from table(pf_demo.emp_pf()) where pf_demo.set_deptno(20) is not null;

     EMPNO ENAME      HIREDATE      DEPTNO                                      
---------- ---------- --------- ----------                                      
      7369 SMITH      17-DEC-80         20                                      
      7566 JONES      02-APR-81         20                                      
      7788 SCOTT      09-DEC-82         20                                      
      7876 ADAMS      12-JAN-83         20                                      
      7902 FORD       03-DEC-81         20                                      

SQL 
SQL create view pf_demo_view as select * from table(pf_demo.emp_pf());

View created.

SQL 
SQL select * from pf_demo_view where pf_demo.set_deptno(30) is not null;

     EMPNO ENAME      HIREDATE      DEPTNO                                      
---------- ---------- --------- ----------                                      
      7499 ALLEN      20-FEB-81         30                                      
      7521 WARD2      22-FEB-81         30                                      
      7654 MARTIN     28-SEP-81         30                                      
      7698 BLAKE      01-MAY-81         30                                      
      7844 TURNER     08-SEP-81         30                                      
      7900 JAMES      03-DEC-81         30                                      

6 rows selected.

SQL 
SQL spool off

Hope you can use this, happy coding Dick