I have a Oracle SQL script and would like to pass input parameter to the script for execution. How would I achieve this?
Say I have date parameter testdate. This is how we do it in sql server
declare @testdate as date
select * from test where testdate=@testdate
Basically I have set of sql statements in a block which needs to be executed.
You can pass it as an argument to the SQL script and invoke it in SQL*Plus.
For example,
I have a script emp.sql which looks like -
select ename from emp where empno=&1
Now, I will invoke it in SQL*Plus passing the empno as an argument.
SQL> @D:\emp.sql 7369
old 1: select ename from emp where empno=&1
new 1: select ename from emp where empno=7369
ENAME
----------
SMITH
SQL>
Similarly, for a date argument -
SQL> @D:\emp.sql sysdate
old 1: select ename from emp where hiredate<=&1
new 1: select ename from emp where hiredate<=sysdate
ENAME
----------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
ENAME
----------
JAMES
FORD
MILLER
14 rows selected.
SQL>
For string type, you need to have single-quotation marks. For example -
SQL> @D:\emp.sql SCOTT
old 1: select empno from emp where ename='&1'
new 1: select empno from emp where ename='SCOTT'
EMPNO
----------
7788
SQL>