How can we run multiple scripts in Oracle in Unix environment

• Author
• Recent Posts

Stratos Matzouranis

The journey began in 2008 where I officially started working in the field of IT (IT). Starting the first semester of school I realized a special attraction towards databases and automation. I have been involved with databases such as Microsoft SQL Server / Oracle Database, data analysis and automations using the command line (CLI), Visual Basic for Applications and Python. Through years of experience I have developed these capabilities so that I can make my life easier. For me, the purpose of every IT guy and every office worker is to have the knowledge so that through tools he can work a little but produce a lot. Through this his website DataPlatform.gr I try to offer knowledge and propose solutions to everyday problems.

Certifications:

Latest posts by Stratos Matzouranis (see all)

• How we enable Unified Auditing in Oracle Database - 7 October 2024
• What is PostgreSQL and how do we do a full installation - September 2, 2024
• How do we configure GoldenGate replication to read from Oracle Data Guard Standby - 2 August 2024

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:

vi script_tade.sh

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:

vi script.par

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

The execution

To finally run the script as a background process (after first giving the file execute permission):

nohup  ./script_tade.sh &