maandag 3 juni 2019

ORDS Remove entire schema info

Business Case

While deploying more and more ORDS based solutions, we need to be able to clean ORDS completely for a certain schema.


We created our own procedure for this cleanup.  While it works in our cases, maybe it is not complete for your environment.  Testing is always crucial.

Follow the following steps to create the procedure:

  1. Login with sys of your database or pluggable db
  2. Execute the following script:
create or replace procedure ords_metadata.remove_schema_entries
authid definer
    v_user constant varchar2(200) := SYS_CONTEXT('USERENV', 'SESSION_USER');
    dbms_output.put_line('start remove schema entries for '||v_user);
    delete from ords_handlers where created_by = v_user;
    delete from ords_modules where created_by = v_user;
    delete from ords_objects where created_by = v_user;
    delete from ords_parameters where created_by = v_user;
    delete from ords_templates where created_by = v_user;
    delete from ords_url_mappings where created_by = v_user;
    delete from sec_authenticators where created_by = v_user;
    delete from sec_keys where created_by = v_user;
    delete from sec_origins_allowed_modules where created_by = v_user;
    delete from sec_privilege_auths where created_by = v_user;
    delete from sec_privilege_mappings where created_by = v_user;
    delete from sec_privilege_modules where created_by = v_user;
    delete from sec_privilege_roles where created_by = v_user;
    delete from sec_privileges where created_by = v_user;
    delete from sec_role_mappings where created_by = v_user;
    delete from sec_roles where created_by = v_user;
    delete from ords_schemas where created_by = v_user;
    dbms_output.put_line('end remove schema entries');
end remove_schema_entries;
grant execute on ords_metadata.remove_schema_entries to public; 
create public synonym ords_remove_schema_entries for ords_metadata.remove_schema_entries;

To use this procedure, execute it with the following syntax:

set serveroutput on


This solution has been developed with ORDS 18.3. 

maandag 16 juli 2018

APEX 5.0 Cascading reports

Business case

You want to create a page with two or more reports that are connected with each other.  A kind of master-detail for reports.
In this example we show the list of departments and as a child report we show the employees of that department.


We start with building a blank page for our reports.  Add the different report regions on the page.  Whether you are using interactive reports or classic ones, it doesn't matter.  The solution works for both.

Once the reports are present and working independently, we can start making them connected.
The first report is called master and the second one is called the detail report.

Follow these steps to connect the reports:

  1. Create the necessary page items to hold the information to be passed on to the detail report.  In my case this is only the primary key.  So I create a page item called P7_DEPT_PK_ID.  These page items can be set to hidden, since they have no added value for the end-user.  But for testing purposes, we lease them visible so we can see what is being passed on.  You can make them hidden, once everything is working correctly.
  2. Now we need some javascript-code to pass the values of the current selected master record to these page items.  The easiest way is by using a link column on the master report.  As target for this link-column, we use an URL with the following destination:
    You can see here that we are using a method called "$s", which can be used to put values in items.  As parameters we give the name of the destination parameter and the value, which in this case is the DEPARTMENT_ID from the currently selected master record.

    Some nice explanation on these javascript functions can be found in this blog:
  3. Once the value of the PK is now in this page item, we still need to refresh the detail report.  This can be done through the use of a dynamic action.  In this dynamic action we indicate that whenever the page item P7_DEPT_PK_ID is changed, we need to refresh the detail report.
  4. The only remaining thing to do is to use the page item in the query of the detail report.
  5. Because we are using javascript to fill in the page item, we need to submit the page item to the server, when we are refreshing the detail report.  This is done through the property 'Page Items to Submit' on the detail report.
  6. Now you can run your solution

vrijdag 6 juli 2018

APEX 5.0 Example use of collections

This is my first attempt in using collections within Oracle APEX.
I just save this code for later use.

I used APEX 5.0 on the Oracle cloud.

I created 2 pages:

  • Overview Page
  • Edit Page
In the Overview page I load through a select-statement the data into a collection during the Pre-Rendering > Before Header > Processes > PL/SQL action, with the following code:
l_query varchar2(300) := 'select department_id, department_name, manager_id from departments';
l_coll_name varchar2(50) := 'DEPARTMENTS';
 APEX_DEBUG.INFO('Start fetchData');
    APEX_DEBUG.INFO('Collection ' ||l_coll_name||' does not exist, creating a new one');
      -- Create the collection from the query 
        p_collection_name => l_coll_name, 
        p_query => l_query);
    end if;
 APEX_DEBUG.INFO('End fetchData');

I'm creating the collection only the first time that I'm entering the page.  So when changes are made on the second page, these changes are immediately shown on this page on returning.

To show the data in a report, you can use a classic report with a query which looks like this one:
  from apex_collections
  where collection_name = 'DEPARTMENTS'

In this simple example, I just copy the values from the collections to the editable fields on the Edit Page through the link-functionality.
When showing the Edit page, I add a save button, which on submitting the page saves the changes in the collection and the db if needed.  These operations are performed in the Processing > Processes > PL/SQL code with the following code:

  cursor c_get_seq
  SELECT seq_id
   FROM APEX_collections
  WHERE collection_name = 'DEPARTMENTS'
    AND c001 = :P6_DEPTID
  r_seq c_get_seq%rowtype;
   open c_get_seq;
   fetch c_get_seq into r_seq;
   if c_get_seq%found
   APEX_DEBUG.INFO('Collection found, updating);
        p_collection_name => 'DEPARTMENTS',
        p_seq => r_seq.seq_id,
        p_c001 => :P6_DEPTID,
        p_c002 => :P6_DEPTNAME,
        p_c003 => :P6_MANAGER);
     -- update departments set department_name = :P6_DEPTNAME, manager_id = :P6_MANAGER where department_id = :P6_DEPTID;
     APEX_DEBUG.ERROR('Collection not found!!');
   end if;
   close c_get_seq;

After clicking on the save-button, the collection gets updated and the user returned to the previous page.

donderdag 8 december 2016

Finding jdbc/db connection leaks


Once I started creating my own application module instances, I got database connections not being released anymore.  And because we are all working in complex and big application, I didn't found the cause of the leak.
This blog will help you in finding the cause for the jdbc leak.
All my knowledge is based on the blog from Raul Castillo:JDBC Connection Leak

Finding that you have leaks

The first thing to do is finding whether you have leaks.  This can be done through the end-users stating that the application returns an error stating no connections are available anymore.
Now you can start investigating the cause of this issue.
You can use the Fusion Middleware console to investigate from which datasource the connections are not freed anymore.  It has a lot of helpfull statistics.
  1. Log in the Fusion Middleware Console : http(s)://<host>:<port>/em
  2. Go to your application on the left side under Farm and Application Deployments
  3. Click on the name of your application
  4. In the Application Deployment menu you can choose for Performance Summary.
  5. This will bring you to a new screen showing some default selected diagrams.  
  6. Click on the "Show Metric Palette" button to change the diagrams being showed.
    1. For this use case, you want to go to Related Targets > ServerName > Metrics > Datasource metrics > YourDataSourceName, domain level
    2. Here can select the following interessanting ones:
      1. Datasource - Available connections
      2. Datasource - Connection Leaks
      3. Datasource - Connection Pool size
      4. Datasource - Connections in use
These graphs are very handy when you want to see the evolution of the use of the connections of the datasource.  You can open multiple graphs from different datasource at the same time.  So  you can correctly define the datasource that is being leaking connections.

Once you have determined the datasource causing the leaks, you need to make sure WebLogic Server is cleaning them up after a while.  This can be configured in the WebLogic console:
  1. Login to WLS Console : http(s)://<host>:<port>/console
  2. Go to the datasources defined in the domain
  3. Select the datasource causing the leaks
  4. Go to Configuration > Connection Pool > Advanced 
  5. Make sure the parameter Inactive Connection Timeout is a positive number.  Put it to 5 for testing.  This parameter will cause WebLogic Server to cleanup stuck db connections  and causing a nice error message in the log files.
  6. You can find the logging in your <WLS managed server name>.log file.
  7. It is this error message that you need.  The Inactive Connection Timeout identifies after how many seconds he will try to cleanup the db connections.  When he finds db connections leaking, he will log a java stack trace indicating from where the leak is originating.  Here is an example:

    ####<Dec 8, 2016 8:12:07 AM CET> <Warning> <Common> <zcorinthe-1> <dev01-S1mc> <[ACTIVE] ExecuteThread: '0' for queue: 'weblogic.kernel.Default (self-tuning)'> <<WLS Kernel>> <> <1bf3554408c0f552:-3a194c43:158d428de6f:-8000-0000000000002b61> <1481181127371> <BEA-000620> <Forcibly releasing inactive resource "autoCommit=false,enabled=true,isXA=false,isJTS=false,vendorID=100,connUsed=false,doInit=false,'null',destroyed=true,poolname=MCPrintDev,appname=null,moduleName=null,connectTime=87,dirtyIsolationLevel=false,initialIsolationLevel=2,infected=false,lastSuccessfulConnectionUse=1481181100045,secondsToTrustAnIdlePoolConnection=10,currentUser=java.lang.Exception
      at weblogic.jdbc.common.internal.ConnectionEnv.setup(
      at weblogic.common.resourcepool.ResourcePoolImpl.reserveResource(
      at weblogic.common.resourcepool.ResourcePoolImpl.reserveResource(
      at weblogic.jdbc.common.internal.ConnectionPool.reserve(
      at weblogic.jdbc.common.internal.ConnectionPool.reserve(
      at weblogic.jdbc.common.internal.ConnectionPoolManager.reserve(
      at weblogic.jdbc.common.internal.ConnectionPoolManager.reserve(
      at weblogic.jdbc.pool.Driver.connect(
      at weblogic.jdbc.jts.Driver.getNonTxConnection(
      at weblogic.jdbc.jts.Driver.connect(
      at weblogic.jdbc.common.internal.RmiDataSource.getConnectionInternal(
      at weblogic.jdbc.common.internal.RmiDataSource.getConnection(
      at weblogic.jdbc.common.internal.RmiDataSource.getConnection(
      at oracle.jbo.server.DBTransactionImpl.establishNewConnection(
      at oracle.jbo.server.DBTransactionImpl.initTransaction(
      at oracle.jbo.server.DBTransactionImpl.initTxn(
      at oracle.jbo.server.DBTransactionImpl2.connectToDataSource(
      at oracle.jbo.server.DBTransactionImpl2.connectToDataSource(
      at oracle.jbo.common.ampool.DefaultConnectionStrategy.connect(
      at oracle.jbo.server.ApplicationPoolMessageHandler.doPoolConnect(
      at oracle.jbo.server.ApplicationPoolMessageHandler.doPoolMessage(
      at oracle.jbo.server.ApplicationModuleImpl.doPoolMessage(
      at oracle.jbo.common.ampool.ApplicationPoolImpl.sendPoolMessage(
      at oracle.jbo.common.ampool.ApplicationPoolImpl.prepareApplicationModule(
      at oracle.jbo.common.ampool.ApplicationPoolImpl.doCheckout(
      at oracle.jbo.common.ampool.ApplicationPoolImpl.useApplicationModule(
      at oracle.jbo.common.ampool.SessionCookieImpl.useApplicationModule(
      at oracle.jbo.http.HttpSessionCookieImpl.useApplicationModule(
      at oracle.jbo.common.ampool.SessionCookieImpl.useApplicationModule(
      at oracle.jbo.common.ampool.SessionCookieImpl.useApplicationModule(
      at oracle.jbo.client.Configuration.getApplicationModule(
      at oracle.jbo.client.Configuration.createRootApplicationModule(
      at oracle.jbo.client.Configuration.createRootApplicationModule(
      at be.contribute.demo.adf.DocumentManagement.createAMInstance(
      at be.contribute.demo.adf.DocumentManagement.<init>(
      at be.contribute.demo.adf.DocumentManagement.<init>(
      at be.contribute.demo.adf.SimpleDocumentGeneration.<init>(
      at be.contribute.demo.adf.bipublisher.SimpleEnvelopeDocumentGeneration.<init>(
      at be.contribute.demo.adf.bipublisher.AcknowledgmentRecieptGeneration.<init>(
      at be.contribute.demo.adf.bipublisher.ReportDataControl.generateReportMotives(
      at sun.reflect.GeneratedMethodAccessor1991.invoke(Unknown Source)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(

     at java.lang.reflect.Method.invoke(
      at oracle.adf.model.binding.DCInvokeMethod.invokeMethod(
      at oracle.adf.model.binding.DCDataControl.invokeMethod(
  8. Now you look for your classes in the stack trace and make sure that for every creation of an am instance, you also close it correctly.
Hope this can help you in finding database leaks.

Have fun.

dinsdag 15 april 2014

ContributeGroep Sessions for OOW14 & JavaOne14

In the spirit of sharing the wonderful things our consultants do at customers, we entered a few of their efforts as proposals for Oracle Open World and JavaOne.

Here is the list of our entries:

ID Proposed Session Title Type Event
CON4630 Integration of Neo4j with Webcenter Content Conference Session OOW
Had enough of the linear searching into your documents?  Needing a dynamic search path in runtime?  Welcome to the world of graph databases.  Using a graph database to perform searches, allows you to add functionality from a totally different order.  Think about searches on relationships between documents or show recommendations of other users into your information model, just to name a few.  Basically, we add relationships between metadata, content, persons, applications, even if they didn’t exist before.  Graph databases allow for a dynamic search structure in runtime, providing more flexibility and agility to your portal.
CON3759 Securing web services - How to make a complex task look easy? Conference Session JavaOne
Not many developers like to implement or expose secured web services.  This is probably because securing web services is a complex matter.  Next to the complexity, the client/server architecture comes into play.  Both sides, client and server, will have their own implementation.  In an ideal world your backend code will not be impacted with the technologies in which it is build, nor which type of security you would choose. Since we are not living in a perfect world, those differences do have a big impact on your approach in bridging those 2 worlds. During this session we will explain how an enterprise service bus could help you out.  Not only in solving this predicament, also in adding clarity in your separation of concerns design.
CON3690 Single-click deployment in Application Express Conference Session OOW
Software development with Oracle Application Express is by no means affiliated with enterprise web development. Oracle itself positions APEX as a so-called rapid application development tool, but recommends other products as soon as projects get larger and more complex. I honestly believe, that APEX has the potential to cope with these large-scale projects. Needless to say, you need more than just plain APEX...

We have managed to incorporate a set of powerful tools in our day-to-day development process. These tools in combination with APEX allow teams to build high-quality database applications in a professional way. It takes no more than a single click on a button to fully deploy your application(s) to a target environment.
CON4662 Taskflows on Sale Conference Session OOW
Get ready for an alternative way of navigating between taskflows within ADF.  Instead of providing only the valid navigation route, we allow for a more flexible system avoiding dependency problems. This opens the way for a free form navigation keeping into account security and his history.  One of the possible implementations of this solution is the creation of a menu that is based on beans or configuration files. Introducing history to this system can overcome the use of the browser back button.
The pattern is already in use at a Belgian customer in providing web(ADF)-based solutions. This customer will be present to explain the business value and agility this brings to their application.

maandag 24 maart 2014

User Experience Event @AMIS

I had the pleasure to join the user experience event @AMIS in the Netherlands.  It was an event organised together with Oracle UX Team.
As a general impression: very well done.  Parking, event location, different themes and very good content are the main positive points that jumps into my mind.
Unfortunately were the sessions programmed without buffer time, so that when you followed one session you are certainly to be late for the next session in another room.  Luckily sessions were repeated, so I didn't have to miss anything and I was able to visit the different Oracle stands explaining the diversity of UX.

For a full list of the sessions, take a look at
Here are some impressions I took from a couple of sessions:

UX Directions with HTML 5

This session was presented by a surprise act, in the sense that it was not Noel Portugal as presented on the site, instead it was a Belgian Oracle UX team member, namely Mark Vilrokx.  He showed us what they are working on.  It is very interesting to see how Oracle is investing into new technologies.  The things this guy showed us, was even new to our UX designer.  He focused on new possibilities with browser interactions in the mobile devices.  Knowing that a server can actually track your whereabouts, how bright it is in the room, whether you are using the application or not, is astonishing.  Not only passive information was gathered, also actions were triggered on the device.  Things like activating the vibrating functionality, playing a movie without user interaction.  This is a clear step forward for the browser in the war against native/hybrid applications.  Which made the off-line capability the only remaining drawback of the browser app in the mobile environment.

The architecture he used was a standard one: HTML5 -> Ajax calls -> REST Services.  To develop the mobile app, he used the Fuse Bootstrap, which is an adapted version of the Twitter Bootstrap.

Not presented in this session, but linked to the presenter is the Oracle Voice product.  It is a standalone product that will be released with the version 9 of HCM Cloud application.  It is an app that you can install on your phone or tablet, allowing you to speak to the application.  Not only in a question-response mode, where you answer to the question of the device, but also in entire sentences.
Example:  Create a new appointment Follow up meeting at Contribute next monday.  Not only will the solution interpret the sentence, it will also automatically fill in the fields of the new appointment.  For the missing information, extra questions will be asked.  When you have multiple opportunities for this account, a list of the possible opportunities for that account will be presented.
What is even more amazing is that the application does not need to learn.  You used to spend an hour saying/pronouncing bizarre and difficult words, this is not needed anymore.  Which indirectly means that you need to speak American English very well, preferably with a Californian accent :-).
A nice site note for my Belgian friends, this product uses Nuance for the Speech-to-Text and Text-to-Speech functionality.  Nuance bought the voice recognition software from our West-Vlaamse creatives L&H and is using their solution as one of the possible resolution software for the Speech-to-Text challenge.

Oracle's Strategy for Cloud User Experience

This session was presented by Jeremy Ashley, what a presenter.  It is not the content that struck me, instead his way of presenting and the feeling of clarity he gave us about this topic.   A must seen presenter!!
Back to the content.  The three main topics for Oracle UX are Simplicity, Mobility and Extensibility.
Simplicity is referring to the completeness of your solution.  It must be no too much, neither not too little, it must be exact for the job at hand.
Mobility is not about the mobile devices, instead it focusses on the way we work.  How we work differently now and in the future, then a couple of years ago.
The audience of these type of applications are people who will be using these not too often.  Hence the importance of keeping the distance between wanting to do something and doing it, must be a as small as possible.  The process that is the main driver, listens to the expression: Glass - Scan - Commit.  Resulting in an application giving the user an high level overview of the data, allowing him to drill down for more detail and then close his action.  This approach is very closely linked to the idea of 10-90-90, meaning that 10% of the tasks are performed by 90% of the users in 90% of the time.  These 10% are the main focus of these applications.  For full feature functionality, the user will be brought to the actual application.
Based on their experience with the Oracle Cloud solutions and fusion applications, the UX team delivers a set of patterns for you to use.  These can be found at
While most of the development and testing is done through HTML5 and then pushed to the ADF team, APEX is gaining momentum here.  In the "near" future, the APEX team will also include these patterns, just like ADF does.

UX Today with ADF

A presentation done by Sten Vesterli.  This session was really focused on ADF, like the title mention.  From the first moment it was clear that Sten was a guy with a lot of experience.  Here are some of his main points during the session:
  • A clear graph on the different types of applications you can build:
  • Use GUI mockup tools to create samples of your UI which don't have a high fidelity.  Reason: possible no feedback from end-users due to the hard work already done AND the possible feedback you get is that it could be finished tomorrow.  Conclusion: make sketches.  Possible tool: Balsamiq
  •  Another great point made by the presenter is the difference in type of application you can build between Forms,Apex and ADF.  While Forms and Apex applications have a 1-1 relationship with the underlying data model, ADF doesn't.  With ADF you have more a n-n relationship, allowing for a far greater UI experience.  Conclusion here: do not generate your UI screens.

Don't generate your user interface, design IT!

Session given by Lonneke Dikmans from Vennster, ACE Director in SOA & BPM.
The problem according to Lonneke is:
  • Using BPEL/BPM Human Tasks
    • too fine grained
    • too much data in process
      • need to adapt the process to changes in the UI
      • inflexible for use -> ex. no bulk operations possible
    • not enough data for the UI -> only task data is available
The solution for this is to take an alternative approach:
  • separate both solutions => BPEL/BPM and UX
  • take the following actions in your project
    • Define the different personas.  Who will be using the application, how often, from which type of devices, do they have knowledge of the app, ...
    • Define different scenario's for different type of personas
    • Design the interaction process
  • Ex: while the HR persons define the process, it should be the users themselves who define the UX/App
My question for Lonneke: how should we build BPM applications then?  From the BPM flow or build a separate application that calls on the BPM API's?
Answer from Lonneke: take the latter one.  This way the UI is really build for the end user and you have more flexibility.

Leveraging FMW for UX

Another session from Lonneke Dikmans.  This session was a simple overview of the main components of the FMW stack of Oracle.  Some points worth remembering:
  • How to test the experience:
    • Do Usability testing
    • Do SCA testing => use mock responses
    • Do Load testing
  • Tip: learning by doing : learn whether something works or not
    • ex: google class: 1st version done in 2 days
  • Monitoring

Oracle and Mobile: From Design to Device; The tools that make it happen

Session by Luc Bors.  Simple overview of ADF Mobile.
There seems to be a misunderstanding in the comparison between ADF mobile or hybrid solutions and native solutions.  According to Luc and Oracle(Oracle is saying this also, I know I did it also before), the effort in making a native app is a lot bigger and more difficult then making a hybrid solution for multiple platforms.  It is true that you need to make an app for each platform and that reuse at the level of the mobile app is almost zero.  Nevertheless, making these mobile apps is only a small part of the total project.  The major effort resides in building the back-end services, including security.
Take into account that the hybrid solution from Oracle only supports 2 platforms, while there are 4 in the market.  

According to me is Oracle ADF great at the data oriented apps, while building great looking apps are better done in native solutions.  One of our main references in this area is the Royal Belgian Football Association, who are really looking at their target audience and decide on the technology uses, like Oracle Forms, ADF, ADF Mobile and native mobile applications.


A great event with may thanks to AMIS and Oracle for organising this.  All speakers at this event where ACE Directors or Oracle employees, pushing the available knowledge at this event at a great height.  Looking forward for a next event like this.
At the following URL you can have a look at the presentations themselves:

vrijdag 3 januari 2014

ADF: Putting IE in a certain document mode


In our ADF application we used some javascript to put the focus on the first field of an af:query component.  This seems to work great when running directly from JDeveloper, but doesn't if the application is deployed on a separate WLS.


First we thought it is the javascript itself, but this was not possible since we do not change the code.
One of our JSF experts, Rudy De Busscher, figured out that the compatibility mode of IE changed between both deployments.  How or why, remains still a mystery.

So, now we need to force IE to keep his IE8 compatibility mode.  To do this, you need to put a meta tag in the header of your page.  This is easy enough to do in ADF, in the af:document tag, add a facet with this tag in, like this:

<af:document id="d1" title="Test Javascript">
   <f:facet name="metaContainer">
         <meta http-equiv="x-ua-compatible" content="IE=8"/>

The problem with this solution is that the meta-tag will be put after your stylesheet tags, which will result in the fact that this meta-tag is not taken into account.
To solve this problem, you need to perform a small operation.  Since this seems to be a hack, I'm not sure it will work in all ADF versions.  I tested it with ADF
Make your code look like the following:

      <head><meta http-equiv=x-uq-compatible" content="IE=8"/></head>
   <af:document id="d1" title="Test Javascript">
To check whether IE is taken this into account, use the F12 key to open the Developer Tools, in the top menu you will find the Document Mode.  This will indicate in which mode you are operating.  Also have a look at the header of the document, the meta-tag of http-equiv should be after the title, but proceeding the stylesheet tags.

Have Fun.