Calling UNIX from PLSQL package
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Calling UNIX from PLSQL package

Hybrid View

  1. #1
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598

    Calling UNIX from PLSQL package

    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
    Cheers!
    OraKid.

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    You need a wrapper, check here for details http://asktom.oracle.com/pls/asktom/...D:952229840241
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    Many thanks Paul....

    That web like was very useful.
    Cheers!
    OraKid.

  4. #4
    Join Date
    May 2002
    Posts
    2,645
    You can also grant execute on a directory object in 11g:

    Code:
    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;

  5. #5
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by stecal View Post
    You can also grant execute on a directory object in 11g:

    Code:
    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?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  6. #6
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    This is also useful.

    Many thanks
    Cheers!
    OraKid.

  7. #7
    Join Date
    May 2002
    Posts
    2,645
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width