DosTips.com

Posted: **22 Nov 2016 14:01**

Hi all,
I want to make a batch script in order to verify some database instances that
do not have invalid objects.

I have created the following sql file :

check.sql

Code: Select all

SELECT owner, object_name, object_type, status
  FROM dba_objects
 WHERE status = 'INVALID'
/

And the executable file is the following :

Code: Select all

sqlplus SYS/"1!password!1"@server1.domain.gr:1521/customer1 as SYSDBA
@check.sql

sqlplus SYS/"1!password!1"@server1.domain.gr:1521/customer2 as SYSDBA
@check.sql

........

sqlplus SYS/"1!password!1"@server1.domain.gr:1521/customer20 as SYSDBA
@check.sql

As you can see the Username, password, hostname are the same. Only the sid is modified in each script

The problem I have is that the connection is done successfully but the script is not executed automatically.
Moreover, is there any way to do it with loop (as only the sid is modified) and print it as header the customer?

Thank you in advance

Posted: **22 Nov 2016 15:53**

It has been a long time since I have had the pleasure of working with Oracle. For the past 6+ years I have been stuck with SQL Server and DB2 on z/os (blech)

You need to put the @check.sql on the same line as the sqlplus call.
I can't remember, but you may need to add EXIT to your check.sql script. It certainly can't hurt.

A simple FOR loop in your batch script takes care of writing the code only once with changing SID.

Code: Select all

@echo off
for %%S in (
  customer1
  customer2
  ...
  customer20
) do sqlplus SYS/"1!password!1"@server1.domain.gr:1521/%%S as SYSDBA @check.sql

Dave Benham

Posted: **22 Nov 2016 19:10**

Dave is correct, you'll need EXIT 0; concluding your SQL script.

Here is a portion of a batch code I use to execute a SQL package after I run exports from a source system:

Code: Select all

echo ********************************************************>>%logfile%
echo Execute Oracle SQL Package                                >>%logfile%
echo ********************************************************>>%logfile%

SET ORACLE_SID=DMB
SET MB_USER=FIN_META
SET MB_PSWD=fin_meta
SET ORA_SRVR=DMB
SET SQLPATH=Scripts\SQL\
SET SQLPKGPATH=ORA_MB_PACKAGES\
SET SQLSCRIPT=PKG_ADPRD_DRM_FNDRPL_CE_INPUT_MEMBERS.sql

sqlplus -S %MB_USER%/%MB_PSWD%@%ORA_SRVR% @%MAINPATH%%SQLPATH%%SQLPKGPATH%%SQLSCRIPT%>>%logfile%

SET myError2=%errorlevel%
IF %myError2%==0 goto NormalExit

echo *********************************************************>>%logfile%
echo Error Encountered in Execute Oracle SQL Package          >>%logfile%
echo *********************************************************>>%logfile%

*.sql script:

Code: Select all

SET HEADING OFF
SET VERIFY OFF

exec PKG_ASC_DRM_PREP_RAW_DATA.DRM_FNDRPL_CE_INPUT_MEMBERS;

EXIT 0;

Let us know if you need any help - happy to lend a hand!

Posted: **23 Nov 2016 13:31**

I appreciate your help.
It works perfect.

Thank you all for your answers

DosTips.com

SQLPLUS Connect and running script

SQLPLUS Connect and running script

Re: SQLPLUS Connect and running script

Re: SQLPLUS Connect and running script

Re: SQLPLUS Connect and running script