Design Patterns for Database API Testing 2: Views 1 – Design

Last October I gave a presentation on database unit testing with utPLSQL, Oracle Unit Testing with utPLSQL. I mentioned design patterns as a way of reducing the effort of building unit tests and outlined some strategies for coding them effectively.

In the current set of articles, I develop the ideas further, starting from the idea that all database APIs can be considered in terms of the axes:

  • direction (i.e. getter or setter, noting that setters can also ‘get’)
  • mode (i.e. real time or batch)

For each cell in the implied matrix, I construct an example API (or view) with specified requirements against Oracle’s HR demo schema, and use this example to construct a testing program with appropriate scenarios as a design pattern. Concepts and common patterns and anti-patterns in automated API testing are discussed throughout, and these are largely independent of testing framework used. However, the examples use my own lightweight independent framework that is designed to help avoid many API testing anti-patterns. The code is available on GitHub here, BrenPatF/trapit_oracle_tester, and includes both framework and design pattern examples.

Behind the four examples, there is an underlying design pattern that involves wrapping the API call in a ‘pure’ procedure, called once per scenario, with the output ‘actuals’ array including everything affected by the API, whether as output parameters, or on database tables, etc. The inputs are also extended from the API parameters to include any other effective inputs. Assertion takes place after all scenarios and is against the extended outputs, with extended inputs also listed. This concept of the ‘pure’ function, central to Functional Programming, has important advantages in automated testing. I explained the concepts involved in a presentation at the Oracle User Group Ireland Conference in March 2018:

The Database API Viewed As A Mathematical Function: Insights into Testing

In this 2-part article, I present a design pattern for testing views. I start by discussing when and how to test views. Unlike in the design paatern of the first article in the series, Design Patterns for Database API Testing 1: Web Service Saving – Design, test data has to be created during testing of views, and a very general approach to creating and selecting the test data is proposed. The use case for the design pattern is described, and scenarios and sub-scenarios are defined conceptually. Finally, the output from the testing is presented, with notes.

The second post provides some code extracts, with notes: Design Patterns for Database API Testing 2: Views 2 – Code.

When to Test Views

Views can be simple or complex, or, as I categorise them in Brendan’s 2-Page Oracle Programming Standards, thin or thick, where thick views include table joins and thinviews don’t. Thin views do not normally require testing while it may or may not be appropriate to test thick views.

As explained in the second part of the first article mentioned above, method-based testing is a bad idea, and occurs when the test suite is based on testing all the methods in a package, rather than units of (external) behaviour (often corresponding to procedures prefixed AIP in a common naming convention). Similarly, we can consider views in the same way as methods and ask whether they represent testable units of behaviour or are merely internal code structures, which should not normally have individual automated tests for the reasons given there.

Good examples of views that should be tested would be those that form the basis of complex data extraction to file, by ETL tools such as Informatica, or those that form the basis of reporting tools such as Business Objects. In fact, it is very good practice to place SQL for these tools into views precisely so that they can be tested.

How to Test Views Using a PL/SQL Testing Framework

In order to leverage a PL/SQL API testing framework to also test views, the API test package procedures call a library procedure passing the name of the relevant view: The library procedure returns the result of querying the view as an array of delimited strings, and the API test procedures then compare the results against their own expected results.

Each API test procedure will have its own setup local procedure to create test data, and we need to discuss the issue of distinguishing test data from pre-existing data.

Test Data

In the earlier article on database save procedures, we did not create any test data within the testing code itself, but the base procedure did create data, and those were queried back for assertion. In order to select only the data created by the procedure call a prefix was used in one of the string fields which was assumed not to exist already. This is a workable approach in some cases, but may not be possible in general. Let us consider the different types of database data that may affect our testing:

  • Data created by the base code being tested
  • Data created by test code to be read by the base code
  • Data not created by test code to be read by base code

In order to verify that the program calls are giving results as expected, the test code needs to know all the data that influence the results, not necessarily just directly created data. Our view testing use case described below has an example where the results depend on an aggregate of all the records on the database. This is a problem when we have a shared database, where we cannot freeze the data at the time of test development. In order to handle this problem, we propose to borrow a technique used in Oracle’s ebusinees applications.

Partitioning Views with System Contexts
In Oracle ebusiness’s multi-org implementations, transactions are partitioned by a numeric identifier for the organization owning the transaction. This org_id value is stored in a column in the base table on transaction creation. Within the application code the base table is not queried directly, but through a view that restricts records returned to those for the organization corresponding to the current role of the application user, which is stored in the userenv system context (this is true up to release 11.5, but the mechanism changed in release 12.1).

See SYS_CONTEXT for information on the system context database feature, and Oracle E-Business Suite Multiple Organizations Implementation Guide (12.1) for release 12.1 multi-org implementation in Oracle ebusiness.

Partitioning Views for Testing
We propose to use views in a similar way to the multi-org views, to restrict records to those created in the testing session, by means of a ttid column on the base table that will hold the session id. The new optional column is added to those tables where this approach is required, and view are created on the tables. Our testing utility package Utils_TT sets a context variable to the value ‘TT’ to signify testing mode, and the session id is set to a package variable in the general utilities package Utils.

Any base code that inserts data into the tables has to check for test mode, and if set, put the session id into the ttid field, and if not, leave it blank. The views use the following clause:

 WHERE (ttid = SYS_Context ('userenv', 'sessionid') OR 
        Substr (Nvl (SYS_Context ('userenv', 'client_info'), 'XX'), 1, 2) != 'TT')

Both test code and base code now query the views instead of the base tables. As the base code to write to the tables has to account for the new column, it is necessary for the column to be added in all instances including production. If this seems a little drastic, consider the importance that you attach to testing, and bear in mind that the earlier, less general, approaches may suffice in many cases. In these design pattern demos I use the general solution.

Schema Structure

In the earlier articles, the base code and test packages were created in the HR schema, with utility packages kept in the custom brendan schema. However, it is more common to use separate schemas for code and data, so we will now place all packages and supporting objects in the brendan schema, and create the testing views there.

Design Pattern Use Case for Testing Views

Modern Oracle SQL is very powerful and can apply complex logic within a single statement, reducing the need for more complex procedural code. In order to show how to test SQL, we will devise a test view, HR_Test_V, having a range of features that we might want to test in general:

  • Inner joins suppress driving records where there is no joining record
  • Outer joins return driving records where there is no joining record
  • Analytic functions that partition by some key, and return aggregates on the returned record set
  • Functions based on aggregates over records that include those not in the returned record set
  • Constraints based on aggregates over records that include those not in the returned record set
  • Constraints on column values

The view functionality can be described in words as:

  • Selected values
    • Employee name, department name, and salary
    • Manager’s name
    • Ratio of employee’s salary to the department average (returned employees only)
    • Ratio of employee’s salary to the average salary of all employees
  • Constraints
    • Exclude employees in job ‘AD_ASST’
    • Exclude employees without a department
    • Do not return any records if the total salary of all employees is below 1600
  • Outer join
    • Include employees both with and without a manager

The view SQL is:

WITH all_emps AS (
        SELECT Avg (salary) avg_sal, SUM (salary) sal_tot_g
          FROM employees e
SELECT e.last_name, d.department_name, m.last_name manager, e.salary,
       Round (e.salary / Avg (e.salary) OVER (PARTITION BY e.department_id), 2) sal_rat,
       Round (e.salary / a.avg_sal, 2) sal_rat_g
  FROM all_emps a
 CROSS JOIN employees e
  JOIN departments d
    ON d.department_id = e.department_id
  LEFT JOIN employees m
    ON m.employee_id = e.manager_id
 WHERE e.job_id != 'AD_ASST'
   AND a.sal_tot_g >= 1600

Scenarios and Sub-scenarios

Scenario definition
Following our earlier article, we may define a scenario as being the set of all relevant records, both on the database and passed as parameters, to a single program call. API or view testing involves creating one or more scenarios, calling the program (or executing the process) for each scenario, and verifying that the output records are as expected.

Good testing is achieved when the scenarios are chosen to validate as wide a range of behaviours as possible. It is not always, or usually, necessary to create a new scenario for each aspect of behaviour to be tested.

Sub-scenario definition
Often, several features can be tested in the same program call by setting up different records in the scenario that will independently test the different features. For example, in our use case above we can create employees with and without a department, and with and without a manager in the same scenario to test the different types of join.

It may be helpful to think of these separate records, or fields within a record, as corresponding to sub-scenarios, and try to construct scenarios as efficiently as possible without making more calls than necessary.

View Test Output

Data setup section

SCENARIO 1: DS-1, testing inner, outer joins, analytic over dep, and global ratios with 1 dep, Employees created in setup: DS-1 - 4 emps, 1 dep (10), emp-3 has no dep, emp-4 has bad job

#  Employee id  Department id     Manager  Job id          Salary
-  -----------  -------------  ----------  ----------  ----------
1         1493             10              IT_PROG           1000
2         1494             10        1493  IT_PROG           2000
3         1495                       1493  IT_PROG           3000
4         1496             10        1493  AD_ASST           4000

SCENARIO 2: DS-2, testing same as 1 but with extra emp in another dep, Employees created in setup: DS-2 - As dataset 1 but with extra emp-5, in second dep (20)

#  Employee id  Department id     Manager  Job id          Salary
-  -----------  -------------  ----------  ----------  ----------
1         1497             10              IT_PROG           1000
2         1498             10        1497  IT_PROG           2000
3         1499                       1497  IT_PROG           3000
4         1500             10        1497  AD_ASST           4000
5         1501             20        1497  IT_PROG           5000

SCENARIO 3: DS-2, passing 'WHERE dep=10', Employees created in setup: DS-2 - As dataset 1 but with extra emp-5, in second dep (20)

#  Employee id  Department id     Manager  Job id          Salary
-  -----------  -------------  ----------  ----------  ----------
1         1502             10              IT_PROG           1000
2         1503             10        1502  IT_PROG           2000
3         1504                       1502  IT_PROG           3000
4         1505             10        1502  AD_ASST           4000
5         1506             20        1502  IT_PROG           5000

SCENARIO 4: DS-3, Salaries total 1500 (< threshold of 1600), Employees created in setup: DS-3 - As dataset 2 but with salaries * 0.1, total below reporting threshold of 1600

#  Employee id  Department id     Manager  Job id          Salary
-  -----------  -------------  ----------  ----------  ----------
1         1507             10              IT_PROG            100
2         1508             10        1507  IT_PROG            200
3         1509                       1507  IT_PROG            300
4         1510             10        1507  AD_ASST            400
5         1511             20        1507  IT_PROG            500

Notes on data setup section

  • There are three data sets, and four scenarios, each of which references a data set
  • The call to set up the data for a scenario writes out all the data created
  • A header provides a description of the features (or sub-scenarios) in the data set
  • In the output above scenarios 2 and 3 use the same data set, DS-2

Results section


  Utils_TT.Run_Suite (Utils_TT.c_tt_suite_bren);

SQL> @L_Log_Default


TRAPIT TEST: TT_View_Drivers.tt_HR_Test_View_V

Employees created in setup: DS-1 - 4 emps, 1 dep (10), emp-3 has no dep, emp-4 has bad job

#  Employee id  Department id     Manager  Job id          Salary
-  -----------  -------------  ----------  ----------  ----------
1         1518             10              IT_PROG           1000
2         1519             10        1518  IT_PROG           2000
3         1520                       1518  IT_PROG           3000
4         1521             10        1518  AD_ASST           4000

Employees created in setup: DS-2 - As dataset 1 but with extra emp-5, in second dep (20)

#  Employee id  Department id     Manager  Job id          Salary
-  -----------  -------------  ----------  ----------  ----------
1         1522             10              IT_PROG           1000
2         1523             10        1522  IT_PROG           2000
3         1524                       1522  IT_PROG           3000
4         1525             10        1522  AD_ASST           4000
5         1526             20        1522  IT_PROG           5000

Employees created in setup: DS-2 - As dataset 1 but with extra emp-5, in second dep (20)

#  Employee id  Department id     Manager  Job id          Salary
-  -----------  -------------  ----------  ----------  ----------
1         1527             10              IT_PROG           1000
2         1528             10        1527  IT_PROG           2000
3         1529                       1527  IT_PROG           3000
4         1530             10        1527  AD_ASST           4000
5         1531             20        1527  IT_PROG           5000

Employees created in setup: DS-3 - As dataset 2 but with salaries * 0.1, total below reporting threshold of 1600

#  Employee id  Department id     Manager  Job id          Salary
-  -----------  -------------  ----------  ----------  ----------
1         1532             10              IT_PROG            100
2         1533             10        1532  IT_PROG            200
3         1534                       1532  IT_PROG            300
4         1535             10        1532  AD_ASST            400
5         1536             20        1532  IT_PROG            500

SCENARIO 1: DS-1, testing inner, outer joins, analytic over dep, and global ratios with 1 dep {


        GROUP Employee {

            Employee Id  Last Name  Email  Hire Date  Job      Salary  Manager Id  department Id
            -----------  ---------  -----  ---------  -------  ------  ----------  -------------
                   1518  LN_1       EM_1   09-JUL-16  IT_PROG    1000                         10
                   1519  LN_2       EM_2   09-JUL-16  IT_PROG    2000        1518             10
                   1520  LN_3       EM_3   09-JUL-16  IT_PROG    3000        1518
                   1521  LN_4       EM_4   09-JUL-16  AD_ASST    4000        1518             10


        GROUP Where {




        GROUP Select results: Actual = 2, Expected = 2 {

            F?  Name  Department      Manager  Salary  Salary Ratio (dep)  Salary Ratio (overall)
            --  ----  --------------  -------  ------  ------------------  ----------------------
                LN_1  Administration             1000                 .67                      .4
                LN_2  Administration  LN_1       2000                1.33                      .8

        } 0 failed, of 2: SUCCESS

} 0 failed, of 2: SUCCESS

SCENARIO 2: DS-2, testing same as 1 but with extra emp in another dep {


        GROUP Employee {

            Employee Id  Last Name  Email  Hire Date  Job      Salary  Manager Id  department Id
            -----------  ---------  -----  ---------  -------  ------  ----------  -------------
                   1522  LN_1       EM_1   09-JUL-16  IT_PROG    1000                         10
                   1523  LN_2       EM_2   09-JUL-16  IT_PROG    2000        1522             10
                   1524  LN_3       EM_3   09-JUL-16  IT_PROG    3000        1522
                   1525  LN_4       EM_4   09-JUL-16  AD_ASST    4000        1522             10
                   1526  LN_5       EM_5   09-JUL-16  IT_PROG    5000        1522             20


        GROUP Where {




        GROUP Select results: Actual = 3, Expected = 3 {

            F?  Name  Department      Manager  Salary  Salary Ratio (dep)  Salary Ratio (overall)
            --  ----  --------------  -------  ------  ------------------  ----------------------
                LN_1  Administration             1000                 .67                     .33
                LN_2  Administration  LN_1       2000                1.33                     .67
                LN_5  Marketing       LN_1       5000                   1                    1.67

        } 0 failed, of 3: SUCCESS

} 0 failed, of 3: SUCCESS

SCENARIO 3: DS-2, passing 'WHERE dep=10' {


        GROUP Employee {

            Employee Id  Last Name  Email  Hire Date  Job      Salary  Manager Id  department Id
            -----------  ---------  -----  ---------  -------  ------  ----------  -------------
                   1527  LN_1       EM_1   09-JUL-16  IT_PROG    1000                         10
                   1528  LN_2       EM_2   09-JUL-16  IT_PROG    2000        1527             10
                   1529  LN_3       EM_3   09-JUL-16  IT_PROG    3000        1527
                   1530  LN_4       EM_4   09-JUL-16  AD_ASST    4000        1527             10
                   1531  LN_5       EM_5   09-JUL-16  IT_PROG    5000        1527             20


        GROUP Where {




        GROUP Select results: Actual = 2, Expected = 2 {

            F?  Name  Department      Manager  Salary  Salary Ratio (dep)  Salary Ratio (overall)
            --  ----  --------------  -------  ------  ------------------  ----------------------
                LN_1  Administration             1000                 .67                     .33
                LN_2  Administration  LN_1       2000                1.33                     .67

        } 0 failed, of 2: SUCCESS

} 0 failed, of 2: SUCCESS

SCENARIO 4: DS-3, Salaries total 1500 (< threshold of 1600) {


        GROUP Employee {

            Employee Id  Last Name  Email  Hire Date  Job      Salary  Manager Id  department Id
            -----------  ---------  -----  ---------  -------  ------  ----------  -------------
                   1532  LN_1       EM_1   09-JUL-16  IT_PROG     100                         10
                   1533  LN_2       EM_2   09-JUL-16  IT_PROG     200        1532             10
                   1534  LN_3       EM_3   09-JUL-16  IT_PROG     300        1532
                   1535  LN_4       EM_4   09-JUL-16  AD_ASST     400        1532             10
                   1536  LN_5       EM_5   09-JUL-16  IT_PROG     500        1532             20


        GROUP Where {




        GROUP Select results: Actual = 0, Expected = 0: SUCCESS

} 0 failed, of 1: SUCCESS

TIMING: Actual = 48, Expected <= 1: FAILURE

SUMMARY for TT_View_Drivers.tt_HR_Test_View_V

Scenario                                                                           # Failed  # Tests  Status
---------------------------------------------------------------------------------  --------  -------  -------
DS-1, testing inner, outer joins, analytic over dep, and global ratios with 1 dep         0        2  SUCCESS
DS-2, testing same as 1 but with extra emp in another dep                                 0        3  SUCCESS
DS-2, passing 'WHERE dep=10'                                                              0        2  SUCCESS
DS-3, Salaries total 1500 (< threshold of 1600)                                           0        1  SUCCESS
Timing                                                                                    1        1  FAILURE
---------------------------------------------------------------------------------  --------  -------  -------
Total                                                                                     1        9  FAILURE
---------------------------------------------------------------------------------  --------  -------  -------

Timer Set: TT_View_Drivers.tt_HR_Test_View_V, Constructed at 09 Jul 2016 13:32:42, written at 13:32:42
[Timer timed: Elapsed (per call): 0.01 (0.000013), CPU (per call): 0.02 (0.000020), calls: 1000, '***' denotes corrected line below]

Timer       Elapsed         CPU         Calls       Ela/Call       CPU/Call
-------  ----------  ----------  ------------  -------------  -------------
Setup          0.11        0.00             4        0.02675        0.00000
Caller         0.19        0.06             4        0.04750        0.01500
(Other)        0.03        0.03             1        0.02700        0.03000
-------  ----------  ----------  ------------  -------------  -------------
Total          0.32        0.09             9        0.03600        0.01000
-------  ----------  ----------  ------------  -------------  -------------

Notes on results section

  • In a view test there is only one group, namely the selected data set

The second part of the article is here: Design Patterns for Database Unit Testing 2: Views 2 - Code


Design Patterns for Database Reports with XML Publisher and Email Bursting

In a recent article, A Design Pattern for Oracle eBusiness Audit Trail Reports with XML Publisher, I presented a report that I developed within Oracle eBusiness 12.1, using Oracle XML Publisher. The report was for displaying audit trail data for a particular table, and I proposed that it could be used as a design pattern for audit trail reporting on any eBusiness table. The article focussed on the rather complex SQL, with associated layout structures, needed to render the audit data into a readable format.

In this article, I present a pair of XML Publisher reports intended to serve as design patterns for more general reporting. The reports were again developed within the framework of Oracle’s eBusiness embedded version of XML Publisher, of which there is also a stand-alone version,
Oracle Business Intelligence Publisher, which describes the product (rather ungrammatically) thus:

Oracle BI Publisher is the reporting solution to author, manage, and deliver all your reports and documents easier and faster than traditional reporting tools.

Reports Overview

The reports were developed specifically to serve as models for other reports, from which program constructs could be copied and pasted. For this reason I considered taking for the data sources universally available Oracle metadata such as all_tables or all_objects etc., but found problems with that idea, including:

  • They are not generally ordinary tables, and tend to produce very complex execution plans
  • The possible group structures I found were not quite general enough for my purposes

On the other hand, within Oracle eBusiness I had a number of queries available against the (FND application) metadata tables for concurrent programs that seemed to fit the purpose pretty well, and so decided to use these. As a compromise I created views on the tables with generic column names, so that the reports could be ported to other data sources relatively easily.

Concurrent Programs Data Model
In Oracle eBusiness applications concurrent (i.e. batch) programs have a logical metadata record for the program itself, and (potentially) a set of records for each of the following associated entities:

  • Parameter
  • Request group
  • XML layout template

All of these can have zero records, and for the third entity, only programs of specific type can have any records defined, a structure providing a reasonable degree of generality.

Email Bursting
Business application reports are often used to generate documents for sending to customers, suppliers etc., and increasingly companies prefer to email these out to save costs compared with mailing printed documents. In the past report developers had to code the emailing functionality themselves, usually by means of a co-ordinating program that would loop over the master records and call the reporting tool individually for each one, creating an attachment file that would then be emailed, perhaps by the Unix mailx program. As well as the development effort involved, this approach had the drawback that it scales badly as the number of emails rises owing to the individual calls to the reporting tool (such as Oracle Reports). A printed report will frequently create 10,000 records in little more time than for 1,000 records, whereas for the hand-coded emailing process it would likely take 10 times longer.

The bursting functionality in Oracle XML Publisher solves these problems by allowing the emailing processing to be specified by an XML configuration file, and by creating the files to be emailed in batch just as with printed reports. The data files are created initially in XML format from the data model, and are then merged with layout templates that are emailed by a second program.

The design pattern reports will consist of an email bursting version with a printed version, which would generally be required as a fallback for records with missing or invalid email addresses. We’ll address the obvious security issue with automated emailing programs as part of our design pattern below.

Report Outputs

Examples of complete report outputs for the printed report in .pdf format are included in the attached zip file. Here three pages are given, two showing the listing for the example report programs themselves, and a third showing a non-XML program.

Example Report – XX Example XML CP
This is page 8 of the printed report run with the only the first parameter set, the application.

  • Only the parameters that were actually set are listed after the title
  • The templates region appears because it is an XML report
  • There is only one template for this printed report, with XSL conditionality to include or exclude regions or columns


Example Report – XX Example XML CP (Email)
This is page 9 of the printed report run with the only the first parameter set, the application.

  • Notice that the bursting file column appears because this report has the file attached, using XSL conditionality
  • This email version has two layout templates that are used conditionally for each record depending on whether it’s of XML type or not
  • This record-level choice of template is implemented in the bursting XML file, and is not available in this way for the printed version


Advanced Pricing Report – QP: Java Pricing Engine Warm Up
This is page 44 of the printed report run for the Advanced Pricing application.

  • It shows a non-XML program, and the templates region consequently does not appear
  • Notice that only the summary line appears for the parameters as there are no parameters defined for the program.


XML Data Templates and XML Generators

XML Publisher reports require at least one layout template in one of several formats, and also require an executable program to generate the XML data file to merge with the template(s). Oracle supplies a Java executable to generate the data file from an XML data template containing the SQL queries, group structure etc. that the programmer attaches to the concurrent program. This is usually the best approach since it mimimizes the amount of programming required.

It is also possible to use Oracle Reports to generate XML. This can be a reasonable approach for converting legacy reports to XML Publisher to avail of some of the additional features, such as Excel output and email bursting. However, if the data model needs changing it is probably best to extract the SQL and create an XML data template from it.

It’s a really bad idea to write a PL/SQL program to generate the XML data file, being a serious case of ‘reinventing the wheel’ – use Oracle’s Java executable with XML data template instead!

Data Model: Groups, Queries, Parameters, Summaries

As described above, the example report has one master data group and three detail groups. As discussed in my last article Query Query Query, the minimum number of queries required for a group structure hierarchy is the number of groups minus the number of parent groups, which equals three here. The group structure with detail queries linked by bind variables could be depicted as below.

Group Structure Diagram
XML Publisher Model - Groups

A possible query structure for the three queries might then look like the following, where I have included summaries for two of the detail groups, and allowed for report constraints via lexical parameters, to be discussed later.

Query Structure Diagram
XML Publisher Model - Queries

Query Links and Bind Variables
It is possible to link detail queries to their master by XML query links, but according to the manual Oracle® XML Publisher Administration and Developer’s Guide, Release 12:

XML Publisher tests have shown that using bind variables is more efficient than using the link tag.

Linking a detail query to its master by bind variables involves simply referencing the master variable link columns within the detail query preceded by a colon.

Constraints and Parameters
Reports often have constraints depending on input parameters, which may be mandatory or optional. Input parameters are defined in a parameters section within the data template.

Mandatory parameters can be referenced directly as bind variables within the queries (i.e. by preceding the parameter name with a colon).

Optional parameters should normally be referenced indirectly using additional lexical parameters. This allows the exact query required to be executed rather than a composite query with Nvls to represent all possible query combinations. The latter all-purpose queries tend to be more complex and to perform poorly. Lexical parameters are declared and processed in the associated database package, and referenced by preceding them with an ampersand (&).

Note that, confusingly, these lexical parameters are not the same as the lexical tags applicable only within eBusiness that refer to eBusiness flexfields. Like the corresponding user-exits in eBusiness Oracle Reports the lexical tags allow flexfields to be included without their structure being known to the report developer. This is necessary for the standard eBusiness reports but developers of custom reports normally know the structures, and so can avoid these tags altogether (at least for new reports).

There are various ways of computing summaries in XML Publisher reports:

  • Within the SQL
  • within the XML group elements
  • within the layout template

SQL Summaries
Often the best way to do the summaries is in the SQL itself. This makes testing simpler because the queries can be run and debugged in SQL Developer or Toad, and also facilitates production support where developers can often run queries in a read-only schema, but can’t change the production code.

In the simple case of summarising detail groups that are defined against a main query, the summaries can be done using analytic functions partitioning by the master key for first level details, and as desired for any subsequent levels. In my example reports, Example Line One is summarised in this way; a subquery factor was used but this is optional here.

Groups defined against additional queries cannot be summarised quite so easily in the SQL. I have summarised the Example Line Two group by adding a subquery factor in the main query purely to do the summaries: Because the line detail is not present in the query we can’t do this by analytic functions, so a grouping summary in a separate subquery is necessary.

XML Group Summaries
Where possible SQL summaries via analytic functions appears best, but in other cases we may wish to consider the non-SQL alternatives. One of these is to define the summaries within the appropriate group as elements in the XML template setting the value attribute to the element in the lower level group to be summarised, and setting the function attribute as desired within a limited set of functions. I have not implemented this method in my examples.

XSL Layout Summaries
A further non-SQL alternative for summaries is to define them within the layout template using the XSL language, and I have implemented the summaries of Example Line Three using this method.

Database Package and Report Triggers

Each XML Publisher has a default package specified in the XML template, that handles parameter processing and implements any triggers required.

Package Spec
The spec declares both input parameters and lexical parameters, as well as the procedures that implement the report triggers (usually three).


Before Report

  • This trigger is used to process the lexical parameters, constructing the appropriate where condition depending on which optional parameters have values
  • The example packages show how ranges of character, date and number are processed. I have found it more convenient to pass dates as string parameters.

After Report

  • This trigger can be used to write column headings to the log file for fields that are logged in the group filter

Group Filter

  • This trigger can be used to filter out records depending on some condition, by returning FALSE.
  • I use it in the examples to log selected fields for each record returned.
  • This logging is particularly important for email bursting reports as it enables checking that emails to be sent externally are appropriate before sending in a second step.

Printed Example Report Details

Report Parameters
The report parameters were designed to illustrate the implementation of character, date and number ranges, with an equality join parameter that fits well with the report content, all optional:

  • Application – the eBusiness application id
  • Program name From and To parameters – character range
  • Creation date From and To parameters – date range
  • Number of parameters From and To parameters – number range

Where there is an associated email version it may be worth having an additional Yes/No parameter determining whether to include records that have an email address defined.

Layout Template
There is a single layout template, of .rtf format.

Layout Template Structure
XML Publisher Model - Layout

Layout Template File


Notes on Layout

  • Body and Margins
    • The page numbers are in the bottom margin (or footer)
    • The title, parameters and header fields are above the body tag, and repeat on each page of a given header record
  • Conditional Blocks and Columns
    • XSL if-blocks are used to conditionally print blocks such as input parameters, or detail tables depending on data values
    • XSL if-blocks with column qualifiers are used to conditionally print table columns: If there is no bursting file, the entire bursting file column will not appear
  • Sections and Page Breaks
    • The XSL for-each field for the header group uses the section qualifier, which, in conjunction with the page break field, specifies a new page after each header record except the last
    • By default, the above section qualifier would specify that page numbers reset with each header record, but this is overriden here by the initial-page-number field in the footer
  • XSL Summary
    • As discussed in the data model section, the line three summary is implemented in XSL – by the XSL count field

Email Bursting Example Report

Email Address Parameters
In addition to the printed report constraint parameters, three email address parameters have been added.

  • Override email address – setting a value here overrides the record level email address and is useful for testing
  • From and CC email addresses are parameters, which can be defaulted, for flexibility
  • The email address data source is hard-coded in this demo report: normally it would be something like a supplier email address at master record level

Two Step Bursting Process
The first step is to run the concurrent program for the report, which results in an XML file containing the report data to be created on the server. In the second step, a standard concurrent program ‘XML Publisher Report Bursting Program’ is run, passing the request id of the earlier custom report, in order to merge the XML data file with the layout template(s) and send the individual reports by email.

It’s sometimes recommended to trigger the running of the second program within the custom report itself, but it’s probably better not to do this, in order to allow validation of the reports and email addresses before sending them out.

Email Report Logging
The example report logs the email address and other fields in the concurrent program log file, including whether an override email address was specified, where the user can check them before executing the second step that sends out the emails.

Layout Template
There are two layout templates, of .rtf format, which are used conditionally depending on the type of record. The structure of the one used for XML type programs (shown below) has conditional header fields and the third lines block that are absent from the other (not shown).

Layout Template Structure
XML Publisher Model - Layout EM

Layout Template File


Notes on Layout

  • The master group is excluded from the template, although its fields are present in the header. The XML Publisher bursting program performs the looping over this group

Email Bursting XML File

<xapi:requestset xmlns:xapi="" type="bursting">
<xapi:request select="/XX_ERPXMLCP_EM/LIST_G_HDR/G_HDR">
<xapi:email server="" port="25" from="${FROM_EMAIL}" reply-to ="">
<xapi:message id="123" to="${EMAIL_ADDRESS}" cc="${CC_EMAIL}" 
attachment="true" subject="${SUB}">Dear Sir/Madam

Pleae find attached your concurrent program details. 

[Alternatively, leave blank and use output_type=html in document to have attachment go to body instead]</xapi:message>
<xapi:document output="CP_${PROG_NAME}.pdf" output-type="pdf" delivery="123">
<xapi:template type="rtf" location="XDO://CUSTOM.XX_ERPXMLCP_EM.en.US/?getSource=true" filter="//G_HDR[OUTPUT_TYPE!='XML']"></xapi:template>
<xapi:template type="rtf" location="XDO://CUSTOM.XX_ERPXMLCP_EM_XML.en.US/?getSource=true" filter="//G_HDR[OUTPUT_TYPE='XML']"></xapi:template>

Notes on Bursting File

  • The email server is specified in the bursting XML configuration file, along with the email addresses to be used, which can be variables from the selected records or hard-coded
  • The layout template is specified, and more than one can be included, as here, with filter conditions depending on selected data fields
  • Here the output is specified to be sent as a .pdf attachment; changing to output type html results in the the output appearing as body text
  • It can be useful to store some or all of the subject (or body) text on the database; the table fnd_messages is used to store the subject here, as records from the fnd tables can be installed automatically via fndload

Code to Download

The zip file Brendan_Model_XML_Reports contains a root folder and three subfolders, as follows:

  • Root – MD120 installation document for the email version. It references a generic Unix script that installs all objects, see A Generic Unix Script for Uploading Oracle eBusiness Concurrent Programs
  • Output – examples of log files for both printed and email versions and pdf outputs for printed version
  • XX_ERPXMLCP – complete code and metadata for the printed version
  • XX_ERPXMLCP_EM – complete code and metadata for the email version (except requires some printed version objects to be installed first)