Hello again. In a series of two articles, we will explain how you can use KeepTool 15 in conjunction with Oracle XML DB. As you may remember, Oracle’s out-of-the-box XML solution allows storing and retrieving of XML documents within the database. You can register XML schemas and perform queries on object-relational data structures.
The first blog entry introduced the fundamentals and ended up with a working data import interface for the HR.EMPLOYEES table.
This is the second blog article of the series. Let us follow up now with creating a data export interface. We will show you how to handle a master/detail relation such as the pair of HR.COUNTRIES and HR.LOCATIONS tables. Overall, we keep strictly focusing on the essentials. There is still plenty space to add more logic and adapt it to your own needs.
Our design approach is exporting data from both the COUNTRIES and LOCATIONS tables of Oracle’s human resources (HR) demo schema.
As you may remember, there is a master-detail relation between both tables. COUNTRIES is the master table and LOCATIONS is the detail table or entity, respectively.
Hora’s master/detail tab on the “Tables” page shows a small diagram. It displays the direct master and detail relations of the currently selected table. We flagged both COUNTRIES and LOCATIONS to highlight the tables that are subject of this article.
Again, we start with defining PL/SQL types that allow Oracle XML DB to create another XSD schema definition for the data export interface.
More specifically, our design approach needs 6 more PL/SQL types. We will create 3 types for each table, each depending on each other.
First, the types for the detail table LOCATIONS must be created. This will later allow the master type to reference them.
Please remember, the logic is very similar to the EMPLOYEES types we created in our first article of the series.
Now we follow up with another 3 types for the master table COUNTRIES:
After creating the types, you can find them on Hora’s PL/SQL Types page. Select the ‘All types’ tab to see object types and collection types together as one list:
Hora shows most recently created or changed objects using a green background color. As time goes by, the green color fades back to the regular background color.
You can use the marked button in the top left to enforce fading. After clicking 3 times, you are back at the regular background color.
As you again can see, Hora automatically displays warnings and error messages from the database server.
Explore possibilities of KeepTool.
In the first article of this series, we created the XML_LOGIC package. It implements the logic for creating the EMPLOYEES.XSD schema that was used for data import.
For the second article, we added some more code to the package. It implements the logic for another XML schema COUNTRIES.XSD.
BTW, the XML_LOGIC package name is drawn using a more saturated green background, because it just has been changed or created, respectively. The XML_INTERFACE package name has a slightly lighter background. It also has been changed recently, but some days ago. You can use the marked button in the top left to enforce fading the green background color back to the regular color. It fades completely after 3 clicks. You can use the button to accept (and fade) all recent changes and find all the next changes highlighted using the most intense color.
But let’s get back to the export interface. We updated the XML_LOGIC package to implement both the existing EMPLOYEES and new COUNTRIES/LOCATIONS logic. For your convenience, two new procedures allow us to
Therefore, we run the following anonymous block:
begin XML_LOGIC.unregister_all_xsds; XML_LOGIC.register_all_xsds; end; /
After running this code, both XSDs have been registered in the database. As a result, you can see the XSD on Hora’s XML page:
On the next tab, Hora shows the mapping of XSD elements to PL/SQL types:
Please note, the XSD has now a master-detail structure.
The XML_COUNTRIES_TRANSFER table is used for managing the data export interface. You can advise the consumer of the COUNTRIES/LOCATIONS data to read this table. The following screenshot shows the table structure:
We will implement PL/SQL logic that for each export adds one new record to the table. A trigger sets the value of primary key column ID using by fetching data from a sequence. The XML_DATA column contains an XML representation of the export data. All remaining columns provide metadata such as time stamps.
The XML_COUNTRIES_TRANS_DATA_V selects data from both COUNTRIES and LOCATIONS tables. It returns a result set that represents an XML structure containing a hierarchy of data:
The PL/SQL logic uses the view to fill the XML_DATA column of the XML_COUNTRIES_TRANSFER table.
Here https://www.oratable.com/xmltable-convert-xml-to-relational-form/ you can find more information about the technology of converting XML data into a relational view. You can see the data content of the view later after we have inserted a record into the XML_COUNTRIES_TRANSFER table.
Now that we have updated all the basics, we can proceed with implementing the export logic. Therefore, we will add more logic to the PL/SQL package XML_INTERFACE.
It contains a new procedure for XML export. The implementation of the procedure is as simple as
Now the XML export can be started by a single PL/SQL call:
declare XmlCountriesTransferID XML_COUNTRIES_TRANSFER.ID%TYPE; begin XmlCountriesTransferID := XML_INTERFACE.CreateCountriesExport; end;
The function returns the primary key value of the new record that has been inserted into the interface table.
Now after the XML record has been inserted, you can review the data in Hora:
The data consumer may fetch the exported data from the XML_COUNTRIES_TRANSFER table.
Now our XML-based data export interface is working. You can download our example and use as a template to build your own data export interface.
Based on this concept, some more complex interface implementations already have been proved in several projects. Developers reported a significant improvement of productivity by using our tools.