Flag images in Apex reports and select lists

The founder of apex.world, Juergen Schuster, had a question about using flag images in reports and select lists. As I had used flags in my Apex World Dashboard I responded and pointed him to the dashboard. But that was not an answer to the flags in the select list. I did some investigation and the answer was not so easy to find. But I found it in the end :-) and I want to share this solution with you.

Flags in reports

For the use in reports flag sprites are used. The advantage of using sprites is that you only need two files, one css file and one image file. The image file contains one large image with all the flags and in the css each country has a style pointing to the right location on the image file. So the HTML:

<div class=“flag flag-nl”></div>

results in the flag of the Netherlands:

To implement this in Apex you have to load the files flags.css and flags.png and upload them as static files. Refer to the CSS file on your page.
Now all you have to do is create a div in your report with the right country code. In an Oracle Apex report you can select the country code:

select lower(country_code)  as country_code , country_name from countries;

Then in the HTML section of the country_code column paste:

<div class="flag flag-#COUNTRY_CODE#"/>

The text #COUNTRY_CODE# is replaced by the value in the row.
NB This value should be lower case because the class names are in lower case.

This method has been used in the Apex World Dashboard of which a clip of the country report is shown:

Flags in select lists (only Firefox :-( )

The flag in the select list caused me more headaches. The option element is very limited with regard to styling. No child elements are allowed. The only possibility I found was to use an image as background.
Using the sprite technique above poses a problem. The size of the flag is limited by the size of the element. With the standard classes above you will only see the flag and not the rest of the content. When the width is increased, you can see the content but on the background are the other flags (from the one large image).
I looked in vain into Super LOV and select2 plugins for a solution.

So the only solution left was to collect separate flag images and use them as background for the option elements. Luckily you can find a collection of images easily on the internet. The images from http://flagpedia.net are used. With CSS3 these images are bound to the corresponding option value in the select list ( presuming it is the two character ISO country code that is returned ). A CSS file was created to bind the option values to the corresponding flags.

So all that is needed is to refer to https://www.speech2form.com/assets/img/flags/select_flags.css on your page:

...and add the class flag to your select item:

When you define the country code in lower case as the return value of your select list you will see the flags in front of the country names:

In the CSS files default values for the option element have been set. You can tweak them to get the best appearance for your application. With margin and padding you can influence the spacing of the option elements. In this solution the files reside on a remote server ( http://www.speech2form.com ).

If you want the files in the database you can download the ZIP file here. You can import the file in your application. The option to unzip should be checked.

Reference the static file select_flags.css on your page and you can use the flag images stored in the database.
After a mornings work I find out that the background images for options only work in firefox! Sigh... When a new solution is available I will get back to you.

Happy coding, Dick Dral

A generator for pipelined functions

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 in a package with a query based on the table definition
• the view to encapsulate the pipelined function

In answer to the comment of Denes the separate types have been replaced by types declared within a package

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:

Oracle Pipelined Function Generator

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.

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