This post is the summary of a presentation held at KScope 2015. You will find links at the bottom to download the presentation and the example application containing all the code.

When using a web application on a smartphone a slow network can be really annoying. Especially moving to another web page can take more than a few seconds.
In mobile development often Single Page Applications are the answer to speed up the application. Because the whole application is on one page the only requests to the server are for getting data or submitting transactions.

Because my Apex smartphone application performed badly I wanted to convert it to a Single Page Application. As this is not supported by Apex the actions needed had to be coded manually in PL/SQL and JavaScript.

The Apex architecture relies strongly on page refreshes. Most of the processing is done when rendering the page or when submitting a page. All this processing can not be used in a SPA because it is rendered only once and never submitted (the Apex way). The image below shows which part of the Apex functionality is available.

There Dynamic Actions come to the rescue. They enable us to execute PL/SQL from an Apex page without having to code all the plumbing underneath ( JavaScript / Ajax / Serverside actions ).

So the way to go is to:
– create one page with all the regions we want on it
– disable all fetch processes
– replace page links with Javascript calls
– implement transactions with Dynamic Actions
– using JavaScript to handle (error)messages

The sample application contains some generic JavaScript functions for region management and messaging. On top of these functions for each region a set of specific JavaScript functions is built.

Much if not all of the code written could be generated from the Apex repository. It is possible to replace the manually coded PL/SQL performing the transactions by generic code based on the Apex repository. Also validations could be combined with this code. All the page links can be replaced by JavaScript calls. The messaging framework is already provided.

I think this way it is possible to generically create Single Page Applications with Apex, without all the manual coding described in the presentation. Only, I have not got the time to code it. Besides, it would be better if it were built into Apex, wouldn’t it? Then everybody could benefit. Well Apex team, what about another challenge?

More information about the exact implementation is available in the presentation: available shortly
You can also download the example application ( Apex 5, only the EMP table needed ) :
Be sure to check the supported objects when importing because the application depends on a package spa_pck.sql. 

Happy Apexing,
Dick Dral

Source Article from

In the Oracle database you can use pipelined functions to improve performance or decrease complexity. With the table function a pipelined functions can be called:

select * from table(pf_demo(10));

This way it is not possible to wrap the parameterised pipelined function in a view. You can only create a view for a specific parameter value:

create view pf_demo_view  as select * from table(pf_demo(10));

This is not very useful. Luckily there is a way to pass parameters to a pipelined function within a view. This solution uses package variables to store the parameter values and setter functions to set the values.
In our example we create the pipelined function in a package pf_demo_pkg. In the case of NULL-arguments the values of package variables are used. We create the view with a call to the pipelined function without parameters. The view can be queried with a call to the setter function(s) in the where clause. In the case of NULL-arguments the values of package variables are used. We create the view with a call to the pipelined function without parameters. The view can be queried with a call to the setter function(s) in the where clause. This way you can query the
view from PL/SQL.

create view pf_demo_view  as select * from table(pf_demo());

  for r in ( select * from pf_demo_view ) loop
  end loop;

Now there is also a solution to use such a view from SQL. This solution utilizes the fact that the where clause of a query is interpreted before the from clause. So when we call the setter(s) in the where-clause the view will pass these parameters to the piplined function. You can see this in action in the code below:

SQL create  type emp_rowtype
  2   as object
  3   ( empno number
  4   , ename varchar2(10)
  5   , hiredate date
  6   , deptno number
  7  );
  8  /

Type created.

SQL create  type emp_table_type as table of emp_rowtype;
  2  /

Type created.

SQL create or replace package pf_demo is
  3    function set_deptno ( p_deptno in number) return number;
  5    function emp_pf (p_deptno in number default null)return emp_table_type  pipelined;
  7  end;
  8  /

Package created.

SQL  create or replace
  2  package body pf_demo is
  4    g_deptno    number := null;
  6    function set_deptno ( p_deptno in number) return number is
  7    begin
  8    g_deptno := p_deptno;
  9         return(p_deptno);
 10    end;
 12    function emp_pf (p_deptno in number default null)return emp_table_type  pipelined is
 13    cursor c_emp (cp_deptno number) is
 14    select empno
 15         , ename
 16         , hiredate
 17         , deptno
 18    from emp
 19    where deptno = cp_deptno
 20   ;
 21   r_emp     c_emp%rowtype;
 22   r_rec     emp_rowtype;
 23   l_empno     number := null;
 24   l_ename     varchar2(10) := null;
 25   l_hiredate  date := null;
 26   l_deptno    number := null;
 27  begin
 28   l_deptno := nvl(p_deptno,g_deptno);
 29   open c_emp(l_deptno);
 30   loop
 31     fetch c_emp into  l_empno
 32          ,  l_ename
 33          ,  l_hiredate
 34          ,  l_deptno
 35     ;
 36     exit when c_emp%notfound;
 37     r_rec := new emp_rowtype
 38           ( l_empno
 39           , l_ename
 40           , l_hiredate
 41           , l_deptno
 42           );
 43     pipe row (r_rec);
 44   end loop;
 45   close c_emp;
 46   return;
 47   end;
 49  end pf_demo;
 50  /

Package body created.

SQL select * from table(pf_demo.emp_pf(10));

     EMPNO ENAME      HIREDATE      DEPTNO                                      
---------- ---------- --------- ----------                                      
      7782 CLARK      09-JUN-81         10                                      
      7839 KING       17-NOV-81         10                                      
      7934 MILLER     23-JAN-82         10                                      

SQL select * from table(pf_demo.emp_pf()) where pf_demo.set_deptno(20) is not null;

     EMPNO ENAME      HIREDATE      DEPTNO                                      
---------- ---------- --------- ----------                                      
      7369 SMITH      17-DEC-80         20                                      
      7566 JONES      02-APR-81         20                                      
      7788 SCOTT      09-DEC-82         20                                      
      7876 ADAMS      12-JAN-83         20                                      
      7902 FORD       03-DEC-81         20                                      

SQL create view pf_demo_view as select * from table(pf_demo.emp_pf());

View created.

SQL select * from pf_demo_view where pf_demo.set_deptno(30) is not null;

     EMPNO ENAME      HIREDATE      DEPTNO                                      
---------- ---------- --------- ----------                                      
      7499 ALLEN      20-FEB-81         30                                      
      7521 WARD2      22-FEB-81         30                                      
      7654 MARTIN     28-SEP-81         30                                      
      7698 BLAKE      01-MAY-81         30                                      
      7844 TURNER     08-SEP-81         30                                      
      7900 JAMES      03-DEC-81         30                                      

6 rows selected.

SQL spool off

Hope you can use this, happy coding


Source Article from

This is a nasty error that can occur when a package is recompiled during an Oracle session. I work now in an environment where this occasionally happens and sometimes minutes of data entry are lost.

 LF      VARCHAR2(10) := CHR(10);

is changed to


To make things even easier I have coded a conversion utility where you can input your constant declarations a retrieve the corresponding functions. You find it here.


Source Article from

Inspired by Dan McGhan, today I launched a new meetup: ORCLAPEX-NL

Everybody speaking enthousiastic about Apex and speaking Dutch is welcome.

Eventhough the home-location is Tilburg, I think most of our meetups will end up to be more in the center of the country.


Tilburg, NL
8 Apex fanatics

Deze groep is voor iedereen in Nederland (en België) die een passie heeft voor Oracle Application Express (APEX) en geïnteresseerd in bijeenkomsten met gelijkgestemden. Zo kun…

Next Meetup

Pizza night

Thursday, Sep 18, 2014, 7:30 PM
5 Attending

Check out this Meetup Group →

The other day I was asked to create a page where a large number of properties should be connected to an object. I created a report consisting of the rows with the property name and a check box. This resulted in a very long and narrow report. All of the data could not be seen without scrolling. So I decided the report should be split into a number of columns. And I wanted to avoid special constructs in Apex and SQL (I have done that before and it was very compex). So I decided to use JavaScript to move the rows. By using JavaScript the Apex transaction mechanism keeps functioning, Apex does not know or notice that anything in the layout of the page has been changed.

I want to achieve the right side situation starting with the left report ( that by the way continues a long way below where the picture ends)

With JavaScript a number of extra table elements are created on the same level as the table element containing the report. The thead element of the original table element will be copied to the other table nodes to provide the same heading and a tbody element will be created to house the rows. After that the rows can be distributed among the tables. With CSS the table elements are positioned next to each other. The only limit to the number of columns is the available horizontal space.
You should call the JavaScript function below at startup or in the refresh event when using partial page rendering.

function reportToColumns ( tabSelector, numCols )
{ var numRows = 0;
  // bereken aantal rijen per kolom
  var rowsPerCol = Math.ceil( ( $(tabSelector+' tr').length-1 )/numCols);
  var baseName = 'reportColumn'; 

  // maak kolommen aan
  for ( i = 2; i <= numCols; i++)
  { $(tabSelector).parent().append('

$(tabSelector+’ thead’).clone().appendTo( $(‘#’+baseName+i) );

// verdeel de rijen over de kolommen
var id = 2;
var dest = ”;
$(tabSelector+’ tr’).each( function(index)
{ if (index > rowsPerCol)
{ dest = ‘#’+baseName+id+’ tbody’;
$(dest).append( $(this) );
numRows = numRows + 1;
// wissel de kolom als rijen per kolom is bereikt
if ( numRows >= rowsPerCol)
{ id = id + 1;
numRows = 0;
} );

I used classes for the layout of a specific report. You might need to change that according to your needs.
To make sure the div’s are placed next to each other a little bit of CSS is needed:

.reportColumn {
    display: block;
    float: left;
    margin-right: 30px;
    position: relative;

You can see this in action right here

This method can be used on either Apex Reports or Apex Tabular Forms. In fact it is not a specific Apex solution and can be used on any HTML table.

Happy Apexing

Source Article from

This post show how to style a link into an iconic button just using CSS. This way the HTML-code remains clean and readable.

Very clean, isn’t it?
The CSS to style it is a bit more complex.

.icon {
  display: block;
  border-radius: 3px;
  color: #FFFFFF;
  font-family: FontAweSome;
  font-size: 20px;
  height: 30px;
  width: 30px;
  line-height: 30px;
  text-align: center;
  margin: auto;
.facebook {
  background-color: #3B5998;
.facebook:before {
  content : "\f09a";

As you see the .icon class does most of the work for us. It takes care of the A-element to be considered as a block, sizes the button etcetera. The .facebook class only takes care of the background color and the character to be used, You can look up this character in the Font Awesome Cheatsheet.

The implement this in Apex a list is created in which the URL’s to be used are entered. The display text should be the name of the class of the button. The template for the list elements is very simple and clean:

As you see the display text is used to indicate the class of the button.

Happy Apexing

Source Article from

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