PL/SQL operating system and filesystem integration
execute shell commands, access files and get directory listings with OS_COMMAND, FILE_PKG and FILE_TYPE

Author: Carsten Czarski Follow @cczarski

Links

Features at a glance

  • Execute a shell or OS command with access
    to STDIN, STDOUT and the return code
  • Get a directory listing as a virtual table
    (table function)
  • Load images or other documents from
    a filesystem folder as BLOB into a table
  • java.io.File functionality in PL/SQL

Usage examples


Java in the database: other examples

 This is Release 1.0!

There are many cases in which interaction of the Oracle database with the operating system or file system is needed. The database provides some PL/SQL packages for this out-of-the-box (UTL_FILE for File access, DBMS_SCHEDULER for executing a shell command), but there are still tasks which cannot be achieved with those packages. The here provided packages close this gap.

Usage example of OS_COMMAND within Oracle SQL*Plus
Usage example of OS_COMMAND within Oracle SQL*Plus

NEW FEATURES IN RELEASE 1.0:

  • Support for Oracle12c; i.e. new method FILE_TYPE.GET_FREE_SPACE
  • BFILE and DIRECTORY Object integration in FILE_PKG and FILE_TYPE
  • New sample scripts

RELEASE 1.0RC1:

  • New function FILE_PKG.SET_FS_ENCODING - useful for filenames with special characters (umlauts)
  • EXEC procedures in OS_COMMAND can handle STDOUT and STDERR separately
  • Internal optimizations: Improved Performance for folder listings by factor 5 
Bug reports or enhancement requests are welcome!

Technical background

This package utilizes the JVM which is embedded in the database since Oracle8i. Every JVM provides operating system interaction out-of-the-box:
  • File and folder operations: java.io.File
  • Executing shell commands: java.lang.Runtime.exec()
This package encapsulates the functionality and provides the integration with the SQL layer of the Oracle database. An example for this integration is the java.io.File.list() method which provides the contents of a folder als java.io.File array. FILE_PKG converts this Array into an Oracle collection which can be queried using the TABLE() operator: select * from table(file_pkg.get_file_list(file_pkg.get_file('/')))

Java has an own security mechanism: The database user which calls the functions and procedured needs appropriate privileges in order to access files or execute shell commands. These privileges can be granted fine-grained using the DBMS_JAVA.GRANT_PERMISSION procedure. The documentation pages for FILE_TYPE and OS_COMMAND contain some examples. The "traditional" PL/SQL directory objects are not used by the database JVM.
The advantage of the JVM's Java2-based security is a much more fine-grained permission handling than PL/SQL: Permissions might be granted on single files, whole directory trees and furthermore negative permissions (DBMS_JAVA.RESTRICT_PERMISSION) are possible: A user might be granted privileges for a file system folder but not for a special file in it. More information about the security mechanism is contained in the Oracle documentation.

last updated: April 24th, 2014


View My Stats