Home » SQL & PL/SQL » Client Tools » Catch Oracle Login error in batch file. (Oracle 10g)
Catch Oracle Login error in batch file. [message #542290] Mon, 06 February 2012 07:02 Go to next message
Messages: 33
Registered: August 2009
Location: india

I have a Batch file(.bat) where I get the user name password ans schema name as a input . Based on that input I connect the sql and run some update query from bat file itself. Now I have issue ,when the user give wrong credintials it ask for the credintials again.

Is there any way to check the credintials of oracle and give the invalid credintial error via bat file. The sample of my Bat file is as follows.

@echo off
for /f %%i in ('sqlplus -s usename/password@db @H:\test_db_connection.sql') do @set count=%%i
echo %count%
IF %count% EQU 1 ECHO ("Database connection working fine")
IF %count% NEQ 1 ECHO ("Not able to connect to database")

This is not working fine. can some one help me on this

Thanks in advance
Re: Catch Oracle Login error in batch file. [message #542298 is a reply to message #542290] Mon, 06 February 2012 07:54 Go to previous messageGo to next message
Michel Cadot
Messages: 67141
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use -l option.

C:\>sqlplus -h

SQL*Plus: Release - Production

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Usage 1: sqlplus -H | -V

    -H             Displays the SQL*Plus version and the
                   usage help.
    -V             Displays the SQL*Plus version.

Usage 2: sqlplus [ [<option>] [<logon>] [<start>] ]

  <option> is: [-C <version>] [-L] [-M "<options>"] [-R <level>] [-S]

    -C <version>   Sets the compatibility of affected commands to the
                   version specified by <version>.  The version has
                   the form "x.y[.z]".  For example, -C 10.2.0
    -L             Attempts to log on just once, instead of
                   reprompting on error.
    -M "<options>" Sets automatic HTML markup of output.  The options
                   have the form:
                   HTML [ON|OFF] [HEAD text] [BODY text] [TABLE text]
                   [ENTMAP {ON|OFF}] [SPOOL {ON|OFF}] [PRE[FORMAT] {ON|OFF}]
    -R <level>     Sets restricted mode to disable SQL*Plus commands
                   that interact with the file system.  The level can
                   be 1, 2 or 3.  The most restrictive is -R 3 which
                   disables all user commands interacting with the
                   file system.
    -S             Sets silent mode which suppresses the display of
                   the SQL*Plus banner, prompts, and echoing of

  <logon> is: (<username>[/<password>][@<connect_identifier>] | /)
              [AS SYSDBA | AS SYSOPER] | /NOLOG

    Specifies the database account username, password and connect
    identifier for the database connection.  Without a connect
    identifier, SQL*Plus connects to the default database.

    The AS SYSDBA and AS SYSOPER options are database administration

    The /NOLOG option starts SQL*Plus without connecting to a

  <start> is: @<URL>|<filename>[.<ext>] [<parameter> ...]

    Runs the specified SQL*Plus script from a web server (URL) or the
    local file system (filename.ext) with specified parameters that
    will be assigned to substitution variables in the script.

When SQL*Plus starts, and after CONNECT commands, the site profile
(e.g. $ORACLE_HOME/sqlplus/admin/glogin.sql) and the user profile
(e.g. login.sql in the working directory) are run.  The files may
contain SQL*Plus commands.

Refer to the SQL*Plus User's Guide and Reference for more information.

Re: Catch Oracle Login error in batch file. [message #542692 is a reply to message #542290] Thu, 09 February 2012 13:58 Go to previous message
Messages: 487
Registered: July 2003
Location: WPB, FL
Senior Member
Try this:
@echo off
sqlplus -l -s usename/password@db @H:\test_db_connection.sql
IF     ERRORLEVEL 0 ECHO "Database connection working fine"
IF NOT ERRORLEVEL 0 ECHO "Not able to connect to database"

[Updated on: Thu, 09 February 2012 14:02] by Moderator

Report message to a moderator

Previous Topic: Check Null Value for SQL*PLUS Variable
Next Topic: pl/sql developer showing error
Goto Forum:

Current Time: Sun May 31 03:19:17 CDT 2020