Bringing Data Pieces Together with Oracle Database 12c
By @"Yuli Vasiliev"
Introduction
Every so often, the information you need is obtainable from different sources, in many formats. What makes Oracle Database outstanding is the ability to handle all those information sources, bringing disparate data pieces together. With native support for many data formats, Oracle Database provides a “common-denominator” interface to XML, HTML, JSON, CSV text files, and relational data, of course. The data in the above formats can be obtained from the database, operating system, other database systems, Web, or represent script-generated content. Once retrieved, non-relational data can be decomposed into relational format so that you can easily join it with relational data – all within a single query. On the other hand, it is equally possible to generate non-relational content from relational data.
This article illustrates some examples of how you can take advantage of the Oracle Database capabilities and features allowing you to access and pull together different type data from disparate sources. In particular, it covers native Oracle Database support for JSON and XML, providing several examples of how you can obtain and transform that kind of data, querying different data sources. You will see the SQL/XML functions and SQL/JSON functions in actions, taking XQuery expressions and JSON Path expressions as arguments, respectively. Also, you will see an example of using the httpuritype function alone, illustrating the case when you need to query an HTTP-callable source that returns neither XML nor JSON, but just plain text data. Finally, the article explains how you can employ external tables to deliver non-relational external data into the database, including script-generated data.
JSON and XML Support in Oracle Database
XML support in Oracle Database is implemented through Oracle XML DB – a set of Oracle Database XML technologies integrated with the relational database server, providing high-performance XML storage, retrieval, and processing. Oracle XML DB has been supported since Oracle 9i Database Release 2, coming with a number of new features in each further release. As of JSON support, it is a new Oracle XML DB feature added in Oracle Database 12c Release 1. From this same release onward, Oracle XML DB is a mandatory component of the database.
Oracle Database allows you to store JSON and XML content in relational tables, as well as in Oracle XML DB Repository as files – like in a regular file system. When it comes to accessing JSON or XML data, Oracle Database can service requests from standard protocol clients (HTTP, HTTPs, FTP) and SQL clients. In particular, you can use standard protocol clients to move entire JSON and XML documents in and out of an Oracle database through Oracle XML DB Repository. SQL, however, offers maximum choice with regard to where the content is housed, allowing you to access and manipulate JSON and XML data stored both in relational tables and in Oracle XML DB Repository. Moreover, SQL allows you to query HTTP-callable sources, which reside outside of the database.
The figure below gives a graphical depiction of how you can access and manipulate XML and JSON content with Oracle Database:
Figure 1: A simplified diagram of how you can access JSON and XML data stored in the database and outside of it.
Now that you have a cursory understanding of how JSON and XML support works in Oracle Database, it’s time to look at some sample code to see it in action. In the following three sections, you will see examples of how standard protocol clients and SQL clients of an Oracle database system can access, generate, and move XML and JSON content.
XQuery to Access and Manipulate XML and HTML Content
Of all the facilities provided by Oracle XML DB, perhaps the most widely used is Oracle XQuery, which allows you to issue XQuery queries right from within SQL statements. In a nutshell, it works as follows: You pass an XQuery expression to a SQL/XML function, such as XMLQuery or XMLTable, querying either XML-expressible data or relational data wherever it is found: in the database, on the Web, or generated on the fly. The retrieved results can be joined with the other data in the same SQL statement. The following example illustrates such a join in action.
Suppose you have a vacation.xml document available through http, which contains the list of employees entitled to vacation next month. The document might look like this:
For simplicity, you can use your localhost web server to make the above document available through http.
In the following query, you access both the departments database table from the HR demonstration schema and the vacation.xml document stored outside of the database, joining the retrieved results together: Before proceeding, make sure you have demonstration schema HR installed and unlocked in your Oracle database:
The above is a good example of how you can generate an XML document with the XMLQuery SQL/XML function, deriving and combining data from different sources. In the compound expression passed to XMLQuery as argument, you define the desired structure for the document being created, incorporating XQuery expressions targeting different data sources.
If the above query fails with the error: 'network access denied by access control list (ACL)', then you need to grant the connect privilege for localhost to the HR user. You can do that being connected as SYS, with the following PL/SQL code, assuming your localhost web server listens on port 9999:
After the above code has completed successfully, you can connect as HR again and reissue the query, which should generate an XML document looking like this:
This example illustrates how you can generate an XML document on the fly, gathering XML data and relational data within a single SQL statement. Another common use case for XQuery is when you need the results in relational format. For such cases, you have SQL/XML function XMLTable, which also takes an XQuery expression as argument but then shreds the results of the expression evaluation into relational data, according to the mapping specified in the COLUMNS clause. You’ll see an example of XMLTable in action at the end of the next section.
Standard Protocols To Access and Move Data In and Out of the Database
Oracle XML DB Repository is a key feature of Oracle XML DB technology, allowing for moving entire documents as files in and out of the database, as if it were a conventional file system. Each file stored in the repository has a URL assigned to it, which makes it available for standard protocols such as FTP and HTTP(S). For security reasons, though, the Oracle XML DB protocol server is disabled by default. So, for example, to check out the status of the Oracle XML DB FTP server, you can issue the following query:
SELECT dbms_xdb_config.getftpport FROM DUAL;
If you get the following result:
GETFTPPORT
----------
0
That means the Oracle XML DB FTP server is disabled. To enable it, you must set up the port on which it will listen.
EXEC dbms\_xdb\_config.setftpport(2100);
Now that you have the FTP server enabled, you can use it for uploading files to Oracle XML DB Repository. Before you can do that, however, you must give the privileges to the user, allowing uploading files to a certain folder in Oracle XML DB Repository. The first step is to create an ACL (Access Control List) granting all privileges to the user you’re going to use. This can be done with the following PL/SQL code, which you can run as the XDB or SYS user:
Then, you can create a repository folder and assign it to the above ACL:
Now, you can connect to the Oracle XML DB FTP server as the HR user and upload files to repository folder /home/hr. Suppose you want to copy the vacation.xml file used in the preceding section example to the /home/hr repository folder. To do this, first launch a system terminal and change directory to the one in which you have file vacation.xml. Then, in the system terminal, launch a FTP client and issue the following commands (highlighted in bold) from the FTP command prompt (output truncated to save space):
As you can see, copying files from the operating system to the database looks the same as it would when copying files within the operating system.
Now that you have vacation.xml in the repository, you can access it with XQuery function fn:doc, which takes the URI of a repository file as argument. So, the query discussed in the preceding section might be rewritten now as follows:
It is important to emphasize that the contents of Oracle XML DB Repository – being exposed through two public views: RESOURCE_VIEW and PATH_VIEW – can be accessed with SQL directly, as shown below:
As a result, you should see the following output (truncated to save space):
Working with JSON Data
In recent years, the more lightweight JSON – as a data exchange format – has become a popular alternative to XML, with the number of JSON Web services growing daily. To meet this reality, Oracle added JSON support to the database, allowing you to access JSON content from within SQL statements.
There is much similarity in how SQL access to XML and SQL access to JSON work in Oracle Database. In both cases, it is based on Oracle SQL functions. For a quick example, let’s make a SQL call to the Google Web Search API that allows you to do programmatic web searches over HTTP, via a URL. It is interesting to note that you don’t even need to obtain an API key to use the Google Web Search API, specifying just a search string as parameter. In the following simple example, you perform a Web search for ‘Oracle Database’, projecting the retrieved JSON data relationally:
The output might look like this:
In the above query, you use the JSON_TABLE SQL function to evaluate the query to the Google Web Search API and then shred the results into relational data, picking up only those pieces of the retrieved data that you are interested in.
Using the HTTPURITYPE Function Alone
The example in the preceding section illustrated how you can selectively shred the retrieved JSON document into relational data, specifying only necessary document keys in the COLUMNS clause of the JSON_TABLE SQL function. This implies that the structure of the document should be known precisely in advance. In this particular example, you might check out Developer's Guide of Google Web Search API to examine that structure. However, the quickest way to learn what the structure of a document looks like, yet without leaving your SQL tool, is to use the HTTPURITYPE function alone (not with JSON_TABLE or XMLTable), as in the following example:
The above query should return the entire JSON document generated by the Google Web Search API for search ‘Oracle Database’.
Another example of when you might need to use HTTPURITYPE alone is accessing a HTTP callable source that returns plain text but not markup of any kind. For example, some Web APIs may return a short text message or a number as plain text.
Of course, you can still wrap the retrieved data in XML tags on the fly, so that it can be processed within a SQL/XML function, as in the following example:
Using External Tables
External tables provide another window to data located outside of the database, allowing you to access that data as if it were in a database table. This feature evolves with each Oracle Database release, and starting with Oracle Database 11g, you can even load an external table with a shell script’s output. In my How to Launch Linux Utilities from Oracle Database OTN article, I gave an example of how you can have an operating system utility's output be inserted into an external table, using the preprocessor directive feature.
The example in this section also uses a shell script as an external table preprocessor program, but this time the script calls a Python script. Diagrammatically, it might look like this:
Figure 2: Using a shell script as an external table preprocessor program, calling a scripting language program.
First, let’s start with the Python script to be called from the shell script. Turning back to the example in the Working with JSON Data section earlier in this article, you can write the following Python code to work with the Google Web Search API:
The content of the shell script might look like this:
As you can see, the search string is passed as a parameter to the Python script.
Next, connect to the database as sysdba and specify a database alias for the directory where the above shell script is located:
CREATE OR REPLACE DIRECTORY my_dir AS '/home/oracle/ext_tbl_dir';
Then, you need to grant the necessary privileges on the files in the my_dir directory to the user schema in which you are going to create the external table:
GRANT READ,WRITE,EXECUTE ON DIRECTORY my_dir TO usr;
Now you can connect as the usr user and create the table:
Once created, the above table can be queried like any other table in the database:
Conclusion
Oracle Database is great for accessing, manipulating, and grouping data of different types together, giving you the ability to handle even the external data sources with minimal effort. The examples in this article illustrate in action some key Oracle Database capabilities related to accessing and pulling together different type data.
See Also
About the Author
Yuli Vasiliev is a software developer, freelance author, and consultant currently specializing in open source development, Java technologies, business intelligence (BI), databases, service-oriented architecture (SOA), and more recently virtualization. He is the author of a series of books on the Oracle technology, including Oracle Business Intelligence: An introduction to Business Analysis and Reporting (Packt) and PHP Oracle Web Development: Data processing, Security, Caching, XML, Web Services, and Ajax (Packt).