Re: Scripts, Sqlloader, and Sqlplus

Sven Barzanallana wrote:
>
> 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.

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