In Oracle, it is possible to execute SQL script from OS (Unix, Windows etc.) prompt.
Below is a sample SQL script:
[oracle@localhost sql]$ cat script1.sql
SET ECHO ON
SELECT employee_id, first_name, job_id, salary
FROM employees
WHERE department_id = 20;
exit
[oracle@localhost sql]
You can execute this script from OS prompt as follows:
[oracle@localhost sql]$ sqlplus hr/oracle@freepdb1 @script1.sql
SQL*Plus: Release 23.26.0.0.0 - Production on Fri Dec 5 23:32:34 2025
Version 23.26.0.0.0
Copyright (c) 1982, 2025, Oracle. All rights reserved.
Last Successful login time: Fri Dec 05 2025 23:32:17 +00:00
Connected to:
Oracle AI Database 26ai Free Release 23.26.0.0.0 - Develop, Learn, and Run for Free
Version 23.26.0.0.0
SQL> SELECT employee_id, first_name, job_id, salary
2 FROM employees
3 WHERE department_id = 20;
EMPLOYEE_ID FIRST_NAME JOB_ID SALARY
----------- -------------------- ---------- ----------
201 Michael MK_MAN 13000
202 Pat MK_REP 6000
SQL>
SQL> exit
Disconnected from Oracle AI Database 26ai Free Release 23.26.0.0.0 - Develop, Learn, and Run for Free
Version 23.26.0.0.0
[oracle@localhost sql]$
If you do not want to print the version information during login and exit, you can use the -s (silent) switch in the sqlplus command. Below is a better way to execute the script.
[oracle@localhost sql]$ sqlplus -s hr/oracle@freepdb1 @script1.sql
EMPLOYEE_ID FIRST_NAME JOB_ID SALARY
----------- -------------------- ---------- ----------
201 Michael MK_MAN 13000
202 Pat MK_REP 6000
[oracle@localhost sql]$
If you want to make the script dynamic so that you can execute the script with different variable each time, you can modify it as follows using the substitution variable (&)
[oracle@localhost sql]$ cat params.sql
SET VERIFY OFF
SET ECHO OFF
SELECT employee_id, first_name, salary from &1 WHERE department_id = &2;
exit
[oracle@localhost sql]$
This script is using substitution variable (&1 and &2) to accept new value for each execution. You can execute this dynamic script with the below command:
[oracle@localhost sql]$ sqlplus -s hr/oracle@freepdb1 @params.sql employees 10
EMPLOYEE_ID FIRST_NAME SALARY
----------- -------------------- ----------
200 Jennifer 1000
[oracle@localhost sql]$
We passed the table name for the 1st parameter, which is mapped to &1 in the script. Similarly, we passed the department_id for the 2nd parameter, which is mapped to &2. This script can be executed with different parameter value for each run and this command can be called from any OS script (such as Unix shell script or Windows Batch script etc.).
Watch the below video for a detailed tutorial & demo on this topic.
=======================================================================
** Email to info@shreyantech.com to get the Tip of the Day in your mailbox.
** Connect with the author / trainer: https://suchitdba.blogspot.com/p/connect-with-author-database-trainer.html
No comments:
Post a Comment