How to Invoke SQL Plus in SQL Loader..

[Oracledba8]

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

[fraze]

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

[Oracledba8]

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.

[Oracledba8]

Any Idea Please... Thanks

[jmodic]

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,