User-controlled font size in Apex

In my current assignment I was requested to create a means for the user to control the font size in Apex, looking something like:

A A A


The font would have to change to the size of the A that was clicked on. This is meant to support people who have a hard time reading websites in the 'normal' font size.
The question can be broken down to two questions:

• How can the font size be controlled?


• How can it be controled within Apex

The first question is rather easy. The general font-size can be set using CSS. The entry ' * {font-size 12px; } sets the default font size to 12 px. This is overruled by more specific CSS entries like ' h1 { font-size: 15px; }'.
It is however possible to express the font size relative to the 'default' font-size. This is done by using the unit 'em', for example ' h1 { font-size:1.25em; }. This way the font size of the H1 elements is relative to the default font and changes accordingly when the default font is changed. So make sure all the font size in the CSS are expressed in em.


The second question took some more thinking. It is not so very hard to change a css property programmatically, when a button or link is clicked. But when a new page is rendered I want the change in font size to be persistent.
For this an Application Item G_FONT_SIZE is used. The following line is entered in a style section in the page template behind the CSS inclusions:

* { font-size:&G_FONT_SIZE.px; }

This makes sure that the ´default´ font size is set to the value in G_FONT_SIZE (make sure this item always has a value!).
To make this font size work for all the elements on your page make sure that the other font-size declarations are expressed in em's.

The biggest challenge is to change the value of G_FONT_SIZE once a font size is chosen. This means that the session state of Apex needs to be changed. I have given the use of Dynamic Actions a thought because it is very easy to change the session state by calling a PL/SQL process. But this would imply that I would have to create three items that I should put on Page Zero with Dynamic Actions etc... in other words a quite complex solution with lots of elements.
In the end I came up with a more simple solution with less 'moving' parts. Clicking on one of the font size selectors resends the URL to the browser with a request FONTSIZExx in which xx is the font size. So a request FONTSIZE18 will result in a font size of 18px. The request is handled by an Application Process, that fires OnLoad before Header on the PL/SQL condition v('REQUEST') like 'FONTSIZE%'.
The source of the Process is:

declare
  l_request     varchar2(250) := v('REQUEST');
  lc_fontsize    varchar2(100) := null;
  ln_fontsize    number;
begin
  if l_request like 'FONTSIZE%' THEN
    lc_fontsize := replace(l_request,'FONTSIZE');
    begin
      ln_fontsize := trunc(to_number(lc_fontsize));
      if ln_fontsize between 5 and 100 then
        :G_FONT_SIZE := ltrim(to_char(ln_fontsize))||'px';
      end if;
    exception
      when others then null;
    end;
  end if;
end;

As you can see the script does not just sets the application item but is also checks whether the size is a valid number that is between 5 and 100.

The font size selectors link to:
f?p=&APP_ID.:&APP_PAGE_ID.:&APP_SESSION.:FONTSIZEfont size:&DEBUG.
in which the desired font size in pixels should replace font size.

Happy Apexing

Oracle and unicode characters. Part 1 Content length

In the application I am working on we came across the need to display non-latin characters. We needed to display both Russian and Chinese characters. The Oracle 10g R2 database is configured for UTF-8. Our application is built in Application Express 3.1. Our charts are generated by outputting SVG from PL/SQL. Also PowerPoint presentations are generated through a Java servlet using Apache POI. This servlet is called from PL/SQL.

The non-latin character should be shown in the Apex application, the SVG charts and the PowerPoint output. The Apex application turned out to be no problem. The other forms of output however have cost me a considerable amount of time to solve. During this quest I stumbled over several problems for which I could not easily find the answers using Google. This surprised me, I expected that more people would have had the same problems already.

But this gives me more reason to writes these posts adressing several problems I encountered.

This postwill deal with the content-length of a stream with multi byte from PL/SQL. The next post will discuss which font to use and how to use it. The last post will be about the XML encoding of UTF-8 characters from PL/SQL.

The database is configured for UTF-8 so there is no problem storing the characters in unicode. The data is delivered in UTF-8 files and these characters are loaded into the database using SQL*Loader without any problem.

Sometimes the data is delivered as an Excel file. Then the file can be opened in Excel and saved as Unicode Text. To edit the file we use the Notepad++ editor that supports UTF-8 very well. Do not forget to set the encoding to UTF8 without BOM before saving.

Application Express supports UTF-8 natively. No modification was needed to show the non-latin characters. However in our application we use SVG for charting and a Java servlet for generating PowerPoint presentations. These two have caused me some headaches and a few sleepless nights.

I will discuss the solution for the SVG chart in this post, the generation of the PowerPoint through a servlet will be the subject of my next post.

The SVG charting is coded in PL/SQL. The SVG is built in a CLOB that is output through the HTP package. In the XML header of the SVG the UTF-8 encoding should be specified:

<?xml version="1.0" encoding="utf-8"?>

The UTF-8 characters are added to the SVG without modification. When we generated the first charts with UTF-8 we got errors of incorrect XML. After a lot of searching it turned out that the wrong content length was specified in the download header. We used the value returned by the function DBMS_LOB.GETLENGTH. This function returns however the number of characters in a CLOB. The content-length in the download header should contain the number of bytes.

When all the characters are ASCII characters these numbers are equal. However all of the non-ASCII characters in UTF-8 use more than one byte per character. When there are mutli byte characters in the CLOB the length of the CLOB will be less than the number of characters so the browser will expect less bytes than the total length of the SVG hence it receives not well formed XML. A solution for this problem is to determine the length of the clob in bytes using the following function that utilises the function LENGTHB to determine the length in bytes of a varchar2.

function get_clob_length
     ( p_clob clob
     ) return number is
  l_no_of_pieces     number := null;
  l_bufsize          number := 2000;
  l_string           varchar2(10000) := null;
  l_start            number := 1;
  l_length           number := null;
  l_amount           number := null;
  l_return           number := 0;
begin
  l_length := dbms_lob.getlength(p_clob);
  l_no_of_pieces := trunc(l_length/l_bufsize) + sign(mod(l_length,l_bufsize));
  for i in 1..l_no_of_pieces loop
    l_amount := least(l_bufsize,l_length-l_start+1);
    l_string := dbms_lob.substr(p_clob,l_amount,l_start);
    l_return := l_return + lengthb(l_string);
    l_start := l_start + l_bufsize;
  end loop;
  return(l_return);
end;

Using this function to determine the right value of content-length solved the problem of the not well formed XML. The SVG charts showed, with Russian characters! However, the Chinese characters showed as small squares. Read the next post to find out about the mystery of the missing Chinese characters.

Default value for check box in Application Express

High time to create a new post. Something not too complicated but which still took me some time to figure out. It surprised me that I did not find information on this subject, but maybe I did not look well enough ;-).
In a page I have made with Apex there is check box. The users want this check box to be checked by default. So the check box was created with a static LOV: STATIC2:;Y. To ensure that the check box was checked by default the default value 'Y' was entered.
This did not function as expected. When the check box was unchecked it returned checked after submitting the page. So the users could not uncheck the check box and so in fact it was useless because it always had but one state: checked.
What happens? When a check box is not checked, its value is not posted to the webserver. Because Apex gets no value for the check box item, it sets the value of this item to null. But when the value is null and a default value exists, the item should be set to the default value. Ergo, once checked, always checked.
Once I realised this the solution is quite simple:

• create a hidden item Px_FIRST_TIME with no default value
• create a pre-region process, that checks on the content of Px_FIRST_TIME. When the item value is null, it is the first time this page is called and the check box should be set to checked. Furthermore the Px_FIRST_TIME should receive a value to signal, that after this time it is not the call to this page (in this session).

Happy Apexing

The power of Apache POI

In my current assignment I face users who would like to have their data and graphs in PowerPoint. How do you do that with Oracle tools when you do not want to spend a lot of money on a BI Publisher license? The answer is simple, by using Apache POI , an Java open source library for MS Office integration.
With the examples supplied with the software I was able to generate a prototype servlet in on afternoon! The result was a servlet that accepts a title and (the URL to) an image which it places in a PowerPoint slide. With some extra hours of effort I expanded the prototype to accept an array of URLs and titles. In combination with an Apex that generates parameter driven images the user can fill his shopping basket with images and generate a PowerPoint presentation by pressing a button.
When you want to generate PowerPoint output, do have a look at Apache POI!

Happy Apexing

Automatic generation of Oracle pipelined functions

Lately I have done some projects that needed the implementation of Oracle pipelined functions. You would typically use pipelined functions for performance or flexibility (for example when using dynamic SQL).
It works great once you have got the job done, but before that there is a lot of typing involved. I myself always have to Google for an example of which objects to create and how it should be done. Then at least an hour further I have got my pipelined function running. This, as a real developer, made me wonder whether is would be possible to automate this process. And after some hours of thinking, I found a solution.

Each pipelined function is based on a table like structure, so we can start with a table or a view, say the known table DEPT. With the definition of this table we can build the rowtype object.

create or replace  type dept_rowtype 
 as object 
 ( deptno number(2,0) 
 , dname varchar2(14) 
 , loc varchar2(13) 
);

On the rowtype object we need to define a tabletype object, that easy (and short):

create or replace  type dept_table_type as table of dept_rowtype;

Then we need to write our function, that is declared returning the tabletype by in fact returning a lot of rowtypes. This is, even for a small table like DEPT quite a lot of typing.

create or replace  function dept_pf return dept_table_type  pipelined is 
  cursor c_cur is 
   select deptno 
       , dname 
       , loc 
    from dept;
  r_rec           dept_rowtype; 
  l_deptno number(2,0) := null;
  l_dname varchar2(14) := null;
  l_loc varchar2(13) := null;
 begin 
    open c_cur; 
   loop 
 fetch c_cur into  l_deptno 
       ,  l_dname 
       ,  l_loc 
  ; 
     exit when c_cur%notfound; 
r_rec := new dept_rowtype       ( l_deptno 
       , l_dname 
       , l_loc 
       ); 
    pipe row (r_rec); 
   end loop; 
   return; 
 end; 
/

At last we need a view to show the data:

create or replace  view dept_view as select * from table(dept_pf());

This code is all be generated automatically on the basis of a table definition. You can reach the Apex application to generate all this code with this button:



It's really easy and fast!
Enter the name of the table, which is used to generate the names of the other objects. Then go to the Object Browser and download the column definitions. Cut the Name and Datatype columns in Excel and Paste them in Column Definition. Now hit the Generate button and presto you have got your scripts. Run them in SQL*Plus or TOAD and you have got your pipelined function working within a few minutes. Now you can query the view based on the function.
Off course you would like to perform some changes on the function itself, because you did not do all this just to get the same result as querying the table directly ;-).
But you did not have to do all the typing,

Hope you enjoy the generator, let me know what you think of it!

Dick Dral

PS A new version of the generator is available here

Pages Under Construction

As I am starting my own business I am building a new site (www.detora.nl). My hosting provider provides a web site with static files, PHP and MySQL. The last two are not my cup of tea, so I decided to create a site with static HTML files for a start. I took my ASCII editor and set out to create the files. A large part of the pages show more or less then same on every page. Do I have to copy this theme on each page? How do I cope with changes in the design, that cann't be implemented in CSS?

There must be tools available for this. But I am not in to Dream Weaver, Frontpage and I don't want to invest time in a new tool if I don't have to. So I decide to use Application Express to build the site. And it works!

I have implemented the basic theme in a page template. Most of the site is formatted using CSS so I have created a very basic templates for a Region (just showing a title and the HTML content) and some list template for the various menus througout the site. The general menus are placed on page zero.

This way I had my site up and running quite fast. There is only one problem. It's run in Application Express and it should be static files. This can be solved by saving the webpages to files (use Show source and save the content of that!) and changing the links. Normally a Apex link has the format f?p=&APP_ID.:200:&APP_SESSION.. This is not very useful, because you cannot extract anything like a filename from this. So the links I created use application and page aliases resulting in a link like f?p=detora:about_detora:&APP_SESSION.. If you clip the part around the page alias and augment it with .html you have a perfectly usable link! Make sure you save the pages under there page alias name!

Off course this work is tedious and error prone, so I would not be an IT professional if I would not set out to automate this. It must be possible to write a PL/SQL procedure that reads the pages from the Apex dictionary, accesses each page through UTL_HTTP, replaces the links and writes the static file for the page. To be continued...

One nice thing about using Apex is the possibility to show objects conditionally. I use this feature more and more. It can be used for example to show an "under construction" image on pages that have not been filled yet:

• create a region on page 0 containing a nice "Under construction" image


• put a condition on it of type "NOT exists (SQL query returns no rows)"


• add a query to this condition that returns the regions of the current page:



select p.name, r.plug_name
from   flows_020200.wwv_flow_page_plugs   r
     , flows_020200.wwv_flow_steps p
where  p.flow_id = &APP_ID.
  and  r.flow_id = &APP_ID.
  and  p.id = r.page_id
  and  p.id = &APP_PAGE_ID.


In this way the "under construction" region will only be shown on a page that does not have any regions.Just make sure you create empty pages for all the links on the site!

Have fun creating your own site with Apex.

Spread table in Apex

It's been a while since I posted my last blog. I'v been busy in a new job as Apex coach for a large dutch bank. The department I work for used to be an Oracle Designer and Forms shop, but last year they discovered the possibilities of Apex by a proof of concept application.
I have held several presentations for my collegues about Apex. They were impressed by the ease of application development, but they missed some features they use in Oracle Forms. One of them is spread tables. When you have a very large form, a spread table gives the the opportunity to scroll through a table both horizontally and vertically, while the heading and the row context stay in place.
Standard Apex provides you with normal HTML tables. There is no such functionality as spread tables possible, is it...?

When I was googling the internet on this subject I came across this site describing a technique to use spread tables using CSS and a bit of Javascript. It is possible to lock one or more columns to keep the context of the rows visible.
Great solution! The only drawback is that it only functions in IE and from version 5 upward, but the bank I work with has standardised on IE for browsing. As long as this feature is used within the company there is no problem.

But I want to use it in Apex. My idea is to create a new Report Template, that can be used to create spread table reports just like you create normal reports.

I have based my new Report Template on the Alternating colors template. In the Before Rows section a DIV container is started, and the table is assigned the ID spreadtable:

<table cellpadding="0" border="0" cellspacing="0" summary="">
#TOP_PAGINATION#<tr><td>
<div id="tbl-container">
<table id="spreadtable" cellpadding="0" border="0" cellspacing="0" summary="" class="t6standardalternatingrowcolors">

which is ended in the After Rows sec

</table>
</div>
</td>
</tr>

The format of the tablecells is controlled by the CSS. The class locked is assigned to table cells to be locked. The locking of the column headers to the top border of the div is done by :

#table-container th.locked { top: expression(document.getElementById("tbl-container").scrollTop);
position : relative; }

As you see the top position of the header cells is determined by a Javascript expression, which can also contain a custom Javascript function. The expression keyword in this syntax is only evaluated by Internet Explorer, it will not work in other browsers.
The column cells that serve as row context are locked to the left side of the container with the folowing CSS.

#table-container td.locked { left: expression(document.getElementById("tbl-container").scrollLeft);
position : relative; }

The full CSS can be found here
The fact that it is based on an alternating colors table makes the CSS more complicated. Apart from this to obtain a clear separation between scrollable and locked columns I needed to create three new classes xxx_last, having a grey right border.

Next we need some Javascript to assign width and height to restrain the table container and to actually assign the class locked to the table cells we want to lock. For a start we put this code in the pageheader.

function lockColumns(regionDivID, width, height, noOfColumns) 
{ // set DIV properties
  div = scrollDiv(regionDivID);
  div.style.width = width;
  div.style.height = height;
  div.style.overflow = "auto";
  // look for table
  var tables = div.getElementsByTagName('TABLE');
  var table = tables[0];
  var tableRows = table.getElementsByTagName('TR');  //collection of rows
  var className = 'locked';
  for (i = 0; i < tableRows.length; i++)     // loop along the table's rows
  { var tableRow = tableRows.item(i);      
    for (j=0; j < noOfColumns; j++)
    { if (j == noOfColumns - 1 ) { className = className + "_last"; }
      tableRow.cells[j].className = className;  // last column get grey right border
    }
    if (className == 'locked_last') 
      { className = 'lockedalt'; } 
    else 
      { className = 'locked'; }
  }
}

We call this funtion in a HTML region that we put behind the reports region so that we are sure that the region has rendered already.

  lockColumns ( "ST_REGION", "400px", "250px", 2); 

With this call we restrain the report in region with ID ST_REGION to a size of 400x250 px. A row context of 2 columns remains locked in position.

The result is shown on this page - remember it only functions with IE 5 and up. In this page support is added for Ajax pagination. In the Reports template the pagination links are supplied with a call to the lockCols procedure:

<a href="#LINK# lockCols(getRegion('#REGION_ID#'));" class="t2pagination">#PAGINATION_NEXT#<img src="#IMAGE_PREFIX#themes/theme_2/paginate_next.gif" alt="Next"></a>

I am still working on adding this call to the sort-links in the column headers.

Happy Apexing!