How to Invoke SQL Plus in SQL Loader..

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

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

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.

Any Idea Please... Thanks

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,