Calling UNIX from PLSQL package

[balajiyes]

Hi,

I have a requirement where I need to call UNIX commands from PLSQL procedure. UNIX commands like, gzip, cat, rm -f, ls, etc will be used.

Hope this is possible in oracle 11G R2. If yes, please provide some sample.

I will be using oracle 11G R2.

Please help.

Thanks in advance

[PAVB]

You need a wrapper, check here for details http://asktom.oracle.com/pls/asktom/...D:952229840241

[balajiyes]

Many thanks Paul....

That web like was very useful.

[stecal]

You can also grant execute on a directory object in 11g:

CREATE TABLE sales_transactions_ext
(PROD_ID NUMBER, CUST_ID NUMBER,
 TIME_ID DATE, CHANNEL_ID NUMBER,
 PROMO_ID NUMBER, QUANTITY_SOLD NUMBER,
 AMOUNT_SOLD NUMBER(10,2), UNIT_COST NUMBER(10,2),
 UNIT_PRICE NUMBER(10,2))
ORGANIZATION external (TYPE oracle_loader
  DEFAULT DIRECTORY data_file_dir ACCESS PARAMETERS
  (RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
--running an OS executable in this directory--
    PREPROCESSOR exec_file_dir:'gunzip' OPTIONS '-C'
    BADFILE log_file_dir:'sh_sales.bad_xt'
    LOGFILE log_file_dir:'sh_sales.log_xt'
    FIELDS TERMINATED BY "|" LDRTRIM
    ( PROD_ID, CUST_ID,
      TIME_ID         DATE(10) "YYYY-MM-DD", 
      CHANNEL_ID, PROMO_ID, QUANTITY_SOLD, AMOUNT_SOLD,
      UNIT_COST, UNIT_PRICE))
  location ('sh_sales.dat.gz') 
)REJECT LIMIT UNLIMITED;

[PAVB]

You can also grant execute on a directory object in 11g:

CREATE TABLE sales_transactions_ext
(PROD_ID NUMBER, CUST_ID NUMBER,
 TIME_ID DATE, CHANNEL_ID NUMBER,
 PROMO_ID NUMBER, QUANTITY_SOLD NUMBER,
 AMOUNT_SOLD NUMBER(10,2), UNIT_COST NUMBER(10,2),
 UNIT_PRICE NUMBER(10,2))
ORGANIZATION external (TYPE oracle_loader
  DEFAULT DIRECTORY data_file_dir ACCESS PARAMETERS
  (RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
--running an OS executable in this directory--
    PREPROCESSOR exec_file_dir:'gunzip' OPTIONS '-C'
    BADFILE log_file_dir:'sh_sales.bad_xt'
    LOGFILE log_file_dir:'sh_sales.log_xt'
    FIELDS TERMINATED BY "|" LDRTRIM
    ( PROD_ID, CUST_ID,
      TIME_ID         DATE(10) "YYYY-MM-DD", 
      CHANNEL_ID, PROMO_ID, QUANTITY_SOLD, AMOUNT_SOLD,
      UNIT_COST, UNIT_PRICE))
  location ('sh_sales.dat.gz') 
)REJECT LIMIT UNLIMITED;

Does this post belongs to a different thread?

[balajiyes]

This is also useful.

Many thanks

[stecal]

No, the code shows an example of using the 11g preprocessor feature, where you can invoke an OS command such as one of the ones asked about in the original post.

External Tables

A feature has been added to external tables that allows users to preprocess input data before it is sent to the access driver. The ability to manipulate input data with a preprocessor program results in additional loadable data formats, which greatly enhances the flexibility and processing power of external tables.

The types of preprocessor programs that can be used are versatile, ranging from system commands, user-generated binaries (for example, a C program), or user-supplied shell scripts. Because the user supplies the program to preprocess the data, it can be tailored to meet the user’s specific needs. This means that the number of loadable formats is restricted only by the ability to manipulate the original data set.

http://www.oracle.com/technetwork/da...ex-093639.html