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!

Batch file connecting with db instances

Discussion in 'Oracle Apps Technical' started by Bufoss, Dec 22, 2016.

  1. Bufoss

    Bufoss Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Greece
    Hi all,
    I am trying to create a batch script which connects to all database instances as SYS and execute a query in order to check if there are invalid objects. The batch file prints the number of invalid objects, OK if there are no invalid and Not connected if error ORA-12541: TNS:no listener is raised

    script1.sql
    Code (SQL):

    SET pages 0

    SELECT COUNT(*)
      FROM DBA_OBJECTS d
    WHERE d.STATUS = 'INVALID';
     
    My batch code is the following

    Code (Text):

    @echo off
    setlocal EnableExtensions
    mode 140,70

    :: Customize Window
    title Test


    set password=password123456


    echo ******
    echo Report
    echo ******


    for %%S in (
      customer1
      customer2
      customer3
      customer4
    ) do (
        exit|sqlplus -s SYS/"%password%"@Server1/%%S  as SYSDBA @script.sql >  __query.tmp
     
        for /f "tokens=3" %%f in ('find /c /i "ORA-" "__query.tmp"') do set errorFound=%%f

            IF %errorFound% > 0
                for /f "usebackq" %%a in ("__query.tmp") do (
                    if %%a==0 (
                         echo %%S [OK]
                     ) else if %%a gtr 0 (
                         echo %%S [%%a invalid]
                     )
                )
            ELSE  echo %%S [Not Connected]

        del __query.tmp
     
    )

    :End
    endlocal
    pause >nul
    Could you help me please do it ?
    My find command it seems not to be the appropriate
     
    Last edited: Dec 22, 2016
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,379
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    find in Windows doesn't work the same way as find in UNIX/Linux. You might want to consider installing UnxUtils:

    -http://unxutils.sourceforge.net/