SQLPATH Environment Variable in Oracle

To execute any .sql script, we will have to go to specific folder which contains .sql script, start sqlplus and then run the script. It would be amazing if we can run any .sql script inside SQLPLUS from any location!

• What is SQLPATH Variable?

• Set SQLPATH Parameter

What is SQLPATH Variable?

SQLPATH points a location on server which contains all the .sql scripts. Anytime you execute a .sql script without giving the script location, sqlplus will first search the script inside SQLPATH location.

If the script is not available in the SQLPATH location, then sqlplus search the script in the current folder where you started sqlplus from.

Set SQLPATH Parameter

First create a location which will contain all your .sql scripts

mkdir -p /home/oracle/scripts

Copy all the .sql scripts into above location and set SQLPATH parameter in .bash_profile

export SQLPATH=/home/oracle/scripts

Now you can be in any location, start sqlplus and simply execute any .sql script

cd /tmp                            --> start sqlplus from /tmp loc
sqlplus / as sysdba
SQL> @database-size.sql            --> runs from $SQLPATH loc

Enjoy! 😋😋😋