Design Patterns for Database API Testing 1: Web Service Saving 2 – Code

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 first example, I present a design pattern for web service ‘save’ procedures by means of a conceptual discussion, together with a working example of base code and test code for a procedure to save new employees in Oracle’s well-known HR demonstration schema.

In part 1, Design Patterns for Database API Testing 1: Web Service Saving 1 – Design I began with an abstract discussion, and progressed to an example based on Oracle’s HR demo schema. That article presented the test scenarios, logical data structures, and the example testing output. Here, I start by listing a number of extremely prevalent antipattern approaches to database API testing, and how to avoid them.

Following this, I list the code for the base procedure and the test package with notes against each section. There are three utility packages used, and I list the code for one procedure from those, that does all of the assertion and reporting from a single call. After the code listings, I provide some design material on the code packages used.

Design Patterns for Database API Testing 2: Views 1 – Design presents another design pattern following the same ideas.

API Testing Antipatterns

Automated unit testing of database APIs is often considered difficult and time-consuming. Unfortunately I believe it is made much worse by widespread following of antipattern approaches, some of which appear on popular database testing-related sites as examples to follow.

Here are a few antipatterns that I have identified, and which my code attached below avoids.

Test this then test this then…

Antipattern description

This occurs when the unit test code is written as a long sequence of method calls, each followed by assertions, and each usually having hard-coded data values in both method calls and assertions. It is a classic antipattern because it is widespread and developers often follow it thinking they are doing the right thing.

Antipattern consequence

The testing code becomes very long-winded and hard to follow, and tends to result in less rigorous testing.

Pattern alternative

Store all input data in arrays at the start, loop over the input scenarios array, accumulating the outputs in arrays, and loop over the output arrays for the assertions.

Method-based testing

Antipattern description

This occurs when the test suite is based on testing all the methods in a package, rather than units of behaviour, such as a procedure serving a web service.

Antipattern consequence

Ian Cooper explains very well in the video link below the adverse consequences of this antipattern from a Java perspective, but it applies equally to database testing.

  • It results in a great deal more testing code, with a lot of redundancy, which deters people from the whole concept of test-driven development
  • Re-factoring is more difficult because the unit test code tests the implementation
  • Shifting the focus of testing from the behavioural side is also unlikely to improve testing quality

TDD: Where Did It All Go Wrong?

Pattern alternative

Include in your test suite only tests of well-defined units of behaviour such as a web service entry point procedure, ignoring helper methods.

Field-level assertion

Antipattern description

This occurs when the individual fields written to the database or in output arrays have their own assertions.

Antipattern consequence

Real database applications often have tables with large numbers of fields, and the numbers of assertions can consequently become very large.

Pattern alternative

Assert at the record level by concatenating the fields in a record into one string.

Coupled tests

Antipattern description

This occurs when testing one scenario affects another; for example, when all the test data are created at once and not rolled back after each scenario and re-created as needed for the next.

This antipattern is strongly promoted by some popular testing frameworks, where package level setup and teardown are mandatory, at least by default. My own framework deliberately does not support these, preferring the test program to call its own private procedures as necessary at the appropriate level.

Antipattern consequence

The coding becomes more complex as it is necessary to distentangle what results from earlier test scenarios from that of the current scenario.

Pattern alternative

Set up test data at the scenario level, not at the procedure level, and roll it back at the end of the scenario testing.

Opaque output

Antipattern description

This occurs when the output does not show what was tested.

Antipattern consequence

It is harder to review the testing, especially when combined, as it usually is, with the Test this then test this then… antipattern. This results in lower quality.

Pattern alternative

Make the output self-documenting with clear scenario descriptions and listings of entities and fields that are being tested.

Emp_WS.AIP_Save_Emps – Base Procedure

PROCEDURE AIP_Save_Emps (p_emp_in_lis           ty_emp_in_arr,     -- list of employees to insert
                         x_emp_out_lis      OUT ty_emp_out_arr) IS -- list of employee results
  l_emp_out_lis        ty_emp_out_arr;
  bulk_errors          EXCEPTION;
  PRAGMA               EXCEPTION_INIT (bulk_errors, -24381);
  n_err PLS_INTEGER := 0;


  FORALL i IN 1..p_emp_in_lis.COUNT
    INSERT INTO employees (
    ) VALUES (
    RETURNING ty_emp_out_obj (employee_id, To_Char(To_Date(employee_id,'J'),'JSP')) BULK COLLECT INTO x_emp_out_lis;

  WHEN bulk_errors THEN

    l_emp_out_lis := x_emp_out_lis;

      IF i > x_emp_out_lis.COUNT THEN
      END IF;
      x_emp_out_lis (SQL%Bulk_Exceptions (i).Error_Index) := ty_emp_out_obj (0, SQLERRM (- (SQL%Bulk_Exceptions (i).Error_Code)));

    FOR i IN 1..p_emp_in_lis.COUNT LOOP
      IF i > x_emp_out_lis.COUNT THEN
      END IF;
      IF x_emp_out_lis(i).employee_id = 0 THEN
        n_err := n_err + 1;
        x_emp_out_lis(i) := l_emp_out_lis(i - n_err);
      END IF;

END AIP_Save_Emps;

Notes on base procedure

  • There is a FORALL statement to insert a record into employees for each record in the input list in one batch
  • The RETURNING clause uses a BULK COLLECT to return the new id and its value in words to the output list
  • SAVE EXCEPTIONS means valid records will be inserted, while invalid ones will fall to the EXCEPTION block
  • Invalid records cause a zero and the error message to be interpolated into the output array
  • I tried to follow a similar principle here to that in Connor McDonald’s article, Assume the Best; Plan for the Worst – Oracle Magazine Article

TT_Emp_WS.tt_AIP_Save_Emps – Testing Package Procedure

Declaration section


  c_proc_name CONSTANT  VARCHAR2(61) := 'TT_Emp_WS.tt_AIP_Save_Emps';

  c_ln_prefix             CONSTANT VARCHAR2(20) := 'LN ';
  c_em_prefix             CONSTANT VARCHAR2(20) := 'EM ';

  c_ln                    CONSTANT L1_chr_arr := L1_chr_arr (
        c_ln_prefix || '1', c_ln_prefix || '2', c_ln_prefix || '3', c_ln_prefix || '4', c_ln_prefix || '5', c_ln_prefix || '6');
  c_em                    CONSTANT L1_chr_arr := L1_chr_arr (
        c_em_prefix || '1', c_em_prefix || '2', c_em_prefix || '3', c_em_prefix || '4', c_em_prefix || '5', c_em_prefix || '6');
  c_job_id                CONSTANT VARCHAR2(20) := 'IT_PROG';
  c_job_id_invalid        CONSTANT VARCHAR2(20) := 'NON_JOB';

  c_salary                CONSTANT L1_chr_arr := L1_chr_arr ('1000', '1500', '2000x', '3000', '4000', '5000');

  c_params_3lis           CONSTANT L3_chr_arr := L3_chr_arr (
          L2_chr_arr (L1_chr_arr (c_ln (1),    c_em (1),        c_job_id,         c_salary (1))), -- valid
          L2_chr_arr (L1_chr_arr (c_ln (2),    c_em (2),        c_job_id_invalid, c_salary (2))), -- invalid
          L2_chr_arr (L1_chr_arr (c_ln (3),    c_em (3),        c_job_id,         c_salary (3))), -- invalid salary, nan
          L2_chr_arr (L1_chr_arr (c_ln (4),    c_em (4),        c_job_id,         c_salary (4)),  -- valid
                      L1_chr_arr (c_ln (5),    c_em (5),        c_job_id_invalid, c_salary (5)),  -- invalid job id
                      L1_chr_arr (c_ln (6),    c_em (6),        c_job_id,         c_salary (6)))  -- valid
  g_exp_3lis                   L3_chr_arr;

  c_ws_ms_limit          CONSTANT PLS_INTEGER := 2;
  c_scenario_lis         CONSTANT L1_chr_arr := L1_chr_arr (
                               '1 valid record',
                               '1 invalid job id',
                               '1 invalid number',
                               '2 valid records, 1 invalid job id (2 deliberate errors)'
  c_inp_group_lis       CONSTANT L1_chr_arr := L1_chr_arr ('Employee');
  c_inp_field_2lis      CONSTANT L2_chr_arr := L2_chr_arr (
                                                        L1_chr_arr (
  c_out_group_lis       CONSTANT L1_chr_arr := L1_chr_arr ('Employee', 'Output array', 'Exception');
  c_out_field_2lis      CONSTANT L2_chr_arr :=  L2_chr_arr (
                                    L1_chr_arr ('*Employee id', 'Name', 'Email', 'Job', '*Salary'),
                                    L1_chr_arr ('*Employee id', 'Description'),
                                    L1_chr_arr ('Error message')
  l_timer_set           PLS_INTEGER;
  l_inp_3lis            L3_chr_arr := L3_chr_arr();

  l_act_3lis         L3_chr_arr := L3_chr_arr();

Notes on declaration section

  • Observe that the declarations of l_inp_3lis and g_exp_3lis as 3-level generic lists map directly to Scenario Inputs and Scenario Outputs in the array structure diagram in Design Patterns for Database API Testing 1: Web Service Saving 1 – Design
  • c_scenario_lis, as a generic list maps directly to Scenario Descriptions
  • Adding or subtracting fields in the input object means simply adding or subtracting fields in the inner level of the list
  • Adding test scenarios (i.e. calls) amounts to adding outer level records in each list
  • Using the program as a template for other unit test programs is therefore very easy
  • All inputs and outputs are printed in the output, making the testing self-documenting


  PROCEDURE Setup_Array IS
    l_last_seq_val         PLS_INTEGER;

    SELECT employees_seq.NEXTVAL
      INTO l_last_seq_val
      FROM DUAL;

    g_exp_3lis := L3_chr_arr ( -- each call results in a list of 2 output lists: first is the table records; second is the out array
                        L2_chr_arr (L1_chr_arr (Utils.List_Delim (To_Char(l_last_seq_val+1), c_ln (1), c_em (1), c_job_id, c_salary (1))), -- valid char, num pair
                                    L1_chr_arr (Utils.List_Delim (To_Char(l_last_seq_val+1), To_Char(To_Date(l_last_seq_val+1,'J'),'JSP'))),
                        L2_chr_arr (Utils_TT.c_empty_list,
                                    L1_chr_arr (Utils.List_Delim (0, 'ORA-02291: integrity constraint (.) violated - parent key not found')),
                        L2_chr_arr (Utils_TT.c_empty_list,
                                    L1_chr_arr ('ORA-06502: PL/SQL: numeric or value error: character to number conversion error')
                        L2_chr_arr (L1_chr_arr (Utils.List_Delim (To_Char(l_last_seq_val+3), c_ln (4), c_em (4), c_job_id, c_salary (1)), -- c_salary (1) should be c_salary (4)
                                                Utils.List_Delim (To_Char(l_last_seq_val+5), c_ln (6), c_em (6), c_job_id, c_salary (6)),
                                                Utils.List_Delim (To_Char(l_last_seq_val+5), c_ln (6), c_em (6), c_job_id, c_salary (6))), -- duplicate record to generate error
                                    L1_chr_arr (Utils.List_Delim (To_Char(l_last_seq_val+3), To_Char(To_Date(l_last_seq_val+3,'J'),'JSP')),
                                                Utils.List_Delim (0, 'ORA-02291: integrity constraint (.) violated - parent key not found'),
                                                Utils.List_Delim (To_Char(l_last_seq_val+5), To_Char(To_Date(l_last_seq_val+5,'J'),'JSP'))),

    l_act_3lis.EXTEND (c_params_3lis.COUNT);
    l_inp_3lis.EXTEND (c_params_3lis.COUNT);

    FOR i IN 1..c_params_3lis.COUNT LOOP

      l_inp_3lis(i) := L2_chr_arr();
      l_inp_3lis(i)(1) := L1_chr_arr();
      FOR j IN 1..c_params_3lis(i).COUNT LOOP

        l_inp_3lis(i)(1)(j) := Utils.List_Delim (c_params_3lis(i)(j)(1), c_params_3lis(i)(j)(2), c_params_3lis(i)(j)(3), c_params_3lis(i)(j)(4));

      END LOOP;


  END Setup_Array;

Notes on Setup

  • Setup gets a value from the sequence in order to determine what values are expected in the outputs
  • Any concurrent calls could invalidate this expectation, but scheduling the test runs may avoid this; if not, one could change the expectation to a category, of say positive integer in a certain range
  • The example has two output groups, corresponding to a table and an output array
  • Observe how easy it would be to extend to extra tables or arrays, just by adding records in the middle level of the list
  • c_empty_list is a 1-record list containing a string ‘EMPTY’, which is used to simplify assertion of empty lists
  • Setup in this case has no need to create test data, as we have chosen to reference pre-existing departments
  • There is no teardown as the inserts from the procedure call are rolled back after each call
  • The call to Write_Times writes out the report from the package level timer set, including setup timings (if any)

‘Pure’ API wrapper procedure

  PROCEDURE Purely_Wrap_API (p_inp_2lis        L2_chr_arr,       -- input list of lists (record, field)
                             x_act_2lis    OUT L2_chr_arr) IS    -- output list of lists (group, record)

    l_emp_out_lis       emp_out_arr;
    l_tab_lis           L1_chr_arr;
    l_arr_lis           L1_chr_arr;
    l_err_lis           L1_chr_arr;

    -- Do_Save makes the ws call and returns o/p array
    PROCEDURE Do_Save (x_emp_out_lis OUT emp_out_arr) IS
      l_emp_in_lis        emp_in_arr := emp_in_arr();

      FOR i IN 1..p_inp_2lis.COUNT LOOP
        l_emp_in_lis (l_emp_in_lis.COUNT) := emp_in_rec (p_inp_2lis(i)(1), p_inp_2lis(i)(2), p_inp_2lis(i)(3), p_inp_2lis(i)(4));
      END LOOP;

      Timer_Set.Init_Time (p_timer_set_ind => l_timer_set);
      Emp_WS.AIP_Save_Emps (
                p_emp_in_lis        => l_emp_in_lis,
                x_emp_out_lis       => x_emp_out_lis);
      Timer_Set.Increment_Time (p_timer_set_ind => l_timer_set, p_timer_name => Utils_TT.c_call_timer);

    END Do_Save;

    -- Get_Tab_Lis: gets the database records inserted into a generic list of strings
    PROCEDURE Get_Tab_Lis (x_tab_lis OUT L1_chr_arr) IS

      SELECT Utils.List_Delim (employee_id, last_name, email, job_id, salary)
        BULK COLLECT INTO x_tab_lis
        FROM employees
       WHERE ttid = Utils.c_session_id_if_TT
       ORDER BY employee_id;
      Timer_Set.Increment_Time (p_timer_set_ind => l_timer_set, p_timer_name => 'SELECT');

    END Get_Tab_Lis;

    -- Get_Arr_Lis converts the ws output array into a generic list of strings
    PROCEDURE Get_Arr_Lis (p_emp_out_lis emp_out_arr, x_arr_lis OUT L1_chr_arr) IS

      IF p_emp_out_lis IS NOT NULL THEN

        x_arr_lis := L1_chr_arr();
        x_arr_lis.EXTEND (p_emp_out_lis.COUNT);
        FOR i IN 1..p_emp_out_lis.COUNT LOOP

          x_arr_lis (i) := Utils.List_Delim (p_emp_out_lis(i).employee_id, p_emp_out_lis(i).description);

        END LOOP;

      END IF;

    END Get_Arr_Lis;



      Do_Save (x_emp_out_lis => l_emp_out_lis);
      Get_Tab_Lis (x_tab_lis => l_tab_lis);
      Get_Arr_Lis (p_emp_out_lis => l_emp_out_lis, x_arr_lis => l_arr_lis);

        l_err_lis := L1_chr_arr (SQLERRM);

    x_act_2lis := L2_chr_arr (Utils_TT.List_or_Empty (l_tab_lis), Utils_TT.List_or_Empty (l_arr_lis), Utils_TT.List_or_Empty (l_err_lis));

  END Purely_Wrap_API;

Notes on main testing procedure

  • The call converts the generic input data into the objects required by the base procedure, then calls the procedure and sets the resulting records for each output group
  • Purely_Wrap_API has a very simple main section that calls local procedures to split the logic into three sections
  • Its EXCEPTION clause captures the scenario where an invalid type conversion is attempted
  • Its output is a generic 2-level list that is mapped to a record in the outer-level output list
  • Empty lists are converted to c_empty_list, the 1-record list mentioned earlier
  • The procedure ends with a rollback

Main section

-- Every testing main section should be similar to this, with array setup, then loop over scenarios
-- making a 'pure'(-ish) call to specific, local Purely_Wrap_API, with single assertion call outside
-- the loop
  l_timer_set := Utils_TT.Init (c_proc_name);
  Timer_Set.Increment_Time (l_timer_set, 'Setup_Array');

  FOR i IN 1..c_params_3lis.COUNT LOOP

    Purely_Wrap_API (c_params_3lis(i), l_act_3lis(i));


  Utils_TT.Is_Deeply (c_proc_name, c_scenario_lis, l_inp_3lis, l_act_3lis, g_exp_3lis, l_timer_set, c_ws_ms_limit,
                      c_inp_group_lis, c_inp_field_2lis, c_out_group_lis, c_out_field_2lis);

END tt_AIP_Save_Emps;

Notes on main section

  • The main section is very simple: a local procedure Purely_Wrap_API is called for each input scenario
  • The call converts the generic input data into the objects required by the base procedure, then calls the procedure and sets the resulting records for each output group
  • Is_Deeply is a generic utility procedure that does all the assertions and calls the results reporting utility
  • This can be used without change for any number of output groups with any numbers of fields, of any types

Utils_TT – API Test Utilities Package
We will include only one procedure from this package in the body of the article. See gitHub link for the full code.
Is_Deeply – to check results from testing

PROCEDURE Is_Deeply (p_proc_name                 VARCHAR2,      -- calling procedure
                     p_test_lis                  L1_chr_arr,    -- test descriptions
                     p_inp_3lis                  L3_chr_arr,    -- actual result strings
                     p_act_3lis                  L3_chr_arr,    -- actual result strings
                     p_exp_3lis                  L3_chr_arr,    -- expected result strings
                     p_timer_set                 PLS_INTEGER,   -- timer set index
                     p_ms_limit                  PLS_INTEGER,   -- call time limit in ms
                     p_inp_group_lis             L1_chr_arr,    -- input group names
                     p_inp_fields_2lis           L2_chr_arr,    -- input fields descriptions
                     p_out_group_lis             L1_chr_arr,    -- output group names
                     p_fields_2lis               L2_chr_arr) IS -- test fields descriptions

  l_num_fails_sce                L1_num_arr :=  L1_num_arr();
  l_num_tests_sce                L1_num_arr :=  L1_num_arr();
  l_tot_fails                    PLS_INTEGER := 0;
  l_tot_tests                    PLS_INTEGER := 0;

(private procedures - see gitHub project,, for full code listings)

  Detail_Section (l_num_fails_sce, l_num_tests_sce);
  Summary_Section (l_num_fails_sce, l_num_tests_sce, l_tot_fails, l_tot_tests);
  Set_Global_Summary (l_tot_fails, l_tot_tests + 1);

END Is_Deeply;

Notes on Is_Deeply

  • This is the base version of Is_Deeply with 3-level arrays of expected and actuals
  • An inner loop asserts actual values (which are records) against expected
  • The final assertion is against average call time
  • It is expected that all assertion within a test procedure will be via a single call to one of the versions of this procedure, making a big reduction in code compared with traditional unit testing approaches
  • After final assertion a call is made to write out all the results, by scenario, with all inputs printed first, followed by actuals (and expected, where they differ); this means that the test outputs now become precise and accurate documents of what the program does

Package Usages Diagram

Call Structure Table – TT_Emp_WS.tt_AIP_Save_Emps
Installation Instructions

See TRAPIT – TRansactional API Testing in Oracle for a link to the installation code for both the framework and the demo code on gitHub, and links to related articles.

If you are interested in following a similar approach to testing, you might find this exercise an easy way to get going: Revise the base procedure to return error records as a second output array, with the first output returning only valid records. You do not need to define any new types for this. Update the  unit test code for the new signature.


A Layered Approach To Processing XML Web Services

As explained in an earlier post, Data Modelling XML SOAP Documents, I have an approach to calling web services that involves the use of a generic layer that lies between the client applications and low-level APIs for HTTP calls and XML processing. The earlier post introduces the subject, and deals with the data modelling aspects. This post gives high-level, largely diagrammatic, design information for my PL/SQL implementation of the approach. I expect to post on examples of use and results at a later date.

Layer Diagram

External Call Structure

External Call Structure Diagram

External Call Procedures

Procedure Description

Client Side

Client Program Any client program that needs to access web services
Client Converter A program specific to the client to convert between the generic data models of the package and the formats of the client. If there is only one client program then the converter need not be a separate program.

WS Package

Set Header Adds header level nodes into the XML Tree array
Format Attribute Formats an attribute string from name, value and name-space prefix
Add Element (Request) Adds an element node into the XML Tree array
Add Record (Request) Adds a record, consisting of a record header element and child element nodes, into the XML Tree array
Add Element (Response) Adds an element node into the Structure Tree array
Add Record (Response) Adds a record, consisting of a record header element and child element nodes, into the Structure Tree array
Process Web Service Converts the XML Tree array into the SOAP request message, calls the web service and transforms the SOAP response message into the output Data Tree array
Write Output Writes the output Data Tree array

Web Service Call

Web Service Call Structure Diagram

Web Service Call Structure Procedures

Procedure Description

Custom Procedures

Call Web Service Coordinating procedure for the web service call. Note that both request and response writing and reading calls are within loops as the messages can be more than the HTTP maximum chunk size of 32767 bytes
Expand Element (Request) Recursive procedure to create the XML SOAP request from the XML Tree array and other inputs
Delim Field Formats an XML element within its tags
Expand Element (Response) Recursive procedure to convert the initial form of Group Structure Tree List by Parent into the nested form, Group Structure Tree List, used by later processing

Oracle Built-in Packages

UTL_HTTP Oracle HTTP package used to make the HTTP request and read the response
DBMS_LOB Oracle ‘large object’ package used for processing CLOB variables for the full request and response, passed in 32767-byte chunks in the HTTP calls
DBMS_XMLDOM Oracle XML package used to create an XML document and node from the response
XMLTYPE Oracle XML package used to create a variable of XML type for passing to the above package

Populate Tree Call

Populate Tree Call Structure Diagram

Populate Tree Procedures

Procedure Description

Custom Procedures

Populate Tree Main procedure for populating Data Tree List array. First an attempt is made to populate the output tree specified by the input Group Structure Tree List array; if this returns an error, then a second call is made to populate the output tree specified by the standard error group structure; sometimes this too can fail, if the HHTP response is not the expected SOAP message, and this will also be trapped and returned as an error message variable
Check Fault Resets the input Group Structure Tree List array to match the standard SOAP error structure and calls the next procedure to populate the corresponding output tree
Populate Specific Tree Populates the output tree specified by the current Group Structure Tree List array: this may be either that specified by the client application, or the standard error structure
Populate Tree Record Recursive procedure to build the output Data Tree List array using Oracle’s XML APIs

Oracle Built-in Packages

DBMS_XMLDOM ‘The DBMS_XMLDOM package is used to access XMLType objects, and implements the Document Object Model (DOM), an application programming interface for HTML and XML documents’ – Oracle® Database PL/SQL Packages and Types Reference, v11.2
DBMS_XMLProcessor ‘The DBMS_XSLPROCESSOR package provides an interface to manage the contents and structure of XML documents’ – Oracle® Database PL/SQL Packages and Types Reference, v11.2

Data Modelling of XML SOAP Documents

I have been involved in a number of projects where web services are used for interfacing, and have generally found a high level of complexity in their use compared with traditional interfacing methods. One of the areas of complexity lies in converting between the XML messages and the fields and arrays used in conventional programming languages such as Oracle PL/SQL. Often this is handled in an unmodular way with request messages being manually built, and the response message being parsed by individual xpath searches for specific strings.

My approach is to handle these conversions in a generic layer that lies between the client applications and the low-level APIs provided by the programming language for HTTP calls and XML processing. This post deals with a data model and the data structures used in the PL/SQL package that I wrote for calling web services from PL/SQL. I have posted on the program itself here: A Layered Approach To Processing XML Web Services and expect to post on examples of use at a later date.

Web Services
XML web services have become a standard mechanism for interfacing data between applications across the internet. The advantage that they have is that a standard transfer mechanism (HTTP, Hypertext Transfer Protocol) is used, and the data are formatted according to a standard specification, regardless of the technologies in which the applications are implemented. The data formats are described in a Web Services Description Language (WSDL) file, and interfacing is effected using SOAP (Simple Object Access Protocol) messages, as specified by the World Wide Web Consortium (W3C).

Web services form the cornerstone of Service Oriented Architecture (SOA), which Oracle uses in its Application Integration Architecture (AIA). A good acronym dictionary is vital for working in these areas 🙂

Interfacing by web services consists of sending input data as a text string in XML format by an HTTP request, and receiving an HTTP response, also as a text string in XML format.

My Web Service Interface Program
The layer package is intended to handle any data structures that can be represented in XML. On the request side, the client application will call layer APIs to add records and elements to build the request structure without having to write any XML directly, and the layer will construct the XML SOAP message and call the web service. The response side relies on a generic data structure comprising two hierarchical arrays: a structure array that specifies the group structure of the response XML message (or a subset of it), and a data array that holds the data with pointers to the structure array. The structure array forms an input, and is used by the layer to call standard XML APIs (Oracle XML APIs in my implementation) to retrieve the data from the response. The data modelling and conceptual framework are not language-specific, while my implementation is in Oracle PL/SQL.

SOAP Data Model
Both input and output of a web service call include an XML SOAP message, which is a text string consisting of the data in the form of an XML hierarchy. The elements in the hierarchy contain a mandatory name field, plus optional namespace and value, optional list of child elements, and an optional list of attributes. The hierarchy must contain certain standard elements and attributes, within a structure shown in this skeleton SOAP message (modified from an example here SOAP Tutorial/SOAP Syntax, by ):

In addition to the standard elements and attributes, there may be application specific elements, attributes and values as indicated by the ellipses.The hierarchy of elements can be represented as below, where the group entity on the left represents the fact that a number of elements at a given level may be implicitly grouped, although without this grouping being explicit in the SOAP document. We use this grouping in the data structures on the response side but not on the request side:

Each element in an XML document has a name, an optional value, optional attribute name/value pairs, with the names being optionally name space-qualified, and the element may contain child elements. Please refer to widely available documentation on the SOAP protocol for further information; here’s a link I found useful: A Busy Developer’s Guide to SOAP 1.1

Request Side Data Structures

The diagram shows the main data structures that we use for building the request. Boxes with double borders represent arrays, and a solid arrow represents a pointer from a field to an array element.

The XML Tree List structure is derived from the general model above by treating the list of attributes as a string to be included in the element entity, and corresponds to the array specified by data type xml_tree_list_type in the following table. This array is built from procedure calls by the client application, and is processed internally by a recursive procedure to construct the SOAP request message. Note that as well as the XML tree types, we have defined two additional list structures for convenience of parameter passing:

  • Name Space List for passing name spaces
  • XML Field List as it is often convenient to treat a group of fields as a record, consisting of a parent element and a list of child fields

Name Space List Data Types

Type Name Element Category Description
name_space_type Object Name space
ns_label Character Name space label
ns_address Character Name space address
name_space_list_type Array List of name spaces
(unnamed element) name_space_type* Name space

Field List Data Types

Type Name Element Category Description
field_type Object XML field
field_name Character Field name
field_value Character Field value (optional)
field_list_type Array List of XML fields
(unnamed element) field_type* XML field

XML Tree List Data Types

Type Name Element Category Description
xml_tree_type Object Record in the XML tree holding the data for an XML element
parent_id Integer Index to parent record in the XML tree
ns_label Character Name space label (optional)
field_name Character Field name
field_value Character Field value (optional)
attr_string Character Attribute string, including name and value (optional)
xml_tree_list_type Array XML tree
(unnamed element) xml_tree_type* Record in the XML tree

Response Side Data Structures

The diagram shows the main data structures that we use for processing the response. The broken arrow between the two arrays signifies that each nested child list in the data tree corresponds to a child group record in the structure tree.

The Group Structure Tree List array defines the group structure of the output data structure. To simplify input, this is first set up in an unnested structure, where a field points to its parent, by procedure calls from the client application. The program then converts this into the nested structure shown below it, where the children are included in the parent record, which is more suitable for the later processing. Note that the hierarchy may be, and usually is, a subset of the actual SOAP response, since typically layers are present in a SOAP response message that are not useful for the client application. Also note that in the event of a standard error response being returned from the web service, the group structure is replaced by that for the error response by the program.

  • Each of the arrays (at the top level) has a root element without a parent, and these records have null values other than for their respective child lists
  • The model can represent any number of hierarchy levels

Group Structure Data Types

Type Name Element Category Description
int_list_type Array List of integers
(unnamed element) Integer Integer
structure_tree_type Object Structure tree record
group_name Character The group tag, used as a search string
ns_prefix Character Name space prefix
attr_string Character An attribute string that has to be included in searches where the group name is ambiguous (Oracle JDeveloper uses array as a group tag for arrays, with an attribute to differentiate)
child_list int_list_type*
structure_tree_list_type Array Structure tree list
(unnamed element) structure_tree_type* Structure tree record

Data Structure Data Types

Type Name Element Category Description
child_group_list_type Array List of indexes in the data tree list of child groups of current data tree record
(unnamed element) int_list_type* List of indexes in the data tree list of child records of current group
data_tree_type Object Data tree record
field_list field_list_type* Field list (specified in input side)
child_group_list child_group_list_type* List of indexes in the data tree list of child groups of current data tree record
data_tree_list_type Array Data tree list
(unnamed element) data_tree_type* Data tree record

Generic Data Models
In database design it is well known that overly generic data models lead to poor performance and extra application complexity. The web service interfacing model is of course highly generic, and it should be noted that the same problems may indeed offset the acknowledged standardisation advantages. The data model and structures described here necessarily reflect the genericity of the underlying architecture, while the approach taken is intended to reduce application complexity by moving much of it into a callable module.