3 Replies Latest reply on Sep 4, 2012 8:23 AM by BluShadow

    Validating Connection from Batch file

    Hulk
      Hi,

      I am trying to create a .bat file where I need to take inputs for below variables
      DNAME
      UNAME
      PSWD
      So I have done like this
      SET /p DNAME="Enter the Database Name "
      SET /p UName="Enter the Username "
      SET /p Pswd="Enter the Password "

      Now I need to validate whether I am able to establish the connection

      sqlpus -s "%UName%/%Pswd%@%DNAME%"

      if the above connection is valid one then perform certain action else need to echo user that Access is denied.
      (ex: I am trying to say if cond is valid
      stmt-1
      else
      echo message to user. )
      May I know how can I implement the above step.
        • 1. Re: Validating Connection from Batch file
          BluShadow
          well, for starters you should use the -L option too...
          c:\>sqlplus -S -L fred/fred@unknown
          ERROR:
          ORA-12154: TNS:could not resolve the connect identifier specified
          
          
          SP2-0751: Unable to connect to Oracle.  Exiting SQL*Plus
          as that will just try a single attempt at connecting.

          Then for Windows command batch files, you should test the ERRORLEVEL to see if it is 0 or 1. (0 = success, 1 = failure), though if it was successful, it won't return to the batch script until sql*plus is exited.
          • 2. Re: Validating Connection from Batch file
            Hulk
            Attempt-1:

            @ECHO OFF
            ECHO Need not provide any quotes for the string values
            PAUSE
            rem set =step1
            :step1
            SET /p DBNAME="Enter the Database Name "
            SET /p UName="Enter System Username "
            SET /p Pword="Enter System Password "
            rem echo The computername is %computername%
            sqlplus -S -L "%UName%/%Pword%@%DBNAME%" @con1.sql
            set ret=%ERRORLEVEL%
            echo The error level is %ERRORLEVEL%
            if %ERRORLEVEL%==0 goto error1

            if not %ret%==0 goto error2

            :error1
            echo everything is fine.......
            goto end

            :error2
            echo something went wrong
            goto end

            :end
            echo this is the end
            ------------------------------------
            Demonstration

            C:\Users\testuser>TEST
            Need not provide any quotes for the string values
            Press any key to continue . . .
            Enter the Database Name GGG
            Enter System Username G
            Enter System Password G
            ERROR:
            ORA-01017: invalid username/password; logon denied


            SP2-0751: Unable to connect to Oracle. Exiting SQL*Plus
            The error level is 1
            something went wrong
            this is the end
            ------------------------------------
            I dnt want this message to get displayed
            ERROR:
            ORA-01017: invalid username/password; logon denied


            SP2-0751: Unable to connect to Oracle. Exiting SQL*Plus

            Is there any possibility to hide it?
            ---------------------------
            Attempt-2
            I have modified the above code as shown below

            sqlplus -S -L "%UName%/%Pword%@%DBNAME%" @con1.sql > t.log

            Error message is getting logged in the t.log file that is fine but the batch file is not going further until I press the enter twice(space b/w Enter System Password
            and The error level is 1.). Is there any method to make my code run without pressing enter.

            demonstration
            C:\Users\testuser>TEST
            Need not provide any quotes for the string values
            Press any key to continue . . .
            Enter the Database Name
            Enter System Username
            Enter System Password


            The error level is 1
            something went wrong
            this is the end
            --------------------------------

            Edited by: Hulk on Sep 3, 2012 11:47 PM
            • 3. Re: Validating Connection from Batch file
              BluShadow
              Well this seems to work ok for me...
              c:\>more conntest.bat
              @ECHO OFF
              ECHO Need not provide any quotes for the string values
              PAUSE
              rem set =step1
              :step1
              SET /p DBNAME="Enter the Database Name "
              SET /p UName="Enter System Username "
              SET /p Pword="Enter System Password "
              rem echo The computername is %computername%
              sqlplus -S -L "%UName%/%Pword%@%DBNAME%" @con1.sql > nul
              set ret=%ERRORLEVEL%
              echo The error level is %ERRORLEVEL%
              if %ERRORLEVEL%==0 goto error1
              
              if not %ret%==0 goto error2
              
              :error1
              echo everything is fine.......
              goto end
              
              :error2
              echo something went wrong
              goto end
              
              :end
              echo this is the end
              
              c:\>more con1.sql
              select * from dual
              /
              
              exit
              
              c:\>conntest
              Need not provide any quotes for the string values
              Press any key to continue . . .
              Enter the Database Name ggg
              Enter System Username g
              Enter System Password g
              The error level is 1
              something went wrong
              this is the end
              
              c:\>conntest
              Need not provide any quotes for the string values
              Press any key to continue . . .
              Enter the Database Name test_db
              Enter System Username scott
              Enter System Password tiger
              The error level is 0
              everything is fine.......
              this is the end
              I didn't have to press return for anything except the initial obvious prompts.