Using views with check option in Apex to protect your data

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 = ( select v('APP_DEPTNO') from dual ) 
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

PS For better performance the call to v() is wrapped in a subselect. Thanks for the tip, Andre

Query the view sources like you query your PL/SQL source

While developing I use the view user_source many times each day to search my PL/SQL code.
In this view the source code of stored PL/SQL and triggers are provided but unfortunately the source of views is missing. Furthermore the dictionary view containing the source very old and thus the source column is of type LONG. And you cannot query on a LONG column :-(.
I have found several solutions to this problem, ranging from creating a new table using TO_LOB to convert the LONG column to using the function dbms_metadata.get_ddl ('VIEW', view_name). I don't want to create another table each time, and the solution with dbms_metadata did not perform for me, so I wrote a solution using a pipelined function, exposing the same interface as user_source.
The skeleton for the pipelined function and the types needed was created by the Pipelined function generator I created a long time ago. It still works! I used the same column definitions as the user_source view. So I ended up with these types:

create  type view_source_rowtype 
 as object 
 ( name varchar2(30) 
 , type varchar2(12) 
 , line number 
 , text varchar2(4000) 
);
/

create  type view_source_table_type as table of view_source_rowtype;
/

In the pipelined function DBMS_SQL is used to query user_views and to extract the view name and the source. The LONG column is read in chunks and each chunk is divided into lines using the Apex function apex_util.string_to_table ( I love this function, it is soo useful!). The separate lines are returned to the TABLE function with PIPE ROW including line number. The source code of the function is too long for this blog post, but you can download all the code HERE.
A view is created for easy access to the function:

create  view view_source as 
   select * 
   from table(view_source_pf());

Now you can query this view:

SQL> select * from view_source where name = 'VIEW_SOURCE';

NAME                           TYPE               LINE TEXT                                                                                               
------------------------------ ------------ ---------- ----------------------------------------------------------------------------------------------------
VIEW_SOURCE                    VIEW                  1 select "NAME","TYPE","LINE","TEXT"                                                                  
VIEW_SOURCE                    VIEW                  2    from table(view_source_pf())                                

You can now even query your PL/SQL and view code in one pass, for example if you want to look for the text label:

select * from 
(
 select * from user_source
 union all
 select * from view_source
) 
where  lower(text) like '%label%';

Happy coding, Dick Dral

Apex World Dashboard

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!

BTW The application is available on Github if you want to examine the source code:

https://github.com/dickdral1/world_dashboard

If you want to run the application you have to install the sample data for the competition. You can find that here.

Identify Fire on Page Load DA Actions

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 the 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

Let’s wreck the default Fire on page load option

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:

Execute on Page Load	Number
No	365
Yes	132

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 :

Execute on Page Load	Number
No	86
Yes	36

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

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