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.
What is new in this release:
- Separated files for Oracle10 and Oracle11g - the OS_COMMAND.LOAD_ENV methods are not available in 10g
- Support for working directories when executing shell commands
- Support for custom environment variables
- FILE_TYPE no longer required write privileges when just reading files
- PL/SQL package now works with invokers' rights
- Get environment specific information: Root directories and name separator character
- FILE_TYPE: get_parent() method
Comments not found