How to filter your sql query using declared parameters in Oracle SQL

Question

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.

possible duplicate of [How do I pass arguments to a PL/SQL script on command line with SQLPLUS?](http://stackoverflow.com/questions/18620893/how-do-i-pass-arguments-to-a-pl-sql-script-on-command-line-with-sqlplus) — , Jan 14 '15 at 09:23
do you want to concatenate previous sql query with new one .....? — koushik veldanda, Jan 14 '15 at 09:26

score 1 · Accepted Answer · answered Jan 14 '15 at 09:27

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>

How to filter your sql query using declared parameters in Oracle SQL

1 Answers1