- How to bulk detach / attach databases in SQL Server - February 2, 2024
- How to enforce password policy in an Oracle database - 4 January 2024
- How do we change the name of the user / schema in an Oracle database - 1 December 2023
In the article we will see how to easily and quickly create a script that can execute multiple other scripts in a database Oracle in an environment Unix. It even provides the ability to stop in case of an error.
Creating the executable
We should make the script that we will call in which the base environment will be defined and the file that will contain all the scripts that will be executed:
We define the paths ORACLE_BASE, ORACLE_HOME, ORACLE_SID and its command sqlplus without connecting to the file with the parameters:
export ORACLE_BASE=/oracle/app/oracle export ORACLE_HOME=/oracle/app/oracle/product/12.1.0/db_1 export ORACLE_SID=DB_TEST $ORACLE_HOME/bin/sqlplus /nolog @/home/oracle/scripts/script.par
The parameter file
We make the file with the parameters that will be connected by holding through spool the results and contains the list of scripts:
We define it WHENEVERSQLERROR EXIT in case we want it to stop on error.
Fill in the username and password of the user with whom the scripts will be executed.
With spool save the log in the path we have defined.
We can during this time connect with another user, e.g. sysdba and do recompile (utlrp):
--WHENEVER SQLERROR EXIT SQL.SQLCODE – αν θέλουμε connect username/password spool /home/oracle/scripts/results.out @/home/oracle/scripts/script.sql @/home/oracle/scripts/script2.sql @/home/oracle/scripts/script3.sql spool off connect / as sysdba @/oracle/app/oracle/product/12.1.0/db_1/rdbms/admin/utlrp.sql exit
To finally run the script as a background process (after first giving the file execute permission):
nohup ./script_tade.sh &