Creating Single Page Applications with Oracle Apex

Single Page Applications with Apex Dealing with the limitations

Content 2 - Introduction - What is SPA and why do we want it? - Creating a SPA using Apex - Some other aspects of mobile

Dick Dral • Oracle since 1988 (Oracle 5) • Started as classic developer (Forms & Designer), now Apex • Special interest in UI, customizing with CSS and JavaScript • Speaker Dutch Apex conference 2013 (files from Apex) & Apex World 2015 (Smartphone applications with Apex) 4 Who am I?

Smartphone applications with Apex 5 - Always enthousiastic about handheld Devices - iWebkit, ‘app’ with CSS and a bit of JavaScript - Apex HTML structures not compatible => JS restructuring

Smartphone applications with Apex 6 - Dealing with limitations: screen, keyboard and network speed - Smartphone applications can be realy slow on a slow network - First action: Design application to prevent page refreshes - Second action: Single Page Application

What and why 7 - SPA is an application on one web page - Why? Performance - Page refreshes take a lot of time, certainly through thin lines - Money - When you are abroad with costly MBs

How? 9 - All regions on one page - All links replaced by JavaScript - All processing replaced by JavaScript - Handle messages with JavaScript

11 Apex Button: Action: Redirect to URL URL: javascript:emp_form_show(); Navigation 1 JavaScript Function (page definition): function emp_form_show() { show_region('emp_form'); setPageTitle('Employee Form'); clear_items('#emp_form'); } function show_region(regionId) { $('.t-Region').hide(); $('#'+regionId).show(); } function setPageTitle (text) { $('.t-Header-logo span').html(text); $('head title').html(text); } function clear_items(selector) { $(selector).find('input,select,textarea').val(''); }

12 Named Column Report Template: #LIST_ELEMENT# Report Query: select spa_pck.emp_list_element( empno, ename, job, sal, deptno) as list_element from emp Resulting column: <li data-empno="7369” onclick="emp_form_show(7369);"> <div class="main_subject”>7369 – Smith</div> <div class="additional”>Clerk (Research) $ 812 /month</div> <i class="fa fa-chevron-right"></i> </li> Navigation

13 Named Column Report Template: #LIST_ELEMENT# Report Query: select spa_pck.emp_list_element( empno, ename, job, sal, deptno) as list_element from emp Resulting column: <li data-empno="7369” onclick="emp_form_show(7369);"> <div class="main_subject”>7369 – Smith</div> <div class="additional”>Clerk (Research) $ 812 /month</div> <i class="fa fa-chevron-right"></i> </li> Navigation JavaScript Function (page definition): function show_emp_form(empno) { show_region('emp_form'); setPageTitle('Employee Form'); emp_form_clear_items(); $('#P1_EMPNO').focus(); if ( empno ) {apex.item('P1_EMPNO').setValue(empno); apex.event.trigger('#P1_EMPNO','change'); } }

14 Named Column Report Template: #LIST_ELEMENT# Report Query: select spa_pck.emp_list_element( empno, ename, job, sal, deptno) as list_element from emp Resulting column: <li data-empno="7369” onclick="emp_form_show(7369);"> <div class="main_subject”>7369 – Smith</div> <div class="additional”>Clerk (Research) $ 812 /month</div> <i class="fa fa-chevron-right"></i> </li> Navigation JavaScript Function (page definition): function show_emp_form(empno) { show_region('emp_form'); setPageTitle('Employee Form'); emp_form_clear_items(); $('#P1_EMPNO').focus(); if ( empno ) {apex.item('P1_EMPNO').setValue(empno); apex.event.trigger('#P1_EMPNO','change'); } } Dynamic Action On Change on P1_EMPNO: begin select ename , job , mgr , hiredate , sal , comm , deptno into :P1_ENAME , :P1_JOB , :P1_MGR , :P1_HIREDATE , :P1_SAL , :P1_COMM , :P1_DEPTNO from emp where empno = :P1_EMPNO ; End;

Message in generated DIV: <div class="message error” onclick="hide_message();"> Commission may not exceed 25% of salary </div> 15 Messages

JavaScript functions: function process_message() { var text = $('#P1_MESSAGE').val(); var type = $('#P1_MESSAGE_TYPE').val() .toLowerCase() || 'info' ; show_message(text,type); $('#P1_MESSAGE_TYPE').val(''); $('#P1_MESSAGE').val(''); } function show_message(text,type) { $('body').append('<div class="message '+type+'" onclick="hide_message();">'+text+'</div>'); } function hide_message() { $('.message').remove(); } function is_info_message () { var msg_type = $('#P1_MESSAGE_TYPE').val(); return ( msg_type == 'INFO' || ! msg_type ); } 16 Messages

JavaScript functions: function process_message() { var text = $('#P1_MESSAGE').val(); var type = $('#P1_MESSAGE_TYPE').val() .toLowerCase() || 'info' ; show_message(text,type); $('#P1_MESSAGE_TYPE').val(''); $('#P1_MESSAGE').val(''); } function show_message(text,type) { $('body').append('<div class="message '+type+'" onclick="hide_message();">'+text+'</div>'); } function hide_message() { $('.message').remove(); } function is_info_message () { var msg_type = $('#P1_MESSAGE_TYPE').val(); return ( msg_type == 'INFO' || ! msg_type ); } 17 Messages Styling of message box (CSS on page): div.message { background-color: #5cb85c; color: white; text-align: center; padding: 5px; min-height: 30px; width: 60%; margin-left: 20%; position: fixed; top: 0; z-index: 1100; } div.message.warning { background-color: #f0ad4e; } div.message.error { background-color: #d9534f; }

JavaScript functions: function process_message() { var text = $('#P1_MESSAGE').val(); var type = $('#P1_MESSAGE_TYPE').val() .toLowerCase() || 'info' ; show_message(text,type); $('#P1_MESSAGE_TYPE').val(''); $('#P1_MESSAGE').val(''); } function show_message(text,type) { $('body').append('<div class="message '+type+'" onclick="hide_message();">'+text+'</div>'); } function hide_message() { $('.message').remove(); } function is_info_message () { var msg_type = $('#P1_MESSAGE_TYPE').val(); return ( msg_type == 'INFO' || ! msg_type ); } 18 Messages Styling of message box (CSS on page): div.message { background-color: #5cb85c; color: white; text-align: center; padding: 5px; min-height: 30px; width: 60%; margin-left: 20%; position: fixed; top: 0; z-index: 1100; } div.message.warning { background-color: #f0ad4e; } div.message.error { background-color: #d9534f; } Fading of INFO message box (CSS on page): div.message.info { backgroud-color: #5cb85c; animation-name: message-fade; animation-duration: 4s; animation-fill-mode: forwards; } /* Standard syntax */ @keyframes message-fade { 0% {opacity: 1;} 75% {opacity: 1;} 100% {opacity: 0;} }

19 Delete Dynamic Action On Click on Delete button: Step 1: PL/SQL Action begin delete emp where empno = :P1_EMPNO ; if sql%rowcount = 1 then spa_pck.set_message('INFO’ ,'Employee record deleted'); else spa_pck.set_message('WARNING’ ,'Employee record NOT deleted'); end if; exception when others then spa_pck.set_message('ERROR',sqlerrm); end; Apex Button: Action: Defined by Dynamic Action Dynamic Action On Click on Delete button: Step 2: JavaScript Action if ( is_info_message() ) { emp_list_remove($(’#P1_EMPNO’).val()); show_emp_list(); process_message(); } else { /* stay on emp_form page */ process_message(); } Javascript function definition: function emp_list_remove(empno) { $('#emp_list li[data-empno=’ +empno+']').remove(); } }

20 Delete Dynamic Action On Click on Delete button: Step 1: PL/SQL Action begin delete emp where empno = :P1_EMPNO ; if sql%rowcount = 1 then spa_pck.set_message('INFO’ ,'Employee record deleted'); else spa_pck.set_message('WARNING’ ,'Employee record NOT deleted'); end if; exception when others then spa_pck.set_message('ERROR',sqlerrm); end;

21 Insert / Update Apex Button: Action: Defined by Dynamic Action

22 Insert / Update Dynamic Action On Click on Save button: Step 1: PL/SQL Action declare cursor c is select * from emp where empno = :P1_EMPNO for update of ename ; r c%rowtype; begin open c; fetch c into r; if c%found then update emp set ename = :P1_ENAME , job = :P1_JOB , mgr = :P1_MGR , hiredate = :P1_HIREDATE , sal = :P1_SAL , comm = :P1_COMM , deptno = :P1_DEPTNO where current of c; if sql%rowcount = 1 then spa_pck.set_message('INFO','Employee record updated'); else spa_pck.set_message('WARNING','Employee record NOT updated');

23 Insert / Update Dynamic Action On Click on Save button: Step 1: PL/SQL Action declare cursor c is select * from emp where empno = :P1_EMPNO for update of ename ; r c%rowtype; begin open c; fetch c into r; if c%found then update emp set ename = :P1_ENAME , job = :P1_JOB , mgr = :P1_MGR , hiredate = :P1_HIREDATE , sal = :P1_SAL , comm = :P1_COMM , deptno = :P1_DEPTNO where current of c; if sql%rowcount = 1 then spa_pck.set_message('INFO','Employee record updated'); else spa_pck.set_message('WARNING','Employee record NOT updated'); Dynamic Action On Click on Save button: Step 1: PL/SQL Action part 2 else insert into emp ( empno, ename, job, mgr, hiredate, sal, comm, deptno ) values ( :P1_EMPNO, :P1_ENAME, :P1_JOB, :P1_MGR, :P1_HIREDATE, :P1_SAL, :P1_COMM, :P1_DEPTNO ) ; if sql%rowcount = 1 then spa_pck.set_message('INFO','New employee record saved'); end if; end if; end if; :P1_LIST_ELEMENT := spa_pck.emp_list_element ( p_empno => :P1_EMPNO , p_ename => :P1_ENAME , p_job => :P1_JOB , p_sal => :P1_SAL , p_deptno => :P1_DEPTNO ); exception

24 Insert / Update Dynamic Action On Click on Delete button: Step 2: JavaScript Action if ( is_info_message() ) { emp_list_show(); process_message(); emp_list_add(); } else { process_message(); } Javascript function definition: function emp_list_add() { var html = apex.item('P1_LIST_ELEMENT').getValue(); var empno = apex.item('P1_EMPNO').getValue(); var old_element = $('#emp_list li[data-empno="'+empno+'"]'); if ( old_element ) { new_element = $(html).insertBefore( old_element); $(old_element).remove(); } else { $('#emp_list ul.list').prepend(html); } }

25 Security - Login Page = Separate Page - Apex login implies submit - After expiration of session good error message in Apex 5: - Your session has expired - ( in Apex 4 it was a cryptic JavaScript error) - Reloading is logging in again

Conclusion 26 - SPA with Apex is possible - Quit a bit of effort - Native SPA from Apex ? - Do it yourself - Download at dickdral.blogspot.nl/2015/06/

Alternative Data Entry 27 - Easy data entry - Pre-fill - Autocomplete - Location - Alternative data entry - Touch - Speech

Using touch for time entry 28 - Metaphore of analog clock - Draw the hands on the screen More information : dickdral.blogspot.nl/2015/02/

Data entry with speech 29 - Speech to text native on iOS and Android - Only iOS investigated - Speech recognition is very good, but… always check the result before submitting or sending! - Can be used in all apps using the keyboard so also in exisiting Apex applications

Numbers in speech recognition 30 - Numbers up to 9 are written in characters (like ‘eight’ instead of ‘8’). - For use in Apex these should be converted to real numbers - Amounts can include currency ( ‘four dollar fifty’ yields ‘$4.50’) - In Apex the currency sign should be removed

Date/time in speech recognition 31 - Time uses a dot as separator and can include AM/PM - For use in Apex the right separator should be used and AM/PM should be converted and removed - Dates include month names - Date can be entered more easily by relative indications ( ‘yesterday’, ‘Monday last week’

Using speech recognition in Apex 1/3 32 - In some cases conversions need to be performed ( in on-change DA’s ) - Filling the seperate items using speech recognition is tedious and error prone, because still a lot of keystrokes - Using one sentence to fill several items in a form is efficient and fast - Conversion of entered speech can be done before entering in item => less change

Input: Groceries yesterday at Walmart for $4.45 Identify and replace date: Groceries on 21-6-2015 at Walmart for $4.45 Cleanse number input Groceries on 21-6-2015 at Walmart for 4.45 Identify item content: Groceries on 21-6-2015 at Walmart for 4.45 33 Using speech recognition in Apex 3/4 P1_DATE P1_NAME P1_AMOUNTP1_DESCRIPTION

Using speech recognition in Apex 2/3 34 - In the spoken sentences the content of the items will have to be extracted. - By fixed term: ‘next’ or ‘next item’ - 19-6-2015 next $12.62 next restaurant next pizza and beer - By label of item in form - Date 19-6-2015 amount $12.62 name restaurant description pizza and beer - By special separator words - Pizza and beer on friday last week at restaurant for $12.62 ( description are the first words untill the first separator)

Using speech recognition in Apex 4/4 35 - Add a new voice input items to the form - Conversion can be done in Javascript or in PL/SQL - PL/SQL easier for Oracle developers - JavaScript faster for slow connections - Not all functions can be performed within Javascript ( database lookups for list of values)

Conclusion 36 - Speech input can be used to fill Apex forms - Can be applied to existing forms with minimal effort - Some datatypes need conversion

Contact 37 - E-mail : dick.dral@detora.nl dickdral@gmail.com - Linkedin: nl.linkedin.com/in/dickdral - Twitter : @DickDral - Website : http://www.smart4apex.nl http://www.detora.nl - Blog : http://dickdral.blogspot.nl

Creating Single Page Applications with Oracle Apex

In this document