DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: How to Invoke SQL Plus in SQL Loader..

  1. #1
    Join Date
    Feb 2001
    Posts
    184

    Question

    Hi,

    I have few scripts, I want to run some of the scripts in SQL Plus to Create the Objects, Then I want to run a batch file in SQL Loader for Data Loading and then I have to run few more Scripts to Clean the Data in SQL.

    Is there any way that I can do all in one Environment.. I mean using SQL Loader,

    How can I invoke SQL Plus, Run those scripts, then In SQl Loader and again in SQL Plus.

    Can any one please guide me HOW... Any Syntext or Script.

    Thanks


  2. #2
    Join Date
    Nov 2000
    Location
    Birmingham, UK
    Posts
    360
    Assuming you are running a UNIX environment:

    You should be able to put all the commands in one script. I've never done it with both sqlplus and sqlldr but I cant see why it cant be done. I usually use the following format when running sqlplus from a shell script:

    #
    # SHELL SCRIPT
    #

    sqlplus -s usr/password << MYSQL


    set head off

    select transaction_ref, pack_id from topm_log where date_generated
    between to_date('${DATE2}','DD-MON-YYYY') and to_date('${DATE3}','DD-MON-YYYY')
    and job_status='${ANS}'
    and pack_type=0
    order by transaction_ref;
    MYSQL

    #
    # SHELL SCRIPT CONTINUED
    #

    THis will run the sql between the two MYSQL's

    You can do the embed sqlldr calls in scripts as well e.g:

    mv $invitem_file $invitem_file.dat


    sqlldr userid=$ORACLE_USER/$ORACLE_PASSWORD log=$TMP/zinvitem.log control=$S
    QL_LOADER/zinvitem.ctl data=$invitem_file bad=$invitem_file.bad direct=true >> $
    LOGFILE
    RETVAL=$?


    mv $invitem_file.dat $invitem_file


    Hope that helps


  3. #3
    Join Date
    Feb 2001
    Posts
    184
    Thanks Fraze,

    Unfortunately I am Using 8i (8.1.5) on Win NT. I need to have some thing that could work with NT.

    Any Comments, Please.
    Thanks.

  4. #4
    Join Date
    Feb 2001
    Posts
    184
    Any Idea Please... Thanks

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    You can wrap this all in a single SQL*Plus script. You can invoke SQL*Loader from inside SQL*Plus by using SQL*Plus's HOST comand, e.g.

    ...
    REM Create table command
    CRATE TABLE my_table.....;
    REM Invoke another script from within this script
    @script_inside_script.sql
    REM Invoke SQL*Loader to load data into table
    HOST c:\oracle\my_ora_home\bin\sqlldr parfile=....
    REM Update some data
    UPDATE mytable .....;

    HTH,
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

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