1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Catch Oracle Login error in batch file.

Discussion in 'SQL PL/SQL' started by karthikeyanc2003, Feb 6, 2012.

  1. karthikeyanc2003

    karthikeyanc2003 Active Member

    Messages:
    24
    Likes Received:
    0
    Trophy Points:
    100
    Hi,

    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
    karthik
     
  2. el8022

    el8022 Guest

    In the BAT file, set up the SQLPLUS connect string to log in as a known user.
    You will also pass the user entries as arguments to the sql script.

    In the sql script, first enter WHEN SQLERROR EXIT SQL.SQLCODE
    In the sql script, then connect using the parameters passed

    If the connect succeeds, you can continue with the script.
    If the connection fails, you will immediately exit to the BAT file. The error code 1017 will be returned to the BAT file.

    In the BAT file, you will want to check the ERRORLEVEL to see if it is 1017 and act as needed.

    Example:


    -- ss/ss@venus is the "known" user/password.
    -- uname and upwd are the user entered username and password.

    (BAT FILE)

    sqlplus -s ss/ss@venus @testscript.sql UNAME UPWD
    if errorlevel 1017 goto EXITERR
    if errorlevel 0 goto NORMAL
    :EXITERR
    @echo "ERROR: user credentials invalid"
    goto end
    :NORMAL
    @echo "SUCCESS: Normal exit"
    :END

    (SQL SCRIPT)

    -- testscript.sql

    whenever sqlerror exit sql.sqlcode
    connect &1/&2@venus

    select systimestamp from dual;
    --
    -- other sql stmts here
    --
    exit
     
    Sadik likes this.