zondag 8 december 2013

Dynamically showing reports side by side

I always see complex requests as a challenge. A recent example is a request to dynamically display reports side by side. Suppose you want to display a number of reports side by side but at runtime you don't know how much reports will be displayed. Could be 2, could be 4, could be anything. You don't know, it depends on the data or the user request. Difficult? Here's how to solve this.

To make it more understandable, I will demonstrate this with the good old EMP and DEPT tables so make sure you have access to these tables. We will make a page with a tabular form based on the EMP table and the dynamic reports beneath.

First, create the tabular form. Just create a page with a form of type tabular form. Select the EMP table as the base table for this form. Select all the columns from the table.

Here are the steps to create the dynamic reports:

- In the application builder go to the page where you just created the tabular form
- In the regions section, click the add region icon (the "+" sign)
- Select "pl/sql dynamic content" and click next
- Enter a title for the region and click next
- In the pl/sql source text area, enter the following:

declare
  cursor c_col is
    select distinct deptno
    from   emp
    order by 1;
  --
  cursor c_emp (b_deptno in number) is
    select deptno
    ,      empno
    ,      ename
    ,      job
    ,      sal
    ,      comm
    from   emp
    where  deptno = b_deptno;
  --
  cursor c_dept (b_deptno in varchar2) is
    select dpt.deptno
    ,      dpt.dname
    ,      dpt.loc
    from   dept dpt
    where  dpt.deptno = b_deptno;
  --
  r_dept  c_dept%rowtype;
  l_query varchar2(4000);
begin
  sys.htp.p('<table>');
  sys.htp.p('<tr>');
  for r_col in c_col
  loop
    open c_dept(r_col.deptno);
    fetch c_dept into r_dept;
    close c_dept;
    --
    sys.htp.p('<td>');
    sys.htp.p('  <h1>Department: '||r_dept.deptno||', '||r_dept.dname||', '||r_dept.loc||'</h1>');
    sys.htp.p('<table class="uReport uReportStandard">');
    sys.htp.p('<thead>');
    sys.htp.p('<tr>');
    sys.htp.p('<th id="EMPNO">Empno</th>');
    sys.htp.p('<th id="ENAME">Ename</th>');
    sys.htp.p('<th id="JOB">Job</th>');
    sys.htp.p('<th id="SAL">Sal</th>');
    sys.htp.p('<th id="COMM">Comm</th>');
    sys.htp.p('</tr>');
    sys.htp.p('</thead>');
    for r_emp in c_emp(r_col.deptno)
    loop
      sys.htp.p('<tr>');
      sys.htp.p('<td>'||r_emp.empno||'</td>');
      sys.htp.p('<td>'||r_emp.ename||'</td>');
      sys.htp.p('<td>'||r_emp.job||'</td>');
      sys.htp.p('<td>'||r_emp.sal||'</td>');
      sys.htp.p('<td>'||r_emp.comm||'</td>');
      sys.htp.p('</tr>');
    end loop;
    sys.htp.p('</table>');
    sys.htp.p('</td>');
  end loop;
  sys.htp.p('</tr>');
  sys.htp.p('</table>');

end;

First, the script runs through all distinct departments. For each deptno, a HTML column (<td>) within a HTML table is created so that each collection is displayed separately. The line with the classes (uReport and uReportStandard) makes sure that the reports look just as the standard reports.

- Click next
- Click create region

The region is ready. Run the page and use the tabular form to update the department for some employees. Especially when you add more departments and move some employees to the new departments, you will see that the number of reports extends to the right side. You could add a scrollbar to the region by entering the following in the region attributes text field in the attributes section of the region:

style="height: 400px; width:1000px; overflow:scroll"





If you have APEX 4.2, use theme 26 and the template for the scrollable content region. Otherwise, if you are using another theme, create a new template as a copy of the scrollable content region template.

This looks quite good. However, the customer could just ask for an extra requirement that the reports should be responsive. So no scrollbar but instead a region with a report for each department. And when resizing the browser, or displaying on a mobile device, the regions are stacked. In that case, use the following pl/sql code instead of the above code:

declare
  cursor c_col is
    select distinct deptno
    from   emp;
  --
  cursor c_emp (b_deptno in number) is
    select deptno
    ,      empno
    ,      ename
    ,      job
    ,      sal
    ,      comm
    from   emp
    where  deptno = b_deptno;
  --
  cursor c_dept (b_deptno in varchar2) is
    select dpt.deptno
    ,      dpt.dname
    ,      dpt.loc
    from   dept dpt
    where  dpt.deptno = b_deptno;
  --
  r_dept  c_dept%rowtype;
  l_query varchar2(4000);
  l_col   number(2);
  l_num_of_cols number(2) := 0;
  l_rowcount number(2) := 1;
begin
  select count(distinct deptno)
  into   l_num_of_cols
  from   emp;
  --
  if l_num_of_cols > 4
  then
    l_col := 2;
  else
    l_col := 12 / l_num_of_cols;
  end if;
  --
  sys.htp.p('<div id="uOneCol:">');
  sys.htp.p('<div class="apex_grid_container">');
  sys.htp.p('<div class="apex_cols apex_span_12">');
  sys.htp.p('<div class="apex_row">');
  for r_col in c_col
  loop
    open c_dept(r_col.deptno);
    fetch c_dept into r_dept;
    close c_dept;
    --
    if l_rowcount = 1
    then
      sys.htp.p('<div class="apex_cols apex_span_'||l_col||' alpha">');
    elsif l_rowcount = l_num_of_cols
    then
      sys.htp.p('<div class="apex_cols apex_span_'||l_col||' omega">');
    else
      sys.htp.p('<div class="apex_cols apex_span_'||l_col||'">');
    end if;
    l_rowcount := l_rowcount + 1;
    sys.htp.p('<section id="" class="uRegion clearfix">');
    sys.htp.p('  <div class="uRegionHeading">');
    sys.htp.p('  <h1>Department: '||r_dept.deptno||', '||r_dept.dname||', '||r_dept.loc||'</h1>');
    sys.htp.p('  </div>');
    sys.htp.p('  <div class="uRegionContent clearfix">');
    sys.htp.p('<table class="uReport uReportStandard">');
    sys.htp.p('<thead>');
    sys.htp.p('<tr>');
    sys.htp.p('<th id="EMPNO">Empno</th>');
    sys.htp.p('<th id="ENAME">Ename</th>');
    sys.htp.p('<th id="JOB">Job</th>');
    sys.htp.p('<th id="SAL">Sal</th>');
    sys.htp.p('<th id="COMM">Comm</th>');
    sys.htp.p('</tr>');
    sys.htp.p('</thead>');
    for r_emp in c_emp(r_col.deptno)
    loop
      sys.htp.p('<tr>');
      sys.htp.p('<td>'||r_emp.empno||'</td>');
      sys.htp.p('<td>'||r_emp.ename||'</td>');
      sys.htp.p('<td>'||r_emp.job||'</td>');
      sys.htp.p('<td>'||r_emp.sal||'</td>');
      sys.htp.p('<td>'||r_emp.comm||'</td>');
      sys.htp.p('</tr>');
    end loop;
    sys.htp.p('</table>');
    sys.htp.p('</div>');
    sys.htp.p('</section>');
    sys.htp.p('</div>');
  end loop;
  sys.htp.p('</div>');
  sys.htp.p('</div>');
  sys.htp.p('</div>');
  sys.htp.p('</div>');
end;


The difference with the first code snippet is that here a number of div's are used to get the responsive behaviour. For the responsiveness, a 12-column layout is used. If two regions are displayed, the two regions get the class apex_span_6 (so 12 divided by 2 columns). If three regions are displayed, the three regions get the class apex_span_4 (so 12 divided by 3 columns). The first region also gets the alpha class, the last region also gets the omega class.

How do I know this? Well, create a new application with theme 25 (the responsive theme) and create a number of reports and display them side by side. Then use the inspect element or view source option from your browser to check what HTML code is generated by APEX.

Run the report. Resize the browser so that you can see that the reports will be stacked when the browser's width is too small to show them all side by side.



vrijdag 1 november 2013

Using jQuery selectors in dynamic actions

When you want to control multiple items with your dynamic action, you can make use of jQuery selectors. This can be very handy when you want to enable or disable a number of buttons on your page but want to leave other buttons untouched.

Suppose you have a resume application where you can add or edit a new resume for an employee, and you can edit the skills and the work history of the employee.




In the application it is possible to maintain multiple resumes, for example in two languages. First select an employee in the Employee select list and then select a resume. When no employee and no resume is selected, no button at all will be enabled. When an employee is selected but no resume, only the New Resume button will be enabled. When an employee and a resume are selected, all buttons will be enabled.

To implement this, we will create two dynamic actions: one for the employee select list and one for the resume select list. It is assumed that you already have a page with the items as displayed in the picture. So, two list items, one for the employees and one for the resumes. And four buttons, one for a new resume, one for editing a resume, one for editing skills and one for editing work history.

- In the Buttons section, click on the New Resume button
- In the Attributes section, enter "BT_EM-1" in the static id text field
- Click "Apply Changes"



- In the Buttons section, click on the Edit Resume button
- In the Attributes section, enter "BT_RM-1" in the static id text field
- Click "Apply Changes"
- In the Buttons section, click on the edit skills button
- In the Attributes section, enter "BT_RM-2" in the static id text field
- Click "Apply Changes"
- In the Buttons section, click on the edit work history button
- In the Attributes section, enter "BT_RM-3" in the static id text field
- Click "Apply Changes"

Instead of numbers, you could also use names that cover the button's function. So now we have four buttons with each having an id, which means that they can be found in the Document Object Model (DOM). Dynamic actions can also find the objects in the DOM and there are several ways to do so.


- In the dynamic actions section, click the "Add" icon
- Enter a name, for example "handle_employee_list"
- Click "Next"
- In the event select list, select "Change"
- In the selection type select list, select "Item"
- In the item text field, enter the name of the employee select list, you can use the icon next to the text field to find the exact name
- In the condition select list, select "Is not null"
- Click "Next"
- In the action select list, select "Enable"
- Make sure the fire on page load and the generate opposite false action checkboxes are checked
- Click "Next"
- In the selection type select list, select "jQuery selector"
- In the jQuery selector text field, enter "[id|='BT_EM']"

- In the dynamic actions section, click the "Add" button
- Enter a name, ie "handle_resume_list"
- Click "Next"
- In the event select list, select "Change"
- In the selection type select list, select "Item"
- In the item text field, enter the name of the resume select list, you can use the icon next to the text field to find the exact name
- In the condition select list, select "Is not null"
- Click "Next"
- In the action select list, select "Enable"
- Make sure the fire on page load and the generate opposite false action checkboxes are checked
- Click "Next"
- In the selection type select list, select "jQuery selector"
- In the jQuery selector text field, enter "[id|='BT_RM']"
- Click the create dynamic action button


We have two dynamic actions now. One is to enable/disable the new resume button and the other one is to enable/disable the edit resume, edit skills and edit work history buttons. Run the page and check if everything works. On entering the page for the first time, both select lists are empty and all buttons are disabled. Select an employee and check that the new resume button will be enabled, but the other
buttons not. Select a resume and check that also the other buttons will be enabled.

The big secret is off course the jQuery selector. The syntaxis tells that the selector should be between square brackets and starts with the name of the attribute. In this case we use the "id" attribute. After the pipe comes the matching string. BT_RM means that all objects are selected where the id attribute has the value 'BT_RM' or at least starts with 'BT_RM'. Everything in the id attribute before the hyphen (-) is matching, so BT_RM-1, BT_RM-2 and BT_RM-3 are all matching id's and thus will be affected when the resume select list changes. The SQL equivalent would be "where id like 'BT_RM%' ".

As you may know, there are two types of buttons in APEX. Buttons in region positions and buttons among items in the region. The recipe here is about buttons in region positions. In case you created buttons as items in the region, you don't see the static id text field. In that case you can prefix the name of the button with 'BT_RM-'. For such buttons, the name will appear in the id attribute.



For more information on jQuery selectors, go to http://api.jquery.com/category/selectors


dinsdag 29 oktober 2013

Oracle APEX Cookbook second edition published today!

I'm proud to announce that our second book has been published today. The Oracle APEX Cookbook second edition, published by Packt. I wrote it together with Michel van Zoest. We both wrote also the Oracle APEX 4.0 Cookbook in 2010. We took the original APEX 4.0 Cookbook and added a number of new recipes, especially for APEX 4.2. Creating mobile applications, using HTML5 with CSS3, touch screen events, RESTful webservices are all new items in this book. We also tried to solve a number of issues that were submitted by reader queries.


Writing a book is a tough job. It's almost like a project. First, you start writing the drafts which have to be finished before a certain deadline. Take 1 month per chapter. When the draft is delivered, it will be reviewed by external reviewers. That could be well-known persons from the Oracle APEX community. After the review of the external reviewers, we will get time to address all the comments that are written by the reviewers. And once that is done, a technical editor will do a check whether everything is OK. The draft will also be checked upon spelling, grammar and so on. In the final stage we receive the chapters in PDF format. This is almost how the contents of the book will look like. Changes are only accepted if it will not affect the layout. And finally, once that is done, the book is ready to be published. Last thing to do is to deliver the code bundle, containing all the scripts and examples shown in the book.

Oracle APEX Cookbook second edition is avaible at bookstores, among others Packt and Amazon.

zondag 27 oktober 2013

APEX Listener 2.0 with Glassfish 3.1.2 - my journey

Recently I tried to install the APEX Listener 2.0 on a Glassfish 3.1.2 application server. It all runs on Red Hat Enterprise Linux  server 5.9 in a virtual server at my company. I wanted to test whether it was indeed possible to download a report to PDF format. Before APEX Listener 2.0, you needed a separate server like BI Publisher or Apache FOP but now with 2.0, you don't need that anymore. Just install the listener 2.0, login to the internal workspace and select "Oracle APEX Listener" in the print server list box in the instance settings and that's all! Unfortunately I didn't even manage to install the new Listener.

Here's what I did:

- Download APEX Listener 2.0 from otn.oracle.com to my Windows laptop.
- unzip the download
- scp the apex.war file to the virtual machine.
- start putty and logon to the virtual machine
- Execute java -jar apex.war
- Enter the name of the configuration directory
- enter the other settings like host, name of the user (APEX_PUBLIC_USER) and password and so on
- create a image archive: java -jar apex.war static /opt/sw/oracle/apex/images
- login to the Glassfish admin console
- deploy the apex.war and i.war

After the deployment, the Engines column in the applications overview page didn't show "web" for the newly deployed apex.war. And when I tried to launch the application I got an error message.



By the way, there were more APEX listener deployments on that machine. For every OTAP environment, there was a listener installed. All those listeners were version 1.4. Deploying the new listener into an existing 1.4 configuration automatically updates it to 2.0 but I didn't want to touch the other listeners, as I only wanted to test the new listener.

Renaming the apex.war file into apex-2.war and using another name for the deployment in the context root did help me a little bit. I was able to deploy the new listener and I could see that the Engines column showed "web". However, it still didn't work. On launching the deployment, I got an error message "Not yet configured".

So, all kinds of problems, tried everytning, nothing worked. Then I came up with an idea. Since the listener was downloaded on my Windows machine, this might have caused some problems. So I logged in to the virtual machine using TighVNC, started a browser session and I downloaded the listener via otn.oracle.com. Ok, I know it's possible to download from the command line but this was the easiest way for me. I repeated the necessary steps to configure the listener and tadaa, it worked!

Here's my conclusion: downloading files on Windows and deploying the files on Linux might give some problems. If possible, download files on the same machine as where you want to use them.