Scripts, Sqlloader, and Sqlplus

[Programming]

I am new to Sqlloader, and am using it to
load some data. This also requires a lot
of commands to be done in SQLPLUS.

I am trying to put everything that I have
done into one script that can be run from
the Unix prompt.

It is easy to put either SQLLOADer or SQLPLUS
to the command prompt, with a script name
as an input parameter.

However, how can I combine the two, into one
Unix script? Is there a way to check any
kind of return codes? ie. If failed, abort.

I would want the script to:
- create some temporary tables in SQLPLUS
- load the data with SQLLOADer
- do update, and other SQL commands in SQLPLUS

I suppose the other question would be, can all
these same things be done from a SQLLOADer script?

Rodger Lepinsky

[Sven Barzanallana]

To answer your last question, a SQL*Loader script (or control file)
can only be used by SQL*Loader and not any other tool, including
SQL*Plus. The good news is that you can boil down your job to at
least 2 files: 1) A shell script and 2) The SQL*Loader control script.

Here's a sample Korn Shell script:

#!/bin/ksh

sqlplus system/manager <<EOSQL1
create table temp_table1 (
name varchar2(10));
create table temp_table2 (
number number));
exit
EOSQL1

sqlldr system/manager control=load_it.ctl log=load_it.log

sqlplus system/manager <<EOSQL2
drop table temp_table1;
drop table temp_table2;
exit
EOSQL2

Obviously, this is only an example, but it shows how you can
tell a shell script to pipe commands into sqlplus. Every line
between the sqlplus command and the EOSQL1 label are not
executed by the shell, but rather by sqlplus.

Hope this helps,

Sven Barzanallana
Lead Oracle DBA
Greyhound Lines, Inc.

[Sven Barzanallana]

Forgot to answer your other question for error checking.

Have a look at this example:

sqlplus system/manager <<EOSQL1
create table temp_table (name varchar2(10));
exit
EOSQL1
rc=$?

if [ ${rc} != 0 ];
then
print "There has been an error"
exit 1
fi

Sven