5 Things I wish I knew about APEX when I just started (part 2)

In my first post of this series, I talked about subscriptions. The next post in this series is about cookie sharing in APEX, I hope you enjoy it.

Apex SSO by Cookie sharing

In the spirit of the previous post, this post will also be one that comes in handy when running multiple applications in the same workspace. When running multiple applications in the same workspace, it’s possible to share the authentication across multiple applications so you don’t have to login every time you switch applications. The only problem is that the feature is tucked away pretty good in the authentication scheme settings.

First we will Create 2 standard Applications

Untitled-1

Now we will create a simple page with a link to a page in the other application, to test if our setup works.

Untitled-2

If we would click the link now we would be redirected to the login page of the other application since, we’re not yet authenticated.

Untitled-3

What we need to do now is set up our authentication, to keep it simple I will make a hardcoded function where the process checks for username and password admin. But feel free to use your own custom login procedure. :-)

Untitled-4

Now for the important part, scroll down in the authentication scheme until you see a block called “Session Cookie Attributes”

In the cookie name field, you can set a name for your cookie, the important thing for this to work is that the name is the same in both applications, you also have the option here to define extra settings for your cookie. Whether it should be secure or not and the cookie path and domain.

Untitled-5

In the second application create the same authentication scheme as a copy of an existing authentication scheme. You can subscribe the authentication scheme to the first application.

For more information about subscription you can look at my first post.

If we log in to one of our applications now, we should be able to switch applications by clicking on the link we have created

That’s it! The third blog in this series will be about build options, click here to read it.

.

5 Things I wish I knew about APEX when I just started (part 1)

I’ve been doing apex for about 7 years now, and along the way I discovered a few things that made my life as a developer a lot easier. I made a list of 5 things that I wish I had known wen I just started. Those things will be explained to you in a series of 5 posts.

Subscriptions

One of the most tiresome things to do when managing multiple applications is to keep things like templates, and security up-to-date across all applications.

Apex has a nice built-in system for this called ‘subscriptions’.

If you just start with a new project, the easiest way to set-up the subscriptions is by creating a new application and call it “MASTER APPLICATION” or something similar, so you know this will be the parent application for all subscriptions. For an existing application the same method applies, but it will be a bit more work intensive to get everything right.

In this application, you define your, security scheme, authorization schemes, templates, list of values.

Once you have your basic configuration done, you are ready to start working on your new applications.

To link the new application to the MASTER application let’s say for example an authorization scheme.

You go to shared components, authorization schemes, create.

Select “create as copy”.

apex_P1_1

Copy from the master application and then select copy and subscribe.

apex_P1_2

If you edit the authorization scheme now you will see that in the subscription part there is an application referenced.

apex_P1_3

By now you are wondering: “What’s the point of all this? Couldn’t I just as easily have copy pasted the code?”

Yes, you could have, but imagine having 20 or more applications using the same authorization scheme, and then one day you may have to change something inside, you would have to modify all 20 schemes in all 20 applications again.

But because now we reference the master template this is no longer necessary. For instance let’s say we want a function to return false instead of true, what we can do now is go to the master application, edit the authorization scheme, change it, save it, and press the publish scheme button.

In the long run this could save you tons of work!

apex_P1_4

For subscribing templates in apex there is a way to do all templates at the same time.

Go to shared components, templates.

On the righthand side you have a menu called task, click the link to replace templates in this application with templates from another application.

apex_P1_5

Select the master application again, in the next screen select the template, and in the action drop down select, replace/subscribe, and press replace templates.

apex_P1_6

Read the next blog in this series:  Apex SSO by Cookie sharing.

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!