Logo Background

Invoking SQL Loader HostCommand Using PL/SQL

  • By on January 26, 2010 | 1 Comment

    Is there any way to invoke or execute Unix command from Oracle PL/SQL? Hostcommand will be the answer where it is another alternative way for Unix call and we don’t need to write any shell script to achieve the execution.

    Hostcommand is a function or utility in Oracle database which was used to pass in command to Unix operating system for execution.

    We mentioned that we can call the hostcommand in either SQL or PL/SQL in our previous post of HostCommand Unix Call From Oracle SQL.

    The example below shows on how we call the sqlldr binary from Unix database machine using hostcommand feature in PL/SQL.

    The reason of why we are calling the sqlldr from Unix machine was the version of 10.x can be used to load the XML data file into database.

    CREATE OR REPLACE PROCEDURE APPS.ORACLE_XML_SQLLDR
    (errbuf  OUT varchar2, retcode OUT NUMBER) IS
    l_err_msg varchar2(80);
    l_err_code NUMBER;
     
    i NUMBER;
     
     
    BEGIN
     
    fnd_file.put_line(fnd_file.log,'Launch sqlldr from Unix database machine');
    fnd_file.put_line(fnd_file.log,'sqlldr_runtime = '||
    hostcommand('/oracle/product/bin/sqlldr apps/<password> 
    control=<.ctl location> log=<.log location>')); 
    fnd_file.put_line(fnd_file.log,'Finish sqlldr');
     
     
    END;
    /
    Previous
    Next
    » ORA-29283 Invalid File Operation sys.utl_file
  1. #1 sidharth naik
    March 28, 2011 6:20 am

    @echo off&SETLOCAL

    SET Month=%date:~4,2%
    SET Day=%date:~7,2%
    SET Year=%date:~-4%

    sqlldr userid=%load_user%/%load_pass%@%load_db% SKIP_INDEX_MAINTENANCE=TRUE Direct=True Parallel=True control=’%intapps_dir%\importSyndData_Mon.ctl’ log=’%inbound_dir%\OUT_SFA_MON_ALGN_RX_%Year%%Month%%Day%.log’ bad=’%inbound_dir%\OUT_SFA_MON_ALGN_RX_%Year%%Month%%Day%.bad’ data=’%inbound_dir%\OUT_SFA_MON_ALGN_RX_%Year%%Month%%Day%.txt’ errors=5000 SILENT=(DISCARDS)

    This is a sql loader command. This .cmd file will run a SQL*Loader job to load the monthly data directly into the SIEBEL.S_SYND_DATA table. The logfile will be placed in E:\oracle\product\10.2.0\utlfiledir\SFAINBOUND\OUT_SFA_MON_ALGN_RX_YYYYMMDD.log

    The question is can I convert this command in PL\SQL and if possible please help me how to do it or guide me with necessary instruction.

    Post ReplyPost Reply
Leave a Comment