Using Bind Variable to Implement Range Selection Declaratively

A bind variable is a placeholder in a SQL command for a value that will be supplied at runtime by the application. Because you can change the value of the bind variable as many times as needed without changing the actual text of the SQL command, the database can reuse the same statement over and over without incurring the overhead of reparsing the command each time it's executed.

In Oracle ADF, all bind variables are defined at the level of the view object. Bind variables provide you with the means to supply attribute values at runtime to the view object or view criteria. You can define a default value for the bind variable or write Groovy Expressions for the bind variable that includes dot notation access to attribute property values. If you do not supply a default value for your named bind variable, it defaults to the NULL value at runtime.

After defining the bind variables, the next step is to reference them in the SQL statement. While SQL syntax allows bind variables to appear both in the SELECT list and in the WHERE clause, you'll typically use them in the latter context, as part of your WHERE clause.

There are many uses of bind variables and three styles of bind variables (i.e., '?' for JDBC Positional, :1, :2, ... for Oracle Positional, and :var1, :var2, ... for Oracle Named). In this article, we'll show one usage of it with Oracle named binding style.

The Task

In the following UI design, a time range can be specified by the user and used to display test results in that period. The test results will be displayed in line graph provided by Oracle Faces Rich Client Components. The line shows the percentage of failures in a specific test run. The Figure below shows what to expect when the design is finished.

To support this design, we need to create a read-only view object named TestTrendVO in which its query statement looks like this:
SQL Statement 1:

SELECT
    TRUNC(XTM_SEL_TEST_RUNS.START_TIME) AS START_DATE,
   (SUM((SELECT count(*) from XTM_SEL_TESTS xst where
      xst.TEST_RUN_ID = XTM_SEL_TEST_RUNS.TEST_RUN_ID AND
      xst.STATUS = 'Failure')) /
    SUM((SELECT count(*) from XTM_SEL_TESTS xst  where
      xst.TEST_RUN_ID = XTM_SEL_TEST_RUNS.TEST_RUN_ID)) * 100) AS FAILURE_PERCENTAGE
FROM
    XTM_SEL_TEST_RUNS
WHERE
  ((TRUNC(XTM_SEL_TEST_RUNS.START_TIME) >= :TSBegin )
  AND
  (TRUNC(XTM_SEL_TEST_RUNS.START_TIME) <= :TSEnd ) ) GROUP BY                        TRUNC(XTM_SEL_TEST_RUNS.START_TIME) 

Notice that there are two bind variables (i.e., TSBegin and TSEnd) being referenced in the query statement which needs to be defined later in the TestTrendVO view object. This query will also be ordered by START_DATE by using the Order By clause in the Create View Object wizard. In the view, we have defined two view attributes (or query columns):

• StartDate(Calculated 'START_DATE')
• FailurePercentage(Calculated 'FAILURE_PERCENTAGE')

Note that TRUNC function is used to retrieve the date part of a timestamp (i.e., XTM_SEL_TEST_RUNS.START_TIME ).

The Steps

To achieve this task, the required steps are outlined below:

1. Creating a Read-only view object
2. Adding TestTrendVO view object to the application module
3. Creating a databound graph with a time selector

Step 1 — Creating a Read-only View Object

For our design, we will use a read-only view object (i.e., with no entity usage). Read-only view objects do not pick up entity-derived default values; they do not reflect pending changes; and they do not reflect updated reference information. In contrast to entity-based view objects, read-only view objects require you to write the query using the SQL query language. To create a read-only view object (i.e., TestTrendVO), you can use the Create View Object wizard, which is available from the New Gallery.

To create a read-only view object:

1. In the Application Navigator, right-click the project in which you want to create the view object and choose New.
2. In the New Gallery, expand Business Tier, select ADF Business Components and then View Object, and click OK.
   
   If this is the first component you're creating in the project, the Initialize Business Components Project dialog appears to allow you to select a database connection.
3. In the Initialize Business Components Project dialog, select the database connection or choose New to create a connection. Click OK.
4. In the Create View Object wizard, on the Name page, enter a package name and a view object name (i.e., TestTrendVO). Select Read-only access through SQL query to indicate that you want this view object to manage data with read-only access. Click Next.
5. On the Query page, paste SQL Statement 1 into the Query Statement box and enter START_DATE into the Order By clause.
6. After entering the query statement, click Next.
7. On the Bind Variables page, enter the name (i.e., TSBegin) and data type (i.e., Date) for the new bind variable by following the instructions here. Because the bind variables share the same namespace as view object attributes, specify names that don't conflict with existing view object attribute names. As with view objects attributes, by convention bind variable names are created with an initial capital letter, but you can rename it as desired.
8. On the Attribute Mappings page, click Finish.

Step 2 — Adding TestTrendVO View Object to the Application Module

An application module can expose its data model of view objects to clients without requiring any custom Java code. In this article, we assume you have created an application module (i.e., SelTestPortalAM) to be used in your UI components. The next step is adding the new TestTrendVO view object to it.

After you've created a new application module, you can edit any of its settings by using the Edit Application Module dialog. To launch the editor, choose Open from the context menu in the Application Navigator, or double-click the application module.

To add TestTrendVO view object to the application module:

1. In the Edit Application Module dialog, select Data Model page
2. Select TestTrendVO view object from the tree of available view objects and click '>' to create a named instance of the view object in the data model.
3. Click the pencil icon next to View Instance and change its name to TestTrend
4. From the Data Controls panel, click the refresh icon to include new view instance in the data model of SelTestPortalAMDataControl

Step 3 — Creating a Databound Graph with a Time Selector

To create a databound graph with a time selector:

1. From the Data Controls panel, select the collection (i.e., TestTrend) and expand the Operations node to display the ExecuteWithParams operation.
2. Drag the ExecuteWithParams operation and drop it onto the page in the visual editor.
3. In the Create menu, select Parameter and then ADF Parameter Form...
4. In the Edit Form Fields, click OK
5. Drag the TestTrend collection and drop it onto the page below the form
6. In the Create menu, select Graph...
7. In the Component Gallery, select Line category and click OK
8. In the Create Line Graph dialog, drag FailurePercentage attribute from the available list to Lines box and drag StartDate attribute from the availabe list to X Axis box
9. Click OK

Explanation

The new UI allows user to specify the values of two bind variables: TSBegin and TSEnd at runtime. When the user clicks on the Execute button, SQL query statement associated with the view object is fired. The resulting rowset is then displayed in the graph underneath.

Reference(s)

1. How-to deploy and run the ADF Faces Rich Client Components Demo on Oracle WebLogic Server
2. Using Bind Variables
3. Working with Bind Variables
4. Book Review: Developing Web Applications with Oracle ADF Essentials