Re: Scripts, Sqlloader, and Sqlplus

Programming wrote:
>
> 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

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. Received on Tue Jun 03 1997 - 00:00:00 CDT