10 things you didn't know you could do with the Oracle database

In the world of enterprise software development we can see a steady tendency in moving business logic from the database tier to the application tier. Many factors influenced this, but the main one, in my opinion, is portability. Modern applications must be able to work correctly on different operating systems, desktop and mobile devices, support interfaces with dozens of upstream and downstream systems, and, of course connect to different databases. For portability Java is, de facto, the number one programming language. But databases have been here for ages, they are being transformed and adopted for enterprise architecture and enhanced with lots of useful features.

In this article I'd like to remind the reader of some nice features of the Oracle database which may be useful in real life applications to solve particular practical tasks.

XML in database. Often in order to build xml reports of a complex structure from relational tables developers prefer Java and xPath. In fact XML appeared in the Oracle database since version 8i and in 11g it's fully supported as database's integral part and compliant with W3C standards. You can create and store XMLType data in tables, convert it from text to DOM and vice versa using SQL syntax as well as xPath, generate an XML of a complex nested structure from regular tables and parse XML documents to relational format. An XSD schema can be stored in the database and used for validation. It's possible to create an XML DB repository inside the database and transfer XML files via widespread protocols like FTP, HTTP or WebDAV.

PL/SQL Web applications. With Oracle PL/SQL gateway it's easy to invoke a database stored procedure from a web browser and pass parameters via URL. The PL/SQL gateway is based on a mod_plsql plugin which translates the URL into a call to a stored program with parameters and returns the result back, typically an HTML page. With XML DB embedded http listener it's possible to generate a web page from a PL/SQL procedure in 5 minutes with minimal configuration as described here.

Communicating via HTTP(s). With the Oracle database solely using the PL/SQL language it's quite simple to send and receive data by HTTP protocol. The Oracle supplied package UTL_HTTP allows you to create powerful data demanding interfaces between the database and, for example, external web services. The first step would be to call a BEGIN_REQUEST function establishing a HTTP session. Various configuration attributes can be set including a header, authentication, cookies support etc. WRITE_TEXT procedure allows you to push text into HTTP request body, and READ_TEXT reads data from the HTTP response. Examples can be found in Steven Feuerstein's famous book: “Oracle PL/SQL programming”.

Securefiles and Database file system. It's well known that Oracle database can be used to store large binary and text data in LOB columns. But in 11g a new architecture for LOBs called Securefiles was introduced. The approach to store and operate with chunks of LOB data was totally re-engineered, so that large objects in LOB columns are internally manipulated very much similar to files in file systems. Based on Securefile architecture it's now possible to mount a file system on a Linux platform which would store its files in the Securefile LOB columns of the database. All file operations in that mount point, i.e. make directory, create, copy or remove files, etc. are converted into PL/SQL API calls and manipulate data in related LOB fields. This feature is quite useful if there is a need, for example, to access application log files stored on a file system with the means of SQL or PL/SQL from the database.

External tables. But what if you have some text files on a database server which you need to read but have no access to the server? It could be trace files, export/import log files, configuration or parameter files, etc. In this case external tables might resolve the issue. You just create a DIRECTORY object in the database pointing to an OS folder and create an external table which maps one or more fields to the required OS file. Now you can read the file with regular SELECT * FROM your_external_table statement. Using ALTER TABLE command you can change the file name and directory at any time should you need to read some other file. Even more, starting from 11g it's possible to run a preprocessor directive and execute some shell script, for example unzip a file from an archive. You can find some examples here.

Java stored procedures. PL/SQL is perfect for implementing database related logic, but sometimes it's not enough. If you need additional processing speed or require some specific Java features you can create Java classes directly in the database. With CREATE OR REPLACE AND COMPILE JAVA SOURCE command you can compile source text into a valid java class stored in the database dictionary. Then you create a PL/SQL wrapper procedure, map parameters if required and call this procedure as any other regular PL/SQL program. JVM embedded into the database will interpret java code. Your DBA does not allow you to create database links? Not a problem. You can create a Java class in the target database which would open a connection to the source database, select from tables of interest and return required rowset.

Multitable insert. Assume some upstream system populates the staging table in a database, and your interface must read that table and insert data into multiple destinations. Would you select source table as many times as the inserts you need to perform? Multitable insert is available in SQL and extremely useful in warehousing solutions to optimize such tasks. Read source row once and insert into as many destinations as needed – that how it works in short.

ROLLUP, CUBE and GROUPING SETS. Everyone heard of these SQL features but might be wondering why would one need them it in real life. Simple example. Assume you have a schema with tables some of which are partitioned. You are curious about the size of each table and how much space the whole schema occupies. This would return the answer for both questions in one SQL.

Analytical functions. Probably LIST_AGG is the most awaited analytical function introduced in 11G SQL. It allows you to concatenate string values into one row. But there are many other nice features. Assume you need to get the maximal salary among the employees who make the lowest commission.

SQL Profile. Are you familiar with situations when a query which was running perfectly in production for ages apparently changes its execution plan and performs poorly? Plan stability is the area where Oracle regularly introduces new features and solutions. SQL Plan Baselines appeared in 11g to substitute stored outlines. But have a look at SQL Profiles. With a call to undocumented function dbms_sqltune.import_sql_profile it's possible to apply hints to a query without changing it's text. So if you notice an unexpected plan switch, you can extract the previous “good” plan from the shared pool and pin it for your query via SQL Profile.

Want to learn how to work with Oracle databases? Check out our trainings.

Alexander Pavlovskiy
Consultant in Oracle Database