Oracle scripts. Learn to create them with examples

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

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

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

echo $ORACLE_HOME
/datos/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 execute:

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 features we already have the skeleton to run our scripts. We can program scripts even as rootand execute the sentences with su – oracle:

Oracle scripts with oraenv gpsos

As you have seen, so that a syntax error does not occur, you must use the escape character \ in front of the double quotes:

\"Select * from dual;\"

In this way we can even execute scripts that pass as parameters values with single 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 next entries. If you don’t want to miss any, subscribe to our newsletter monthly to be aware of our tickets with just one email per month.