Change ADF BC Data Update Locking with FOR UPDATE WAIT

Each time when data is changed and updated through ADF BC, before posting changes to DB, SQL query with FOR UPDATE NOWAIT is generated and executed. In case if other process locks row to be updated, or another user in the same moment is updating it, error will be generated and update will be stopped. There might be use cases, when you would like to wait for certain period of time, until row will be unlocked and then commit row changes. This is especially true, if 3rd party (PL/SQL) process is updating rows and you have defined change indicator attribute in ADF BC (see my previous post - ADF BC Version Number and Change Indicator to Track Changed Rows).

We can change default behavior, instead of requesting for immediate lock - we can wait a period of time. If lock becomes available during this period, session acquires lock. If row remains locked, error is returned. Instead of default FOR UDPATE NOWAIT, we can generate FOR UDPATE WAIT (time period in seconds).

To override default behavior, we need to specify custom SQLBuilder class. This can be registered in Application Module configuration jbo.SQLBuilder property:

Class must extend from OracleSQLBuilderImpl and override getSqlVariantLockTrailer() method. My sample application is implemented to return FOR UPDATE WAIT 30:

We can do a test. We can simulate PL/SQL lock by executing SQL query with FOR UPDATE from SQL Developer:

Try to update same row from ADF BC with default behavior, error will be returned - "Failed to lock the record, another user holds the lock". This is expected, because row remains locked:

With overriden SQL Builder, FOR UPDATE WAIT 30 is generated. It waits 30 seconds, as soon as lock is removed - lock from current session is set and row data is updated:

Download sample application - LockUpdateApp.zip.

ADF BC Version Number and Change Indicator to Track Changed Rows

One of the common use cases in enteprise applications is to track concurrent user changes. There are two types of changes possible - when two real users are changing data in the same row, or when single user is changing data and same row is updated by PL/SQL procedure/function (all happen in the same user session). In the first case, we would like to inform a user - row data was changed (two different users changing data). In the second case, there is no need to inform user (data wasn't changed by another real user, it was changed by PL/SQL function/procedure invoked in the same session).

ADF BC by default tracks all attributes and checks if they values were changed. This would mean, if any attribute value is changed directly in DB, EO cache will be out of synch and changed row error will be reported. We can minimize number of attributes to be checked to single attribute - Version Number. There must be additional number type column created in DB table, we are going to use it for Version Number in the EO. EO attribute should be marked as Change Indicator and set to be "version number" in Track Change History property. This means, row will be considered as changed by another user, only if Version Number attribute value will be changed. All changes happening directly from PL/SQL will not increment Version Number (it will be incremented only for the changes submitted from ADF BC):

There should be new column created in the DB for Version Number attribute:

Let's see how it works. I will use two different browser sessions, to simulate two users. Change value for First Name, take a look into Version Number. Value is equal to 4, before Save:

Data is saved successfully to DB. New value 5 is assigned automatically for Version Number:

Switch to different session now. Version Number is equal to previous value 4 (this session was opened before recent commit from first session). Both sessions are working with the same row. Change value for Salary:

Try to save this change. You will get standard error message, informing user about changes in the same row. This happens, because Version Number value doesn't match with recent Version Number for the same record:

At the same time, Version Number is synchronized automatically (updated to 5) and user can try to commit his changes again. This time data is saved and Version Number is increased to 6:

We should check now, how it works data is changed in the DB directly, without increasing Version Number (the case of update happening from PL/SQL). I'm changing Employee_Id = 100 record directly in DB (changing Salary value). Version Number is not changed:

Go back to Web session and change First Name value, press Save:

Despite data was changed in DB, save was successful - Version Number was recent. First Name was successfully changed and Version Number was increased to 7:

Download sample application (make sure to create manually VERSION_NO(10, 0) column in the EMPLOYEES DB table) - ADFChangedRowApp.zip.

How To Be More Productive with JDeveloper 12c Application Redeployment

When you work on larger JDeveloper/ADF 12c project, most likely you will experience issues with application redeployment (rerun). These two issues will be encountered:

1. It doesnt work anymore (it worked on 11g) to simply rerun application (applies for 12.1.3 and 12.2.1), without stopping it and then running again. In most of the cases if you try to rerun application, some strange errors are returned or changes are not visible at all. It takes time to stop application and run it from scratch

2. Oracle recommended approach is to use Make All to recompile the code and then refresh or reopen browser (Servlet Reload or Fast Swap options). This should bring the changes in ADF BC, Bindings, Task Flows and UI. While this works on simple use cases, often it doesnt work if many files are changed at once

What if you want to re-run application in JDeveloper 12c, same as it was possible in 11g? Yes, you can do it. There is a simple configuration change in JDeveloper 12c, to be able to rerun application without issues:

Go to JDeveloper preferences wizard and select Run option. Uncheck option "Enable Application Rerun Optimizations" - this will allow to rerun ADF application without errors and without a need to stop and start it manually:

Once application rerun optimizations are disabled in JDeveloper, you should add simple configuration to your ADF application (to make sure application redeploys fast). Disable auto generation for JDBC descriptors in application profile (Deployment -> WebLogic):

Provide external Data Source in AM configuration, or set direct JDBC URL (suitable for testing locally, as in my case):

Do above steps, when Make All (Servlet Reload or Fast Swap) doesn't work for you:

Accessing Oracle Mobile Cloud Service (MCS) REST from Postman

Do you want to test in your environment REST service running on Oracle MCS cloud? I have implemented public REST service hosted on Oracle MCS, you can call it from Postman application (Google Chrome extension).

Implemented use case - SOAP Connector in MCS is reading Stock Quote information for the company supplied in the request. Information is transformed by MCS Custom API (with Node.js) into REST structure and returned to the client through MCS Mobile backend.

Here is the REST GET url to invoke in Postman: https://mobileportalsetrial1304dev-mcsdem0001.mobileenv.us2.oraclecloud.com:443/mobile/custom/STOCKQUOTE_API_REDSAMURAI_BLOG_1/stockquote/ORCL. Try to run it, you can replace ORCL with another company ID. To execute this REST request in Postman, you need to provide configuration file (this allows to call MCS). Configuration file can be downloaded from here - Postman_MCS_1.json. This file contains all required info to make REST request from Postman:

To add configuration file to Postman, go to Configuration and upload it through Import data option:

Make sure MCS profile is selected:

When you copy paste REST URL, go to Headers section and select MCS from Presets - this will add two required headers:

You should see company stock information returned back from REST call:

Let's switch to MCS. There are three main concepts in MCS - Mobile Backend, custom API, Connector. Mobile Backend is a service interface, it allows to configure mobile access functionality and contains a list of Web Services (API's) exposed to the clients. It allows to track service usage and view statistics in the dashboard:

My example contains single Web Service (custom API). This service is responsible to handle REST GET method and return company stock quote data:

Custom API defines REST resource /stockquote/{company}, this is what we are executing in Postman URL:

Resource /stockquote/{company} is configured with GET method:

GET method is configured to handle two type of responses - 200 (OK) and 404 (Not Found):

To provide API implementation (mapping between Connector and API response), we can download (see example - stockquote_api_redsamurai_blog_1.zip) Node.js file from MCS and edit it locally (edited file can be uploaded back in the archive):

We need to specify Connector name to be available in Node.js, this is how it will call Web Service defined in the Connector:

Node.js function is responsible to call Web Service from Connector, pass parameter and check the result (here you can do complex transformations between SOAP and REST):

I'm using SOAP connector, pointing to external Web Service to fetch Stock Quote information for the company:

OFM Forum Session Slides - Oracle JET and WebSocket

I would like to post slides from my session on Oracle Fusion Middleware Forum - Oracle JET and WebSocket. This session was done today.

Slides are available on slideshare:

Sample application used for the demo, can be downloaded from here (it consists of three parts - WebSocker server implementation, ADF BC tester application to generate continuos DB updates and JET application) - Oracle JET Live List with WebSocket.

ADF application with live data DVT component rendered in JET can be downloaded from here - When JET Comes To Rescue - Live Data Charts in ADF.

Oracle JET and ADF BC REST Basic Authentication

You might be interested to check my previous sample about CRUD implementation in JET - Handling ADF BC 12.2.1 REST Validation in Oracle JET. I'm going to describe how to access secure ADF BC REST service from JET, based on the CRUD sample app. We need to pass authorization header on each REST request, this way server can authenticate user and authorize access to the REST resource. There are couple of other tips applied on ADF BC REST service side, all described below.

Blog reader rjahn shared a link in this blog comment - Oracle JET - Rendering Table from ADF BC REST Service, he describes how to pass authorization header using oauth property, while executing GET and fetching collection in JET. I'm using same approach for GET, but for PATCH, POST and DELETE authorization header is set in slightly different way.

First of all we need to take a look into ADF BC REST security implementation. Each time when custom header is attached in JET, it executes OPTIONS method without appending authorization header. I have disabled security check for OPTIONS in web.xml:

Important tip - by default security operations granted to ADF BC resource are all assigned through single permission (in jazn-data.xml):

I have found this doesn't work, even from Postman - only first operation from the list is authorized. This must be a bug in ADF BC REST security. Workaround is to separate each operation into different permission group manually:

GET executed from JET is authorized and data is coming through:

We can see in network monitor recorded GET execution from JET - authorization header is submitted along with request:

Authorization header is encoded with JavaScript btoa function. Obviously it must run on HTTPS to be completely secure, otherwise someone can intercept and decode the password. Authorization header is being constructed in JET (you would fetch username/password from HTML form, etc.) and is attached to the collection (this way it gets included into request header executed by GET). It doesn't work to attach it to collection through header property (as it works for POST, PATCH and DELETE), but it works with oauth:

Authorization header can be set for PATCH. Data can be updated through secure service:

Authorization header is present in PATCH request:

Authorization header is injected through property - headers (differently than for collection and GET). It didn't work to use oauth here:

Create case is executed through POST, security is enforced through authorization header here also:

We can see header is present in the request:

Authorization header is attached in the same way as for PATCH:

Delete case is executed through DELETE, same authorization header is applied:

Header info:

Authorization header is attached to JET destroy operation:

Download sample application (JET and ADF BC code) - JETCRUDApp_v5.zip. You should include JET code into JET toolkit distribution.

ADF BC REST Support for List Of Values (LOV) Data

ADF BC REST service out of the box supports LOV list data. You can define LOV for ADF BC View Object attribute and use it straight away in REST service. This is especially useful when LOV list is filtered based on other attributes from current row - no need to collect and send these attributes to the service separately, filtering logic will be handled for you in ADF BC backend. One more benefit - you can reuse existing ADF BC with LOV's implementation and expose it through REST service.

Below you can see employee #108 attributes with values returned by REST service, generated on top of ADF BC View Object. ManagerId attribute in View Object is configured with LOV list. Based on this configuration, each item in REST collection will have a link to REST resource providing LOV data (Employees/280/lov/EmployeesLovView1). For employee #108, it retrieves LOV data from VO EmployeesLovView1:

We can get LOV list entries for employee #108 by executing simple REST request, no need to specify additional parameters (Employees/280/lov/EmployeesLovView1):

LOV VO is set with View Criteria to return a list of possible managers, employees with the same job, excluding employee himself:

LOV View Accessor in the main VO (Employees) is configured to use employee ID and job ID from current row (current REST collection item) - in this way LOV list will be filtered automatically, by View Criteria:

The only thing that needs to be done to enable LOV support - define LOV for manager ID attribute in the base VO:

As you see, it is pretty easy to reuse ADF BC LOV's in REST service. Download sample application - ADFBCRestApp_v6.zip.