Chapter 10. Working with the Operating System

When working with unstructured data, there is a major requirement to work with the operating system. The Oracle Database cannot handle a variety of multimedia objects, and handling unstructured data will likely require external processing.

There are two key methods for achieving this. The first involves shelling out of the database and invoking an operating system executable. The second involves calling an Oracle cartridge, which is linked to an external program, which then does the processing.

The cartridge is more complicated to develop and is operating-system specific. It can be built in C or in Windows using the .Net Framework. The .Net Framework uses a special purpose cartridge supplied with the database that can dynamically call a .Net program. This requires the use of Oracle Data Access Components (ODAC)⁽¹⁾.

The goal of this chapter is to describe the methods for shelling out of the database and techniques for working with the Unix and Windows...

Why shell out?

There are a lot of different types of unstructured data in the market place. On top of this and as has been discussed in previous chapters, there is a huge variety of multimedia types. Oracle does not support all of these, so when it comes to processing them, which might involve transforming, extracting, or converting, the solution is to build a program that runs in the database in PL/SQL or Java. This program when run will invoke an external process to perform digital object processing and retrieve the results back into the database.

The processing could be done before the data is loaded into the database. Based on business requirements though, it might be more efficient or a business necessity to perform this after the data has been loaded into the database.

The following are examples of uses of shelling out of the database:

• A DNG (Photo of type Adobe Digital Negative) has been loaded into the database, and a thumbnail needs to be extracted from the digital image. This involves...

Unload and load digital objects

When it comes to shelling out of the database and processing a digital object, it becomes apparent that the digital object needs to be unloaded and saved into the filesystem for processing. This is an additional process and can take time if the digital object is quite large. By using Oracle Securefiles, this step is much faster to perform, but it still is an additional step. Though there are many disadvantages to storing a digital object outside the database, the one advantage in doing it can be seen when it comes to external processing. By storing, it externally needs to unload and reload the processed digital object that is removed. This simplifies the process.

As has been covered in previous chapters, one of the exciting features in the latest 11gR2 release is the Oracle Database File System. This removes the need to unload the digital object when it's stored in the database. This is because the filesystem is integrated with the database. In this case, all...

How to shell out

There are a number of methods available within Oracle to shell out of the database and call an operating system script.

Java

Java running inside the database has an access to a command, which will shell out and call an operating system script. This requires Java to be installed in the database (not available with Oracle XE). The Java program sets up environment variables and eventually invokes a routine, which performs the shell process.

final Process p = doexec(command, osexecEnv, osworkDir);

The schema running the Java program might need additional privileges to run commands (see dbms_java.grant_permission for more information).

For more information on this, including the methods outlined, refer to http://www.oracle.com/technetwork/database/enterprise-edition/calling-shell-commands-from-plsql-1-1-129519.pdf.

Scheduler

The dbms_scheduler package supersedes the often used dbms_jobs package. It enables batch jobs written in PL/SQL and Java to be configured and run as well as the...

Challenges when shelling out

Shelling out and running an operating script can be harder than it looks. There are numerous considerations that have to be made, and there can be issues with security, the environment, and monitoring the job. In most cases, the shelling out process is one of just submitting blindly, and hoping it works.

Synchronous or asynchronous?

The first decision is to determine whether the script should:

• Run, and the database should wait for it to finish (synchronous)

• Submit it, immediately return, and then forget about it (asynchronous)

• Submit it, monitor its progress, and if need be, terminate the process

Synchronous is the default behavior. A script might be run to convert an audio WAV file into an MP3 file. This might take 5 minutes to complete. In this case, the calling program running in the database and doing the shell command will just wait for it to finish. If the script fails to finish or hangs, then the calling program will also hang. Steps need to be put into the...

Windows

When it comes to operating systems, the two well-known giants are Unix and Windows. One could argue that the mobile device operating systems are bigger in terms of volume, but until they can support a running database on them, they will not be included. The Mainframe gurus will also raise the point that operating systems, such as MVS, have been around for a longer time, are better in capabilities, more reliable, more mature, and still used by large businesses to support mission critical systems. For those who would consider themselves geeks, this is likely falling into the timeless argument about which is better Star Trek or Star Wars, and then adding to the mix Flash Gordon. As fun as it is to debate these points, the arguments are not beneficial, as most businesses buy operating systems based on a myriad of issues, technical prowess being just one of many factors.

A well-skilled database administrator in today's computing environment needs to be proficient in both, Windows and Unix...

Unix

The Unix operating system was first developed in 1969. The trademark is currently owned by The Open Group, an industry standards consortium. The operating system is currently well-know for running on servers even though it can run equally well on desktops and mobile devices. Its popularity on desktops grew when Apple rewrote and ported its PowerPC operating system to a Unix variant called Max OS X. On servers, Unix remains a very popular operating system and most hardware vendors support one or more Unix or Unix-like variations on it.

How Unix differs from Windows

A lot of database administrators are either Unix administrators or Windows ones and not both. Finding an administrator skilled in both is quite rare. The reason is that to be an efficient administrator requires becoming well-versed and skilled in the underlying operating system. It's common to hear a Unix administrator make the comment that Oracle on Windows is small scale or use the colloquial term "mickey mouse". With recent...

Summary

For small-sized to medium-sized businesses, the differences between the different Unix versions can be difficult to understand. Between Oracle Solaris, IBM AIX, and HP-UX, the differences are seen, as the servers are scaled to support tens to thousands of terabytes of data, with thousands of concurrent users. Each platform offers its own architectures, which the analogy for most is like trying to choose between different mobile phone carriers. Each is very different, no two plans can be easily compared, but each claims to offer the best capabilities, scale the most while offering the best price for doing so.

When comparing Unix to Windows, the questions raised have to cover the skill set at the business, the budget, the number of users, and the commercial requirements. Windows is best known as an environment that is designed for small-sized to medium-sized businesses. Unix has a reputation for scaling to very large database sizes and concurrent users. With the latest versions of Windows...

Exercises

These questions are designed to have the reader go beyond the traditional method of answering questions. They involve using the concepts designed in the chapter and doing additional research on the Internet to come up with the best solution to address the questions raised:

• Design an algorithm for killing a rogue process that has occurred during a shell out from the database.

• You are an enterprise architect and have been asked to recommend an operating system and platform for storing and delivering 10 million digital images over the Internet. Justify the operating system and platform to be used. What is the key business consideration that controls the decision?

• Which is the fastest and most efficient method for shelling out of the database, dbms_scheduler, Java, or using utl_http with PHP?

• What other methods are there available for invoking an operating system script besides the ones mentioned in this chapter?

• Given a novice developer, which Unix shell would you give them to start work...