Oracle external tables and preprocessor commands

This could be an interesting concept whereby I use preprocessor commands with external to generate the contents for the external table.

For example I want to know how much space is available on the database filesystem. A nice way of approaching this problem is within the database using external tables and preprocessor commands.

Typically preprocessor commands would be used as part of some batch running procedure, essentially translating data into the database. However preprocessor commands can also be used to run operating system commands (as the owner of the database software).

A good example of where the preprocessor commands can be useful is running a batch script as the preprocessor command which loads the output into the external table. This output is then made available to the database.

Technical aspects

The first thing to note is the shell script is dumb. This means if there are any commands which need to be used then a shell parameter should be used. For example.

PWD/bin/mkdir

Now run the script, correct any errors and ensure the text file is generated as expected.

Create directory

Create a database directory which points to the directories where the shell script and output directories are located. Currently I am setting this location to the same directory.

Update permissions on the directory

Now update permissions for any user who is going to use this external table and shell script.

grant execute, read, write on directory xxx to nn.
Where xxx is the directory name created above
Where nn is the database user you want to target the grants — that is the user who will run this procedure.

external table script

The external table script is always a little more complex than standard create table commands. Essentially the statement will look something like:

CREATE TABLE xxx (cols … )
organisation external (type oracle_loader default directory yyy
access parameters (… PREPROCESSOR ‘zzz.bat’ … )
location (‘zzz.bat’);

The major difference to a normal external table command is the PREPROCESSOR command which is where the batch script is and then the location is pointing to the script which is being generated.

This command works well when you are wanting to gather information about the operating system. The obvious command which comes to mind relates to capacity type questions. When I want to work out how much space is available on the database filesystem I can run a preprocessor external table command which will generate the information as it stands at this point in time.

Use this command in conjunction with sending emails for storage alerts.