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 su – 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.