no more unix scripts in oracle 11.2 !!


This is very bold statement and really does not truly reflect small trick I would like to show you.

Oracle 11.2 has very interesting new feature -EXTERNAL TABLES PREPROCESSOR. It basically allows to execute any unix script and use the output of the script as a source of external table (classic example is reading data from compressed archive and loading them directly to oracle without any transient files). Please see my other article what’s new in Oracle 11g R2 – external tables preprocessor for more details.

Thanks to this preprocessor you can load pretty much anything even output from any unix script – yes you can read for example df -k on a fly without creating any transient files in unix.

1. Create oracle directories which correspond to existing directories on unix.

 CREATE DIRECTORY dir_sample_data as '/home/jiri/Desktop/sample/data';
 CREATE DIRECTORY dir_sample_log as '/home/jiri/Desktop/sample/log';
 CREATE DIRECTORY dir_sample_bad as '/home/jiri/Desktop/sample/bad';
 CREATE DIRECTORY dir_sample_exec as '/home/jiri/Desktop/sample/execute';
 GRANT READ        on DIRECTORY dir_sample_data to JIRI;
 GRANT READ, WRITE on DIRECTORY dir_sample_log  to JIRI;
 GRANT READ, WRITE on DIRECTORY dir_sample_bad  to JIRI;
 GRANT EXECUTE     on DIRECTORY dir_sample_exec to JIRI;

2. place dummy.dat file to /home/jiri/Desktop/sample/data directory – this is dummy empty file

3. place unix script below to /home/jiri/Desktop/sample/execute/run_my_script.sh

#!/bin/bash
 /bin/df -k

4. create external table with preprocessor

CREATE TABLE JIRI.SAMPLE_01_EXT
 (
 SCRIPT_OUTPUT   VARCHAR2(2000)
 )
 ORGANIZATION EXTERNAL
 ( TYPE ORACLE_LOADER
 DEFAULT DIRECTORY dir_sample_data
 ACCESS PARAMETERS
 ( RECORDS DELIMITED BY NEWLINE
 PREPROCESSOR dir_sample_exec:'run_my_script.sh'
 SKIP 1
 LOGFILE dir_sample_log:'dummy.log'
 BADFILE dir_sample_bad:'dummy.bad'
 NODISCARDFILE
 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
 MISSING FIELD VALUES ARE NULL
 )
 LOCATION (dir_sample_data:'dummy.dat')
 )
 REJECT LIMIT 0
 NOPARALLEL
 NOMONITORING;

as you can see external tables preprocessor is very simple yet very effective way for loading not only files but pretty much any information from unix. I can see where  SELECT * FROM JIRI.SAMPLE_01_EXT can be much easier to use than executing unix scripts directly. Please note that there is no transient file or script created during this process, 1000 users can run this at the same time with no issues of overlapping files or some home made unique file naming (well not sure if you want to run this 1000x but you certainly can) and no files (except log you can suppress) will be created !

Advertisements

One thought on “no more unix scripts in oracle 11.2 !!

  1. Anonymous says:

    Well worded.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: