Scripts in Oracle. Learn how to create them with examples

Hello everyone. Today we wanted to share with you a way to run Oracle commands within Linux scripts.

A very convenient way to load environment variables is by using the oraenv command. For example, we can run this script, to prevent the prompt from coming out on the screen:

export ORAENV_ASK=NO;
export ORACLE_SID=ORCL1;
. oraenv

echo $ORACLE_HOME
/data/oracle/product/12.2.0/db1 

Once the Oracle variables are loaded, we can execute queries as follows:

echo "Select * from dual;" | sqlplus / as sysdba

The result is:

SQL*Plus: Release 12.2.0.1.0 Production on Wed Aug 21 14:13:12 2019
Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 12.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options 
SQL>
D
-
X 
SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 12.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options 

We can complicate it a little more. To load the variables and launch the query at the same time, we run:

export ORAENV_ASK=NO;export ORACLE_SID=ORCL2;. oraenv 1>/dev/null; echo "Select * from dual;" | sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Aug 21 14:14:55 2019
Copyright (c) 1982, 2016, Oracle. All rights reserved. 

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 

SQL>
D
-
X 
SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 

With these functionalities we already have the skeleton to execute our scripts. We can program scripts even as root and that execute the statements with your – oracle:

As you have seen, in order not to produce a syntax error , you have to use the escape \ character in front of the double quotation marks:

\"Select * from dual;\"

In this way we can even execute scripts that pass as parameters values with simple quotes and variables:

su - oracle -c "export ORAENV_ASK=NO;export ORACLE_SID=ORCL2;. oraenv 1>/dev/null; echo \"@script.sql '${fechainicio}'" | sqlplus / as sysdba"

We hope you find it useful the next time you have to program an Oracle script on a machine.

See you in future posts. If you don’t want to miss any, subscribe to our monthly newsletter to be aware of our entries with only one email per month.

 

Comments are closed.