Get "ORA-56901: non-constant expression is not allowed for pivot|unpivot values" ? Not a big deal, check a workaround!

Problem

Get "ORA-56901: non-constant expression is not allowed for pivot|unpivot values" while trying to execute SQL statement with pivot clause. Lets consider this tiny sample:
  • Here is the table:


  • SQL statement with PIVOT clause and constant expressions, which executes successfully:
  • SQL statement with PIVOT clause and dynamic expressions, which fails:

Solution
  • Workaround - just don't use PIVOT at this point, simple group by clause will fullfill requirement:

  • Here is the .sql file with presented content to download.

Oracle DB version 11.2.0.4.0 

ADF application layout messed up after migration to 12.2.1 ... What might be the reason ?

Problem:

After migration of ADF application from 12.1.3 to 12.2.1 layout was messed up. In particular - component stretching was disordered.

Reason:

As ADF is going step by step to adaptive layouting, component stretching is optimized accordingly.
In version 11.1.1.7 new context parameter was introduced: oracle.adf.view.rich.geometry.DEFAULT_DIMENSIONS. It basically tells what dimensions for stretchable components should be applied by default. For example if you set dimensionsFrom="children" to most top component in the hierarchy, all its children should inherit the same. But as a consequence, a legacy layout, which was organized with absence of this parameter can be disordered. In my case it happened that this parameter was forced in web.xml on migrattion to 12.2.1 and set by default to "auto". Previously we were not able to modify it and it was "parent" by default.

Solution:

Modify oracle.adf.view.rich.geometry.DEFAULT_DIMENSIONS parameter to "not set" or "parent" or just remove it from web.xml:



Useful links:

JBO-26041: Failed to post data to database during "Update"... What might be the reason?

Problem:

Exception during data save is thrown:
oracle.jbo.DMLException: JBO-26041: Failed to post data to database during "Update": SQL Statement "UPDATE...
It might be followed by similar to this one:
java.sql.SQLException: Fail to construct descriptor: Unable to resolve type "NVARCHAR2"...
Solution:

Check if  SQLType attribute of an entity/view object attribute being updated is correct. For example it can be set to "STRUCT" when "VARCHAR" is necessary.


Comment:

   Sometimes framework generates wrong type here. For example when adding new NVARCHAR2 column into entity, JDeveloper generates new custom datatype domain (i.e. Nvarchar2). Usually it is not necessary unless you really want to implement there something custom. But in most cases standard String fits well. So you tune newly added attribute to be of type String, everything then looks fine and works fine untill data update does not become necessary. And then you get exception noted above. Thats because Jdeveloper did not tune all necessary attributes for entity object attribute. It left SQLType unchanged and set to "STRUCT". So in this case after manual change to SQLType="VARCHAR" problem was solved. This applies either to entity and view objects.
   However this exception is a generic one and there can be a plenty of reasons to get it. But this is one of those. Check out if its not your case. Good luck!

JDeveloper version 12.1.3.0.0

Override inputListOfValues component model and Continuous Query Notification sample in ADF

Requirement:

Standard ADF inputListOfValues component should behave like this:
    • When pressed "Search", action should stop and wait untill any change happen in DB table being searched
    • When change happen in DB table, search should resume among updated data. If no change happen it should resume after 10 seconds timeout
    • In addition - search input field validation should happen on "Search" press. If there is no at least one field filled with at least 3 symbols - error should be shown.
Well, this requirement may look sensless, but this is just a PoC of functionality, of what and how is possible to implement. Maybe a peace or a whole of this sample can be adapted to your case.

Solution:
  • Create a separate class which extends ListOfValuesModel, put custom logic there for validation and processing query
  • Use Continuous Query Notification (CQN) or in earlier Java releases called Database Change Notification (DCN) for delivering event from DB to application
  • Use Java latches (CountDownLatc) for suspending and resuming search after either an event from DB or timeout occurs.
This PoC will use Oracle DB and HR schema for sample data. Will put LOV on departmentId attribute of EMPLOYEES table. Will suspend searching in DEPARTMENTS table untill any change there happens.
  • Lets start from creating custom class for inputListOfValues model:


  • Configure LOV for departmentId in EmployeesView. Set UI hint for list type "Input Text With List of Values". Create main.jspx file and drag'n'drop EmpoyeesView data control into it, render as regular ADF table. Check whether inputListOfValues component is rendered for departmentId attribute:

  • Create a managed bean mainBean.java for main.jspx and put there getter of inputListOfValues model. Reassign model in main.jspx.
  • Get back to custom model class ListOfValuesModelEx. Put necessary validation and change subscribtion logic into method performQuery. Special attention to CountDownLatch utility here - it is used to suspend query execution and wait either till it is notified from the CQN listener or timeout occurs. The key points are emphasized in red:
For more detailed sample description of Continuous Query Notification (CQN/DCN) usage, please have a glimpse to my previous post: WebSocket together with DB Change Notification in ADF for immediate data refresh in browser (complete sample).

Thats it,  please download sample application to find more details and test it!

JDeveloper version 12.1.0.0

WebSocket together with DB Change Notification in ADF for immediate data refresh in browser (complete sample).

Requirement
  • We have DB table contents displayed in ADF GUI
  • After changes in that DB table (by anyone else in the background) we want to immediately display in our currenlty open browser session
Solution

To achieve our goal we need somehow to know when changes in DB happen. Once we know when, need to notify browser session in order it could be refreshed. This time we'll exploit two Java offerings:
  • JDBC Database Change Notifications (DCN) - to subscribe and receive notifications when changes happen in DB
  • WebSocket - protocol other that HTTP which works in request/response manner. Basically its full-duplex communication channel over a single TCP connection. That means we can have bidirectional continuous communication between server application and browser.
Lets do simple PoC with EMPLOYEES table from HR schema. Just display its contents and immediately refresh if changes happen in this table.
Make sure you have access to any Oracle DB (recommended >= 11.1 - more DCN features available) HR schema.
  • Create basic ADF Fusion Web Application. Create bussiness component for EMPLOYEES table and generate simple .jspx file with its contents displayed on the screen (ADF table component is fine):

  • That was the very basic so far. Now will create WebSocket project which will serve as listener for DB changes and the one which send notifications to browser. In the same recently created application, create new project of type "WebSocket":

    • Create java class which will implement WebSocket service. Annotate it as a server endpoint:

    • Click on code assistant icon and select "Configure Project for Web Socket":

Recent action has been generated web content folder with necessary belongings. 
    • Implement main service methods. Lets choose annotation driven implementation and add following methods with according annotations:
Note that method processMessageFromClient will not be used by fact in this sample, because it is not necessary to fullfill our requirement. But we just add a stub of it to be able to utilize it later if necessary. We have full WebSocket service structure defined already. It can be deployed although will not process anything benefiting our needs.
  • Now lets move to ViewController project and do necessary steps to be able to  establish and close connection between browser session and WebSocket service. Also implement refreshing of data in the browser.
    • As there are no dedicated ADF components for connection establishing and maintaining it, will leverage basic javascript to implement that. Add WebSocket.js file to ViewController project:

Notice WebSocket service URL marked with green rectangle. You need to check if its the same in your enviroment. Target URL is displayed right after running service in integrated server:

    • Create two buttons in the GUI which do connection and disconnection accordingly. Either of them will be client listener based and call javascript functions. Add javascript resources to page:


    • Add another button id="b1" (visible in screenshot above) to refresh table content when event from WebSocket service is received. You can make it either visible or invisible. It doesn't matter, because its action is queued using javascript means (function refreshJs() ) anyway. Add server listener which invokes bean method to refresh EmployeesView by re-executing its iterator:


At this moment were done with basic WebSocket connectivity implementation. We can run either ViewController and WebSocket project and try to enable and disable WebSocket connection. Check how it works:

  • Lets move back to WebSocket project implementation. There we still need logic which takes care of data change notifications from database.
    • Ensure that DB user HR has change notification privilege: 
grant change notification to hr;
    • Add DB notification subscription controller and listener classes. Add WebLogic remote client library to project:


    • Configure data source for HR connection in WebLogic and use it in DBChangeNotification.java for accessing DB:


    • Configure DB Change registration in DBChangeNotification.java class. Two main things worth to pay attention to: 
      • There are set two registration properties: DCN_NOTIFY_ROWIDS (tells that we are interested in changed rowids and want to get them in the payload) and DCN_QUERY_CHANGE_NOTIFICATION (tells that our registration is of higher granularity and is select statement based rather than DB object based).
      • In this sample statement "select salary from employees where employee_id=100" is used,  by which we ask DB to notify only when salary of employee whose employee_id="100" changes. DCN_QUERY_CHANGE_NOTIFICATION property is needed if we want filtering against some criteria and focus on certain columns take effect. If this property would be missing - even with such quite fine granularity query DB would notify about any change in EMPLOYEES table. 

    • Add Service.SendMessageToClient call in DB change listener (DBChangeNotificationListener.java):


  • Add javascript logic inside onMessage function to queue refresh action to ADF application from browser:

  • We're done, now can test the behavior:
    • Run application and WebSocket service
    • Enable WebSocket connection by clicking button in the GUI
    • Change salary of employee_id="100" using SQLDeveloper or other SQL execution means. 
    • Change should be immediately visible in the browser. 
In the Integrated server and browser logs can see notification cycle logged:

 

Note: for cleaning DB registrations can use app described in my other post How to select all JDBC Database Change Notification Registrations and clean them?

Demo application can be downloaded here



JDeveloper version 12.1.3.0.0

How to select all JDBC Database Change Notification Registrations and clean them?

Question:
How do I select all database change notifications registered via JDBC driver by my DB user?

Answer:
select * from user_change_notification_regs


Question:
How do I clean all of my DB change notifications?

Answer:
Database change notifications registered via JDBC driver can be deregistered only the same way - using JDBC driver. Therefore you need to execute this code:

This code can be downloaded here.

Comment:
More info about JDBC database change notifications can find in Database JDBC Developer's Guide

How to add ADF source code in JDeveloper 12c ?

Problem

I want to have ADF source code available in my project.

Solution
  • Open service request ticket at My Oracle Support and ask to provide you a source code for ADF. Oracle staff will provide you a security code and a link to download a zip file.
  •  Open JDeveloper, select Tools -> Manage Libraries... 
    • Click Libraries tab
    • Select folder User
    • Click New
    • Select Source Path on the right pane then click Add Entry... 
    • Point to zip file received from Oracle

  • Add defined library to particular project
    • Right click on project folder and select Properties
    • Select Libraries and Classpath on the left pane
    • Click Add Library on the right pane
    • Point to previously defined ADF source code library which should be available in User folder
  • You should be able to access ADF source classes now!
JDeveloper version 12.1.3.0.0