By saving public Apex Interactive Reports you can offer various custom reports to users without programming. The user opens the page and can choose the reports from the Reports drop down list.
New reports can be added by defining the new report and then exporting the application and importing it in the production environment. Changing existing reports can in some cases cause double entries in the Reports drop down list. This is because the changed report has received a new ID and therefor the original report is not overwritten.
The following procedure can be run for each relevant page before importing the application. It deletes the existing public reports for a specific application and page.

create or replace 
  procedure delete_public_ir_report
                ( p_app_id      in  number
                , p_page_id     in  number
                ) is
  dbms_output.put_line('Deleting public IR reports for application '||p_app_id||' page '||p_page_id);
  for r in ( select * 
             from   apex_application_page_ir_rpt
             where  application_id = p_app_id
               and  page_id        = p_page_id
               and  report_type    = 'PUBLIC'
    dbms_output.put_line('Deleting report '||nvl(r.report_name,'with id '||r.report_id));
  end loop;

This procedure works in my situation but each situation is different. Be careful to apply this procedure to your environment and test the results thoroughly before applying it to a production environment.

Happy apexing

Source Article from

Lately I have been developing a kind of SaaS website with Apex. Several companies were to be represented in this website and each company wanted to have its own logo and colors. Should be a piece of cake with CSS, shouldn’t it?

But the requirements said rounded corners for tabs and buttons, and support for IE7. That rules out HTML5 :-(.
Using plugins for rounded corners I was not able to get a stable result, so I turned to the good old sliding windows solution using background images. This way the website was stable and reliable. Below two examples of look-and-feel using the same HTML.

The only drawback of this was that a set of 12 images was needed for each company.
Creating these images manually using drawing software is a time consuming, tedious and error-prone process.
So I created a webpage containing SVG-images. The webmaster could enter the colors and generate the images. Then these images could be grabbed and cut on the surrounding dotted lines in GIMP. Sounds a lot like kindergarten, doesn’t it?
This was already better, but still a tedious and error-prone.

Then my 15 year old son –who is a computer programming addict like myself ;-)- came up with a solution. The open source program ImageMagick is able to convert images including SVG to PNG. The only challenge was that it runs on the command line and Apex is webbased!

The solution is to generate a Windows BAT-file that can be run on a client where ImageMagick is installed. In the BAT-file the SVG images are generated with ECHO-statements. After this ImageMagick is called to do the conversion to PNG. With this solution the neccesary images can be generated in a few minutes, just the time needed to enter the colors and to execute the BAT-file. Below is an example of the generation of the image for a button background.

echo <svg height="22px" version="1.1" width="400px" xmlns="http:www.w3.org2000svg">                  >button.svg
echo </svg>      >>button.svg

convert button.svg button.png
convert button.png -transparent white button.png

The echo statements generate the 2 lines SVG file. The first convert statement converts the SVG file to a PNG. The second convert command changes the white background to transparent in order to use the button on other than white backgrounds.

Happy Apexing! (but you can use this for any kind of website)

Source Article from

The weekday in Apex is dependent on of the language set in the Application Properties. For that you cannot rely on TO_CHAR(datevalue,’d’), but should use TRUNC(datevalue)-TRUNC(datevalue,’iw’)+1 to get ISO weekdays, that are independent of the language set. SQL Workshop did not show differences because the language for SQL Workshop remained the same. This made the analysis more difficult as the results of queries in Apex and SQL Workshop were inconsistent.

I noticed this after having changed the language from English (us) to Dutch(nl) in an application for registering the hours spent and declared on assignments.
The screen for registering the declared shows an enterable fields for each day of week to accept the hours worked that day. This screen is showed in the picture below.

Before the change of language this page worked flawlessly. After having set the language to dutch the page did not function like I did before.
At the moment I have the luxury of working four days a week ;-), so I had filled in 8 hours for Monday up to Thursday. After saving this data the application returns to the menu. When I retrieved the page once more, I noticed that the screen showed Sunday up to Wednesday as days.
The data was stored using an offset from the first day of the ISO week. As the ISO week returns the same value regardless of the language the right data were saved.
To retrieve the data a view is used in which I used SUM(DECODE(TO_CHAR(datefield,’d’)),2,hours) AS hours_monday to determine the number of hours worked on Monday. With the language set to English, this yielded the correct result. After the language was changed to Dutch, the value for Tuesday was retrieved, as tuesday is the second day in the ISO week.
The number of the weekday differs on the basis of the language. This may not be a surprise, but it was hard to find, because as it was a remote site I was using SQL Workshop to perform SQL statements to check. And apparently SQL Workshop is set to English, regardless of the language of the application (sounds logical). This meant that in SQL Workshop I still got the right results, while in the application, the days were shifted.
The solution was to calculate the weekday number by referring to the start of the ISO-week. The latter number is independent of the language used. We subtract the start of the ISO-week from a given date (use TRUNC to eliminate the time fraction) and add 1 to get the weekday number. The query

SELECT TRUNC(sysdate)-TRUNC(sysdate,’iw’)+1 FROM dual

will yield the correct (ISO) day of the week.

As you may have noticed I am developing an Apex application for iPhone. For this I use iWebkit, which results in really nice and usable mobile applications. I hope to blog more about this in the near future.

Having fun Apexing

Source Article from

This morning I was developing a page on which some input items could be prefilled from a Before Region Process and should be disabled if containing a value. After some Googling I soon found out that this could be accomplished by setting the disabled attribute.
And indeed, the items with a value appeared correctly with a gray background and non-enterable. But when I submitted the form, the value of the items appeared to be empty.

After half an hour of trying all kinds of things I, examining session state and going through the debug output, I finally found the solution:
– when an item is disabled it’s value is set to empty…

So the solution was not to use the attribute disable, but the attribute readonly in combination with the style “background-color:#CCCCCC”.

With this solution, the session state is retained.
Visually the result is the same, the only difference is that the cursor can be placed inside the item, but no changes to the content can be made.

Happy Apexing

Source Article from

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


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:

l_request varchar2(250) := v('REQUEST');
lc_fontsize varchar2(100) := null;
ln_fontsize number;
if l_request like 'FONTSIZE%' THEN
lc_fontsize := replace(l_request,'FONTSIZE');
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;
when others then null;
end if;

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:
in which the desired font size in pixels should replace font size.

Happy Apexing

Source Article from

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

Source Article from

Laltely 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;
open c_cur;
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;

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

Source Article from

As I am starting my own business I am building a new site ( 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, 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 = r.page_id
and = &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.

Source Article from