Description

Sql_plus_remote_launcher allows you to run a bunch of SQL Oracle scripts in several databases without opening a session for each of them with toad. This is particularly useful to propagate a local modification of you database to others database (development, integration, etc.).

Compatibility

Only for oracle databases.
The script only works on Windows and you need to have Oracle installed.

Installation

Download the SQLPlus Remote Launcher and dezip it.

How to use it

1. put the SQL scripts in the scripts_to_launch directory

2. configure the file oracle_instances.conf with the instances where you want to deploy your scripts. These instances are defined in your tnsnames.ora file. Note that you can use # to comment an instance.

  	LOGIN1/PASSWORD@MY_INSTANCE
 	#LOGIN1/PASSWORD@MY_INSTANCE
 	LOGIN1/PASSWORD@MY_INSTANCE_2

3. open a dos command

4. navigate to directory containing the launcher.bat

 	cd D:\Aden\oracle\sqlplus-remote-launcher

5. run the script

 	launcher.bat

6. answer the question. They are asked to prevent any unfortunate configuration.

7. that's it !

Sources

:: sql_plus_remote_launcher.bat
:: launches the scripts that are in the directory scripts_to_launch remotely, on every oracle instance defined in oracle_instances.conf


@ECHO OFF

@rem DONE : parcourir tous les fichiers du répertoire
@rem DONE : pouvoir gérer plusieurs connexions (avec fichier de conf)
@rem TODO : definir un ensemble de fichiers à executer à distance (en passant un fichier avec les chemins absolus) >> en fait il suffit de verifier au debut qu'il n'y a pas de fichiers sqlplus.tmp, si y en a un : il l'emporte. Ainsi l'ordre sera respecté.
@rem TODO : gerer les cas d'erreurs (pas de fichier sql, ou pas de conf)
@rem TODO : recompiler tous les packages/trigger en erreur (voir recompile_objets.sql)
@rem AIDE DOS: http://fr.wikibooks.org/wiki/DOS/For



@rem dir scripts_to_launch remotely /b /s > scripts_to_launch.dat

echo Building a temporary sqlplus file ...
break > sqlplus.tmp			  
FOR %%f IN (scripts_to_launch\*.sql) DO (
	echo @@%%f	>> sqlplus.tmp
) 
echo quit >> sqlplus.tmp 

echo ===================================================================
echo == Content of the temporary sqlplus file ...
echo ===================================================================
echo [	start of file	]
more sqlplus.tmp
echo [	end of file	]
set /p scriptOk=Is the SQLPlus script above correct (y/n)
IF NOT "%scriptOk%" == "y" (
	echo Good bye then
	pause
	exit
)
echo ===================================================================
echo == Oracle instances that will be impacted ...
echo ===================================================================
echo [	start of file	]
more oracle_instances.conf
echo [	end of file	]
set /p instancesOk=Are you sure you want to launch it on these oracle instances ? (y/n)
IF NOT "%instancesOk%" == "y" (
	echo Good bye then
  pause
	exit
)


FOR /F "eol=#" %%i IN (oracle_instances.conf) DO (
echo ===================================================================
echo Oracle instance : %%i
echo ===================================================================
sqlplus %%i @sqlplus.tmp
)

del sqlplus.tmp

pause