Sunday, January 22, 2017

SQL Bind Variable Support in ADF BC REST

Is not that obvious from Oracle ADF BC REST developer guide how to provide value for bind variable defined directly in the View Object SQL statement. I did research around this and would like to post few hints to make your life easier, if you have same requirement - pass values from REST request to View Object required bind variables. This topic is especially useful, when you want to reuse existing ADF BC implementation for ADF BC REST access.

We are going to use View Object Row Finder. Oracle ADF BC REST developer guide explains how to use Row Finder with View Criteria. In our case we have different situation -  we would like to use Row Finder for required bind variables, referenced directly in SQL statement.

You can't define Row Finder without View Criteria. First trick is to define empty View Criteria, just to be able to define Row Finder - we are not going to use View Criteria functionality, our bind variables are referenced directly in SQL WHERE clause:


Once Row Finder is defined, you are going to see bind variables listed. Keep in mind - this doesn't means bind variables are referenced by Row Finder, they are just listed for possible use. Now main trick - go and define some dummy expression for each bind variable you want to use in REST request (make sure to uncheck - Save expression to groovy file):


This action would generate Groovy expression for each bind variable and list these bind variables under Row Finder. Go to source of View Object to see the structure:


Now remove Groovy expressions assigned to each bind variable - we don't need them, keep only bind variable names assigned to Row Finder:


Visually in the wizard is going to look like there are no changes made - but we keep bind variables under Row Finder tag now:


Bind variables are included directly into SQL statement WHERE clause:


ADF BC REST service is defined in regular way, no special tips here:


This is how REST call looks like: Departments?finder=RESTFilter;depNameVar=IT.locIdVar=1700. We include Row Finder name and two bind variables with values:


In the background we could check ADF BC log, where it prints SQL statement with both bind variables assigned with values:


Download and browse sample application ADFBCRestApp from my GitHub repository - jetcrud.

No comments: