SQLPATH Environment Variable in Oracle
Use SQLPATH for efficient SQL script management.
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?
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/scriptsCopy all the .sql scripts into above location and set SQLPATH parameter in .bash_profile
export SQLPATH=/home/oracle/scriptsNow 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 locEnjoy! 😋😋😋
