Re: Using SQL*Plus in Batch Environment

Jonathan,

Your response definitely pointed me in the right direction! Thanks.

Here is basically what I came up with:

Unix shell script:
  if sqlplus /nolog _at_sql.sql "'userid'" "'password'" other sql parms   then
    :
  else
    echo ERROR SQLPlus session failed...     exit 1
  fi

SQL Script:
  WHENEVER SQLERROR EXIT 1
  CONNECT &&1./&&2.
  BEGIN
    NULL;
  END;
  /
  PROMPT Connection to Oracle successful...   {logic}
  EXIT I had to do the "BEGIN NULL; END; /" to get a SQL error for a failed connection. Unfortunately SQLPlus does not exit on an error from one of its own commands (CONNECT is a SQLPlus command); it must run into an error from a SQL statement or PL/SQL block.

Again, thanks. I was able to solve my problem with your input.

Randy

Jonathan Gennick wrote:

> Randy,
>
> Try starting your SQL*Plus script file these commands:
>
> WHENEVER SQLERROR EXIT 1
> CONNECT USERID/PASSWORD
> ...
> ...the rest of your script
>
> Then execute this file from the OS prompt using the /NOLOG
> option:
>
> sqlplus /nolog _at_yourfile
>
> If the password is incorrect the script will error out and
> control will return to your OS batch file. I assume that the
> error code of 1 would be passed back as well, but I havn't
> tested that.
>
> Let me know if this works.
>
> Jonathan
>
> On Fri, 13 Feb 1998 11:50:20 -0600, Randy Lewis
> <rlewis_at_systemhouse.mci.com> wrote:
>
> >Question to the Knowledgible,
> >
> >I am attempting to use SQL*Plus in an un-attended batch environment.
 We
> >are using Korn shell under Solaris. I want the ability to detect an
> >unsuccessful attempt at logging into the Oracle database using
> >SQL*Plus. I have tried testing the return code in the shell, but all
 I
> >get is zero. Only if I fail the login attempt three times does
 SQL*Plus
> >return a non-zero return code; but in an un-attended batch
 environment
> >it will only attempt one login and then abort.
> >
> >Shell Script:
> >
> > if sqlplus userid/password _at_$SQL_DIR/sql.sql 2>&1 1>logfile
> > then
> > :
> > else
> > echo ERROR occured during SQLPlus run...
> > exit 1
> > fi
> >
> >
> >The above code produces a return code of zero if the userid or the
> >password is invalid. Any help would be appreciated.
> >
> >Randy
> >
Received on Mon Feb 16 1998 - 00:00:00 CET