OGH APEX World 2014

Last week we attended the the 5th annual APEX World event in Zeist. As every year it was very nice to meet the growing APEX community in the Benelux, combined with some excellent APEX international and dutch presentations.
The  keynote was given  by Joel Kallman about APEX 5.0 followed by 18 very interesting sessions about customer business cases, technical developments and international presentations by APEX specialist from all over the world.

APEX 5.0

The key focus in the new APEX 5.0 is improved developer productivity.oracle apex page designer
The page builder is completely new. Through this interface developers will be able to do more in less time and most important, in fewer clicks. With a properties sidebar on the right side of the screen it will be possible to quickly change elements and regions on a page, even multiple elements at the same time!  Regions and items can be created through drag and drop which increases the development speed.

Other new features

Tabs
Improved tab navigation. The current tab system isn’t user friendly enough, so it’s better to use lists. Now you can create new pages and define their hierarchy in the application. When this is done, an automatic tab will be created with dropdown submenus to display the hierarchy.

Interactive reports
Two important improvements for interactive reports. First and foremost it’s possible to have multiple interactive reports on one page, something we’ve all been waiting for since APEX 4.x. And secondly there is a new format function to pivot your report. Joel Kallman presented this feature: in a couple of clicks he created a nice pivoted table on the screen.

jQuery Mobile integration
With jQuery Mobile your SQL reports will have the possibility to be responsive. You have the option to:
a) only display the most important columns on a small screen, or
b) to switch to some kind of single record view. The result is something similar to what you can see here: http://elvery.net/demo/responsive-tables/

Modal popup
Instead of using a plugin to let your pages open in a modal window, users can now set this feature as a property of the page. Whenever the user navigates to this page, it will open in a modal window.

Be sure to take a look at the APEX early adaptor: apexea.oracle.com

 

Presentations

After the APEX 5.0 demonstration, there were 3 parallel tracks, all with very different and interesting sessions.  Read our impressions …

Going public with your APEX application
FOEX brought this presentation very well. Their problem scenario was the following one: If you want to make a public APEX application, you are always stuck with the typical APEX URL like “apex/f?p=100:1:5039230103::::”. During the demo they showed how to create a nice and readable URL like “apex/demo/customers”. To accomplish this they used aliases, REST services, PL/SQL and a few lines of javascript.

The best of both worlds: going hybrid with your mobile APEX application
Roel Hartman gave a presentation about Phonegap in combination with APEX. He showed a nice demo on how to sync the contacts from a database with the ones from his cell phone through a Phonegap App. It was surprising how easily this could be setup without too much code and in-depth knowledge. He used REST services to sync the data between APEX and his cellphone.

Using AngularJS in oracle applications express
Dan McGhan of Enkitec (USA) brought a technical session about combining AngularJS and APEX. He showed us a single page application containing a to do list with advanced calendar features. The end result was very nice and the demo illustrated the power of AngularJS, but it certainly requires some time to understand this framework. Maybe an interesting idea is to include AngularJS natively in APEX 6.0?

A B2B weboracle apex b2b webshop - tuur hendrickxshop with APEX!
iAdvise did two presentations. The first one dealt with a B2B webshop we developed in APEX for Billiet. Justine Ghekiere gave a brief introduction about the core business of her company, Biliet. Our colleague Tuur Hendrickx showed a lot of features he implemented in the webshop with APEX. Topics he show-cased were:  special advertisements, restricted products for different customers, the use of a shopping cart and a stunning layout were demonstrated.

APEX & HTML5
We also attended a nice presentation of Martin Giffy D’Souza about APEX and HTML5. He showed the advantages of HTML5 and the typical use cases in APEX. During a live demo he showed how to record a video within APEX and stream the feed to another frame in the same screen. Really impressive!  Also nice to see was how easily it is to implement voice recognition by using HTML5.

Dutch immigration services (IND) monitor xml messages with oracle apex
A department of the Dutch government has built an application which provides residence permits to immigrants or refugees. Before they could start building the APEX application there was a lot of effort necessary in the Oracle database for dealing with all the XML files. It was not just a problem with the size of the XML files, but there were also issues with differences between Oracle 10.2 and 11.2 in the way the database handles XML files.

Reporting solutions for oracle APEX – choose your weapons
During this session Dietmar Aust gave us an overview of possible reporting solutions  for APEX applications. Many solutions were covered in an objective way:  BI Publisher, Jasper Reports, Apache FOP, APEX PDF printing, PL/PDF, … Dietmar even demonstrated our own tool Doxxy (www.doxxy.eu). Nice to hear that he likes Doxxy! He also showed us his own solution for typical problems related to exporting data from interactive report to MS Excel, especially regarding the proper data types: OPAL:XP (for eXPorting to MS Excel).

Single-click deployment in APEX development
One of the last tracks we visited was about single-click deployment of APEX applications in OTAP areas. They talked about the use of bamboo, in combination with GIT and APEX. It was nice to see how they solved the problem of continuous integration with APEX.

A logistic data portal with APEX!oracle apex data portaal - menno hoogendijk
In the second iAdvise customer case Robert Esseling explained why Bas Logistics needed a data portal. Those requirements where then demonstrated by Menno Hoogendijk.
The portal has an admin module to manage the data import and mapping settings. In the very straight-forward  front-end, users drill down from dashboards to detailed data.

 

Thanks to the organization for hosting this great event, really one of the best conferences in the benelux!
See you at APEX World 2015!

Watch out with function result cache based on data dictionary views

Result cache is a powerful tool to gain performance in PL/SQL.
There are many examples on the internet that proves this, e.g. these articles on All things Oracle:
Result Cache(1)
Result Cache(2)

But I’m not going to talk about performance.
This article is some kind of warning.

First I’ll show you how result cache works on a normal view.
I’ll create a table, a view on this table and a function that counts the rows in the view.

SQL> create table x (field1 varchar2(1), field2 number(1));

Table created.

SQL> create or replace view vie_x as select * from x;

View created.

SQL> CREATE OR REPLACE FUNCTION vie_x_rowcount(p_field1 IN VARCHAR2)
RETURN NUMBER RESULT_CACHE
IS
   l_return NUMBER;
BEGIN
   SELECT count(*)
     INTO l_return
     FROM vie_x
     WHERE field1 = p_field1;

   RETURN l_return;

END vie_x_rowcount;
/

Function created.

SQL> insert into x(field1, field2) values('x', 1);

1 row created.

SQL> commit;

Commit complete.

These are the statistics for the result cache, just to show you we’re starting without any caching.

SQL> select name, value from v$result_cache_statistics where name in ('Create Count Success', 'Find Count', 'Invalidation Count');

NAME			               VALUE
------------------------------ ------------------------------
Create Count Success	       0
Find Count		               0
Invalidation Count	           0

When we execute the function, the statistics show that there’s an entry created in the cache.

SQL> select vie_x_rowcount('x') from dual;

VIE_X_ROWCOUNT('X')
-------------------
		  1

SQL> select name, value from v$result_cache_statistics where name in ('Create Count Success', 'Find Count', 'Invalidation Count');

NAME			               VALUE
------------------------------ ------------------------------
Create Count Success	       1
Find Count		               0
Invalidation Count	           0

When we execute the same code again, we’ll get the same result and the statistics show us that the result is found in the cache.
Good job Oracle!

SQL> select vie_x_rowcount('x') from dual;

VIE_X_ROWCOUNT('X')
-------------------
		  1

SQL> select name, value from v$result_cache_statistics where name in ('Create Count Success', 'Find Count', 'Invalidation Count');

NAME			               VALUE
------------------------------ ------------------------------
Create Count Success	       1
Find Count		               1
Invalidation Count	           0

Let’s insert a new row in the table.
This time the statistics show us that the cache is “invalidated”, meaning the function has to be executed again to return the correct value.

SQL> insert into x values('x', 2);

1 row created.

SQL> commit;

Commit complete.

SQL> select name, value from v$result_cache_statistics where name in ('Create Count Success', 'Find Count', 'Invalidation Count');

NAME			               VALUE
------------------------------ ------------------------------
Create Count Success	       1
Find Count		               1
Invalidation Count	           1

And the expected result…

SQL> select vie_x_rowcount('x') from dual;

VIE_X_ROWCOUNT('X')
-------------------
		  2

The Oracle database has its own data dictionary, a set of tables where it stores all information about the database and what’s in it.
Data of these tables are available through views, data dictionary views.
In the following example I’ll use the data dictionary view that holds the information on columns.
I created a function that returns the number of columns for a certain table.

SQL> CREATE OR REPLACE FUNCTION number_of_columns(p_table_name VARCHAR2)
RETURN NUMBER RESULT_CACHE
IS

   l_return NUMBER;

BEGIN

   SELECT count(*)
     INTO l_return
     FROM user_tab_columns
    WHERE table_name = p_table_name;

   RETURN l_return;

END number_of_columns;
/

Function created.

To make sure we’ll start with a clean cache, I’ll flush it using the dbms_result_cache.flush procedure.

SQL> execute dbms_result_cache.flush

PL/SQL procedure successfully completed.

SQL> select name, value from v$result_cache_statistics where name in ('Create Count Success', 'Find Count', 'Invalidation Count');

NAME			               VALUE
------------------------------ ------------------------------
Create Count Success	       0
Find Count		               0
Invalidation Count	           0

When we execute the function, we’ll get the expected result: the function is executed and a cache entry is created.

SQL> select number_of_columns('X') from dual;

NUMBER_OF_COLUMNS('X')
----------------------
		     2

SQL> select name, value from v$result_cache_statistics where name in ('Create Count Success', 'Find Count', 'Invalidation Count');

NAME			               VALUE
------------------------------ ------------------------------
Create Count Success	       1
Find Count		               0
Invalidation Count	           0

We can execute it again and see that the return value is retrieved from the cache.

SQL> select number_of_columns('X') from dual;

NUMBER_OF_COLUMNS('X')
----------------------
		     2

SQL> select name, value from v$result_cache_statistics where name in ('Create Count Success', 'Find Count', 'Invalidation Count');

NAME			               VALUE
------------------------------ ------------------------------
Create Count Success	       1
Find Count		               1
Invalidation Count	           0

Let’s add a column to the table.
This should add a new row in a data dictionary table and thus in the data dictionary view we use in our function.

SQL> alter table x add (field3 date);

Table altered.

SQL> desc x
 Name					   Null?    Type
 ------------------------- -------- ----------------------------
 FIELD1 					        VARCHAR2(1)
 FIELD2 					        NUMBER(1)
 FIELD3 					        DATE

Now execute the function again.
And the result is…

SQL> select number_of_columns('X') from dual;

NUMBER_OF_COLUMNS('X')
----------------------
		     2

Not what we expected!
When we take a look at the result cache statistics, it shows that the cache wasn’t invalidated and the result was retrieved from the result cache.

SQL> select name, value from v$result_cache_statistics where name in ('Create Count Success', 'Find Count', 'Invalidation Count');

NAME			               VALUE
------------------------------ ------------------------------
Create Count Success	       1
Find Count		               2
Invalidation Count	           0

When we flush the cash and execute the function again, we’ll get the correct result cache.

SQL> execute dbms_result_cache.flush

PL/SQL procedure successfully completed.

SQL> select number_of_columns('X') from dual;

NUMBER_OF_COLUMNS('X')
----------------------
		     3

So, it seems that the result cache isn’t invalidated on data dictionary tables.
And indeed this is what I found in the Oracle documentation:

You cannot cache results when the following objects or functions are in a query:

  • Temporary tables and tables in the SYS or SYSTEM schemas

Migrate your MS Access data to an Oracle database using the ETL Tool Talend

APEX is promoted as the perfect replacement for MS Access applications. One thing you should consider though is how you migrate your data to the Oracle database. In APEX there is a handy tool called the Data Workshop that can be used for this. You first export your Excel files from the MS Access database, and then follow the data upload wizard to import the data into identical tables. Since you are not always working with a 1-1 relationship, you will most likely have to write some PL/SQL to get all the data in the right tables.

dataworkshop

The downside is that you will need to repeat this process when you go into production. This is not a big problem if you only have one table to migrate. But if you have multiple tables and/ or your users also want new data during tests and trainings, you will spend a lot of time exporting and importing Excel files.

A recent APEX project for a client required a large data migration from MS Access Databases to the Oracle database. Because we would require fresh data on several points in the development process we decided to use the ETL Open Source Tool Talend. We got impressed of how intuitive the tool is, it only took a few days before we were familiar with the tool. Once you get the hang of it, you can write (or should I say draw) migrations of tables in no time. We needed to migrate from an MS Access database but the tool supports a wide range of databases and documents to import your data from. In total we migrated around 30-40 tables to our Oracle database.

Let’s have a closer look at one of our migration jobs.

talendmsaccessoracle

At the left we see our MS Access database. Each tAccessInput component will get data from one table. After that we join the tables in our tMap_1 component. The reason we don’t just write our joins in one component, is because this way we can really see how many rows every table returns.

On the bottom we have some Oracle Database input connections. They will join the persons of our MS Access Database with the persons in our Oracle Database based on the National registration number. After that we write our data to our Oracle Database. You may notice that we have two lines going to Excel files. This is our error logging; we use this to log the rows that did not find a match. In our first Excel for example we write persons that did not find a match in our Oracle Database.

This is just one example, in total about 20 jobs were built. During the development we also had to deal with certain calculations or convert data. For most things there was a component ready to use and if there wasn’t you could always write a Java expression in the tMap items.

I hope I convinced you of the benefits of using Talend as a migration tool for APEX projects, because we will certainly use this tool again!

Bryxx has launched!

On Tuesday, May 7 the Bryxx launch event took place. In the beautiful setting of the Flandria boat, and in the presence of a large number of customers, we revealed the services of this new venture. As a joint venture between the iAdvise and Contribute infrastructure teams, Bryxx will specifically focus on the middleware field.
In bringing together both expert middleware teams, we will focus on
  • Opening up your business critical web applications to your intranet or to the internet
  • Making sure that these applications, deployed on your middleware stack, are secure on all layers of the underlying architecture. Security from-data-to-browser
  • Streamlining and automating your process of development towards production
  • Providing you with the opportunities to outsource the maintenance of your private middleware cloud or to outsource your entire private middleware cloud
From a technical point of view Bryxx will dedicate its expertise to 4 domains:
  • Oracle Cloud Application Foundation (with web logic as the main driver)
  • Oracle Identity & Access management
  • Oracle Database Security
  • DevOps
With respect to these 4 areas of expertise, Bryxx provides strong consultancy profiles on all levels (pre-sales, infrastructure architects, senior implementation engineers, etc) to design, install, configure, maintain and monitor your middleware platform as well as to streamline the process of application development towards your preferred middleware solution.
When you add our managed services and hosted solutions offering on each of these domains to this package, with strong partnerships in the backend, we believe Bryxx has a strong and complete offering for all your middleware challenges !Our team of 14 dedicated and experienced middleware engineers is ready for you.
Want to know more?
Visit us at www.bryxx.eu or contact us at info@bryxx.eu
 bryxx1bryxx2bryxx3bryxx4

OBUG Connect 2013: iAdvise presentation on ADF & Web Services

On 26 March OBUG Connect, the yearly Oracle Benelux User Group conference, will be held in Antwerp.

iAdvise will be presenting about ADF and web services.
We’ll show you how you can expose your ADF Business components as Web Services.
But also how you can consume Web Services in your application.

The presentation is session 3 in the “Middleware track”(track 7) and starts at 15.45.
We hope to see you in Antwerp!

 

Slides of the ODTUG Webinar: “Oracle ADF Immersion: How an Oracle Forms Developer Immersed Himself in the Oracle ADF World”

A few weeks ago we did an ODTUG Webinar: “Oracle ADF Immersion: How an Oracle Forms Developer Immersed Himself in the Oracle ADF World”.

About 186 followed the seminar online.
Those people received a link to the recorded session and the slides of the presentation.

For those who couldn’t attend, these are the slides of the presentation: ADF Immersion presentation
But the presentation was a lot more than a few slides, there was also a demo(>30 minutes).
So if you want to see the full recording, you can see all past webinars as a full ODTUG member.

If you need more info on ADF methodologies and ADF best practices or want to ask questions about these topics, check out the ADF EMG group.

Starting with ADF 11G Logging

In software development, logging is an indispensible part of the job. When developing  java programs, Log4j (Apache framework) is probably the most commonly used framework.

But when we are writing Java programs, using the Oracle ADF framework, there is another option : ADF Logger. This logger is integrated in the Weblogic enterprise manager, and gives you the flexibility to adjust your log-levels at runtime. This blog post briefly demonstrates how the ADF logging works, using a servlet that logs at all levels.

The first example is run on a remote Weblogic server. At the end of the post the same example is run within the integrated Weblogic server of JDeveloper.

First we start with viewing the loggers defined in Oracle Enterprise Manager (OEM) before our servlet is deployed :

em_go_to_logconfig_2

When we select ‘Log Configuration’ we get the Log Configuration screen, where some loggers are already defined and their loglevels can be configured :

em_initieel_2

We’ll deploy a servlet that uses the ADF Logger, change the loglevels at runtime, and check the logging to see what happens.

So fire up your JDeveloper and select a new Fusion Web Application (ADF) or download the JDeveloper project from  https://www.dropbox.com/s/mwkjdw8k265iadx/LoggerApp.zip

Then create a servlet like the one below, which is a simple servlet called ‘ExecuteLogger’ that logs a message on all loglevels :


package be.iadvise.loggerapp;

 import java.io.IOException;
 import java.io.PrintWriter;

 import java.util.Calendar;

 import javax.servlet.*;
 import javax.servlet.http.*;

 import oracle.adf.share.logging.ADFLogger;

 public class ExecuteLogger extends HttpServlet {
  private static final String CONTENT_TYPE = "text/html; charset=UTF-8";

 private static ADFLogger _log = ADFLogger.createADFLogger(ExecuteLogger.class);

  public void init(ServletConfig config) throws ServletException {
  super.init(config);
  }

 public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
  response.setContentType(CONTENT_TYPE);
  PrintWriter out = response.getWriter();
  out.println("<html>");
  out.println("<head><title>Executing logging with ADFLogging and System.out.println</title></head>");

 Calendar cal = Calendar.getInstance();
  String loggingId = Long.toString(cal.getTimeInMillis());
  out.println("<h1>Executing logging with ADFLogging and System.out.println</h1>");
  out.println("<h2>Used Logging id = "+loggingId+"</h2>");
  out.println("<p>The logging id is different for each request.</br>Use the logging id to search the log messages in the logfiles or EM for this request.");
  out.println("<p>Logger Name in EM = "+_log.getName());

  out.println("<p>****************** START LOGGING ******************");

  out.println("<table >");

 // FINEST
  _log.finest("Loglevel finest : This is a logmessage with the ADFLogger. Logging id = "+loggingId);
  System.out.println("Loglevel finest : This is a logmessage with the System.out.println. Logging id = "+loggingId);
  out.println("<tr><td>FINEST</td><td>Loglevel finest : logMessage written to ADFLogger and System.out.println. Logging id = "+loggingId+"</td></tr>");

  // FINER
  _log.finer("Loglevel finer : This is a logmessage with the ADFLogger. Logging id = "+loggingId);
  System.out.println("Loglevel finer : This is a logmessage with the System.out.println. Logging id = "+loggingId);
  out.println("<tr><td>FINER</td><td>Loglevel finer : logMessage written to ADFLogger and System.out.println. Logging id = "+loggingId+"</td></tr>");

  // FINE
  _log.fine("Loglevel fine : This is a logmessage with the ADFLogger. Logging id = "+loggingId);
  System.out.println("Loglevel fine : This is a logmessage with the System.out.println. Logging id = "+loggingId);
  out.println("<tr><td>FINE</td><td>Loglevel fine : logMessage written to ADFLogger and System.out.println. Logging id = "+loggingId+"</td></tr>");

  // CONFIG
  _log.config("Loglevel config : This is a logmessage with the ADFLogger. Logging id = "+loggingId);
  System.out.println("Loglevel config : This is a logmessage with the System.out.println. Logging id = "+loggingId);
  out.println("<tr><td>CONFIG</td><td>Loglevel config : logMessage written to ADFLogger and System.out.println. Logging id = "+loggingId+"</td></tr>");

  // INFO
  _log.info("Loglevel info : This is a logmessage with the ADFLogger. Logging id = "+loggingId);
  System.out.println("Loglevel info : This is a logmessage with the System.out.println. Logging id = "+loggingId);
  out.println("<tr><td>INFO</td><td>Loglevel info : logMessage written to ADFLogger and System.out.println. Logging id = "+loggingId+"</td></tr>");

  // WARNING
  _log.warning("Loglevel warning : This is a logmessage with the ADFLogger. Logging id = "+loggingId);
  System.out.println("Loglevel warning : This is a logmessage with the System.out.println. Logging id = "+loggingId);
  out.println("<tr><td>WARNING</td><td>Loglevel warning : logMessage written to ADFLogger and System.out.println. Logging id = "+loggingId+"</td></tr>");

  // SEVERE
  _log.severe("Loglevel severe : This is a logmessage with the ADFLogger. Logging id = "+loggingId);
  System.out.println("Loglevel severe : This is a logmessage with the System.out.println. Logging id = "+loggingId);
  out.println("<tr><td>SEVERE</td><td>Loglevel severe : logMessage written to ADFLogger and System.out.println. Logging id = "+loggingId+"</td></tr>");

  out.println("</table>");

  out.println("<p>****************** END LOGGING ******************");

  out.println("<body>");
  out.println("</body></html>");
  out.close();
  }
 }
 

Then, map this servlet in your web.xml as follows :

<servlet>
 <servlet-name>ExecuteLogging</servlet-name>
 <servlet-class>be.iadvise.loggerapp.ExecuteLogger</servlet-class>
 </servlet>
 <servlet-mapping>
 <servlet-name>ExecuteLogging</servlet-name>
 <url-pattern>/executeLogging</url-pattern>
 </servlet-mapping>

The line

private static ADFLogger _log = ADFLogger.createADFLogger(ExecuteLogger.class);

will create an entry in the ADF logging panel of OEM  for the class ‘be.iadvise.loggerapp.ExecuteLogger’ during the first execution of our servlet.

So generate the ear file (in JDeveloper : Application -> Deploy -> your application) , and deploy to an ear , and deploy the ear to your remote Weblogic.

Then, execute the servlet by entering following url in a browser http://server:port/appname/executeLogger

We will receive the following output where the logging id is a unique id for every request (this will help us find the log info logged for every run) :

output_servlet_3

and in our log configuration screen, the logger for our servlet is added automatically with level WARNING :

EM_na_uitvoeren_servlet_2

When we look to the logging itself, by selecting the following :

go_to_logmessages

We see that our servlet has logged 2 lines  : level WARNING and ERROR, as the logger was default created with level “warning”.

(We look in the log files for the id generated by the servlet to get our 2 lines)

output_first_log_2

Now let’s update our log level to TRACE (finest), press apply, confirm the update, execute the servlet again , and see what happens to our logging :

trace

confirm the update :

confirm

execute the servlet again (it generates a new logging id)

new_servlet

look for the generated logging :

new_logging

As we have set the loglevel to trace (finest), which is the lowest level, we see all the generated loglines.

Remark : When we undeploy the application, the logger will remain visible in the log configuration screen until the managed server is restarted.

Using the integrated Weblogic server in JDeveloper

This screen shot shows you how get to the log configuration and logging screen in JDeveloper :

1_go_log_screen

After executing the servlet locally, the Oracle Diagnostic Logging will show you the following :

2_log_config

and the analyze log :

3_logging_first_run

then we change the log level :

4_change_log_level

and execute the servlet again and watch the output :

5_output_logging_2

That’s it !!!

The big advantage of ADFLogging is the update of the loglevel at runtime, so whenever something seems to be going wrong, just set the loglevel to e.g. TRACE and check the logfiles. When the problem is solved reset it to WARNING or ERROR. And this without having to restart your application.

Also, being able to view and search the logfiles using the EM increases the userfriendlyness of this system. You don’t have to access the files directly on the system anymore.

ODTUG Webinar: “Oracle ADF Immersion: How an Oracle Forms Developer Immersed Himself in the Oracle ADF World”

On Oracle Open World we had the chance to present our “Oracle ADF Immersion” track thanks to the ADF EMG.
In case you couldn’t attend OOW or missed the session, you’ve got another chance: on january 10, 2013 we will present it as an ODTUG Webinar.

We will show you how you can start with ADF, coming from an Oracle Forms(or non-java) background.
You can register for the webinar here.

If you like to join the ADF EMG, don’t hesitate, it’s a free ADF usergroup!