Oracle PL/SQL executing OS Commands0.6

Public Domain    
PL/SQL executing shell (operating system) commands: OS_COMMAND and FILE_TYPE





Oracle PL/SQL executing OS Commands project contains the PL/SQL packages OS_COMMAND, FILE_TYPE and FILE_PKG which enable an Oracle database developer to interact with the operating system or with ordinary files. Oracle provides out-of-the-box only limited functionality for accessing the file system and no functionality to execute shell commands or operating system processes. There are PL/SQL scripts spread over the internet and in the Oracle forums but there is no ready-to-install package. This project is about to provide exactly this.

In general there are at least three ways to execute shell commands by the Oracle engine.

 1. Coding a native shared library, reference it in the Oracle database using CREATE LIBRARY and create a PL/SQL wrapper on the particular functions. This required a C compiler for the platform Oracle is running on.
 2. Using the Oracle-supplied package DBMS_SCHEDULER which is able to execute shell commands. But DBMS_SCHEDULER lacks access to STDIN and STDOUT; if this is needed, DBMS_SCHEDULER is not longer an option.
 3. Since Oracle8i there is a J2SE compliant Java Engine contained in the database; as any other Java Engine it is capable to execute the shell commands via the System.exec method. The JDBC API allows to pass the contents of STDIN, STDOUT or the OS return values back to the SQL engine. And since Java is platform-independent one install script does the job for all platforms. So I took the decision for Java in the database.

The package runs in Oracle 10.1 or higher.
Last updated on November 11th, 2008

0 User reviews so far.