Launching SQL*Loader from within PL/SQL procedures
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Launching SQL*Loader from within PL/SQL procedures

Hybrid View

  1. #1
    Join Date
    Nov 2002
    Location
    Ottawa, Ontario
    Posts
    1

    Question Launching SQL*Loader from within PL/SQL procedures

    We have a requirement to automate a data load process that is currently being launched manually. I have been looking into several options including using the OCI to make a call to SQL*Loader. We are using Oracle 8i (8.1.7) on a SUN box (Solaris 5.8).

    We are looking at having a process (possibly PL/SQL) check for the existance of the raw data file (a fixed length text file). If the file exists then the process would call SQL*Loader, with associated arguments, to load the file into the database. Upon success the process would then execute additional data validation and data manipulation batch processes that are written
    in PL/SQL packages.

    We are still investigating options but I was hoping someone else out there has done this. Any comments are welcome. How did you do it? What did you have to do to DB parameters and OS config to get it to work? What challenges did you have? etc.

    Thanks

    David Le Gallez
    IBM Business Consulting Services

  2. #2
    Join Date
    Dec 2000
    Posts
    138
    If there is no other option (like using ext proc) u can use the host command to invoke sql loader to populate.
    HTH
    -dharma

  3. #3
    Join Date
    Feb 2001
    Posts
    180
    The easiest way to do such things is creating a shell-script in Unix,
    that will check if a file exists and then start sql-loader.
    The script can be scheduled in the CRONTAB.
    Something like this:
    #!/bin/sh
    function LoadFile
    {
    # Start SQL loader

    sqlldr userid=user/passwd \
    control=$CNTL_FILE \
    data=$DATA_DIR/$1 \
    log=$LOG_DIR/$1.log \
    bad=$BAD_DIR/$1.bad \
    discard=$DSC_DIR/$1.dis \
    rows=100000 >> $vLogFile
    trc=$?


    return 0;
    }
    ########### Here it starts ###########
    filetypes=`cat $FILE_DIR/cmafiles.lis`

    for currtype in $filetypes
    do
    LoadFile $currtype
    rm $FILE_DIR/$currtype
    done

    Success
    Regards
    Ben de Boer

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