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?
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
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