One of the options for solving performance issues is the use of pipelined functions. The performance of queries can in some cases be improved dramatically by the use of PL/SQL instead of pure SQL.
The setup of pipelined functions requires however the creation of two object types for the return values and a function in which these object type are filled with values. There is a lot of typing before you can start to create the actual logic.
For this purpose the Oracle Pipelined Function Generator is created. With this tool you can generate a skeleton for the pipelined function with the required types. You can start coding the logic immediately!
The basis of the generation is a table definition with column names and data types.
A script for the following objects is generated :

  • the pipelined function with a query based on the table definition
  • the type to be used in the PIPE ROW command
  • the type to be used as the return type of the function
  • the view to encapsulate the pipelined function

If your pipelined functions recieves parameters you can use the technique with package variables that is described in another blogpost.

The online generation of the objects is available here:

Happy coding,
Dick Dral

PS This generator is an overhauled version of my old generator. The formatting of the create script has been improved, the application’s theme is changed to UT and a Copy to Clipboard button has been added.

Source Article from http://dickdral.blogspot.com/2016/05/a-generator-for-pipelined-functions.html

The other day I noticed a problem in an time registration system built with Apex. One employee could register hours under the name of another employee. This was possible because the application had access to the table with all the registrations and the filtering to the employee was done on the page.
One way to prevent this is to use views refering to application items. On the EMP table we create a view restricting it to one department:

create view emp_for_dept as select * from emp where deptno = v('APP_DEPTNO');

Outside of Apex this view will return no rows. Within an Apex application with the application item APP_DEPTNO set to 10, 20 or 30 the view will return the rows for that specific department. This way it is possible to limit the visible rows in a report.

It gets even better when we create the view with check option:

create view emp_for_dept as select * from emp where deptno = v('APP_DEPTNO') with check option;

The check option prevents rows to be inserted or updated, that do not comply with the where clause of the view. If you have a form based on this view, you cannot change the department. If you try to do so, you will get an error:

So in the case of the time registration system mentioned above the Apex application could be based on views limited by an application item. On logon this application item can be filled with the ID of the employee and she will only see her registrations. Moreover she can only enter new registrations that match the views selection criteria, so only for her own hours.
It is an elegant and easy way of solving a security issue.

Happy apexing,
Dick Dral

Source Article from http://dickdral.blogspot.com/2016/05/using-views-with-check-option-in-apex.html

For the Apex Dashboard Competition I created an World Dashboard. It is based on the demographical and geographical data on all countries of the world. It was a lot of fun to build it and also fun to use it. You can take a look at it using the URL shown below.

http://www.speech2form.com/ords/f?p=AWD

Using it you will discover amazing facts. The dashboard contains data on population, land use, energy use, health and transportation. You can find for example which are the countries with the most doctors or the highest percentage forest. Let yourself be surprised – at least I was. You can also see how your country compares to others in the areas afore mentioned.

In following post I will discuss some of the features I have built in like the Oracle JET Sunburst Chart or the comparison table.

Have fun!

Source Article from http://dickdral.blogspot.com/2016/04/apex-world-dashboard.html

Today I am investigating performance problems on an Oracle Apex page. On of the possible causes are JavaScript DA Actions that are Fired on page load. The following query helps to identify these DA Actions:

select distinct da.page_id
     , da.page_name
     , da.when_event_name
     , case when when_selection_type is not null then 
                    when_selection_type ||':' || when_element || when_region
            else null
       end    as  object_name
     , da.dynamic_action_name
from   apex_application_page_da_acts   act
       join apex_application_page_da   da  on ( da.dynamic_action_id = act.dynamic_action_id ) 
where  act.action_code = 'NATIVE_JAVASCRIPT_CODE'
  and  act.application_name = '[app_name]'
  and  act.page_id = [page_id]
  and  substr(act.execute_on_page_init,1,1) = 'Y'
;

Fill in the name of your application ([appname]) and the ID of the page ([page_id]) and run the query. You will get a list of teh relevant DA Actions that you can check.

By the way, if you are at this point you could also run the query for the DA Fire on Page Load settings and contribute the survey.

Happy Apexing,
Dick Dral

Source Article from http://dickdral.blogspot.com/2016/03/identify-fire-on-page-load-da-actions.html

Do you recognize the situation that you have coded a nice JavaScript Dynamic Action to respond on user input, you run the page and you notice unwanted behaviour when loading the page?
Oh no, forgot to uncheck the Fire on page load option! It happens to me all the time!

In Oracle Apex JavaScript DA steps have the option Fire on page load checked by default, as you will all be aware. PL/SQL steps have it default unchecked.
I stumble regularly over forgetting to uncheck the option. Most of the DA’s I write are about responding on user actions so I normally do not want them to execute on page load. Not unchecking the option results in unwanted behaviour which is not always obvious. One time it has cost me a few hours to come to the conclusion I should uncheck it.

I would like to have the option default unchecked. This ranks high on my Apex enhancement requests list.

To back this I decided to examine how many Javascript DA steps still have the Fire on page load option active in the application I currently work on together with 5 other developers.
This is the result:

The ratio between unchecked and checked is 2,7! A good argument for me to have the option default unchecked.

What about the Apex builder? A query on the Apex repository for application ID’s between 4000 and 5000 results in :

Also in their own work it would be an advantage to uncheck by default!

But how about your application? I am curious what the ratio is in your application.
If you fire the query below you can find out.

select execute_on_page_init, count(*)
from   apex_application_page_da_acts
where  action_code = 'NATIVE_JAVASCRIPT_CODE'
  and  application_name = 'appname
group by execute_on_page_init;

You can fill in the application name at appname or omit this line to query the whole Apex repository.

Please you post the results on www.speech2form.com/da_survey.
If your results also show a majority for the unchecked steps I will try to convince the Apex team of unchecking the option by default for JavaScript DA steps.

#letswreckthistogether ;-).

Happy apexing,
Dick Dral

Source Article from http://dickdral.blogspot.com/2016/03/lets-wreck-default-fire-on-page-load.html

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,

Source Article from http://dickdral.blogspot.com/2015/11/using-apex-auto-complete-item-with.html

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

Source Article from http://dickdral.blogspot.com/2015/11/change-those-icons-in-your-navigation.html

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:

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

Source Article from http://dickdral.blogspot.com/2015/09/speak-to-your-apex-application.html

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

Source Article from http://dickdral.blogspot.com/2015/08/preventing-sql-injection-without-using.html