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 !