Red-Database-Security GmbH is specialized in Oracle Security

Products
Repscan 2.5
Hedgehog Enterprise
Checkpwd (free)

Services
Oracle Audit / Hardening
Security Training
Consulting

Information
Oracle Security Blog
Published Alerts
Upcoming Alerts
Patch Information
Whitepaper
Presentations
Oracle Fact Sheets
Exploits
Tutorials
Videos
Scripts

News & Events
Events
News

Company
Contact
People
Partner
Impressum
Sitemap

Run OS commands via create table

Documented way to run OS commands. First seen here

Requirements

CREATE TABLE
Read-Privilege on directory object
Oracle 11.1.0.7 / 10.2.0.5 or higher

For security reasons the preprocessor option does not allow the usage of |, < , >, &, and $ characters due to security reasons.

Code:

SQL> create or replace directory exec_dir as 'C:\WINDOWS\system32';

SQL> create or replace directory load_dir as 'C:\TOOLS';

SQL> create or replace directory log_dir as 'C:\TOOLS';

SQL> CREATE TABLE ADDRESS( "NAME" VARCHAR2(60))
ORGANIZATION EXTERNAL(
  TYPE oracle_loader  DEFAULT DIRECTORY load_dir
  ACCESS PARAMETERS  (
     RECORDS DELIMITED BY NEWLINE
     PREPROCESSOR exec_dir:'gunzip' OPTIONS ' -d'
     BADFILE log_dir: 'address.bad'
     LOGFILE load_dir: 'address.log'
     FIELDS TERMINATED BY '|'
     MISSING FIELD VALUES ARE NULL  (
        "NAME"     )  )
  LOCATION ('address.txt.gz'))
REJECT LIMIT UNLIMITED;

SQL> select count(*) from ADDRESS;