for example: SQL> create table t1(a int,b int);Table created.SQL> SQL> insert into t1 values (1,2);1 row created.SQL> insert into t1 values (2,3);1 row created.SQL> insert into t1 values (3,4);1 row created.SQL> SQL> select * from t1 where a=nvl(&a,a) and b=nvl(&b,b); Enter value for a: null Enter value for b: 2 old 1: select * from t1 where a=nvl(&a,a) and b=nvl(&b,b) new 1: select * from t1 where a=nvl(null,a) and b=nvl(2,b) A B ---------- ---------- 1 2
SQL> create table t1(a int,b int);Table created.SQL>
SQL> insert into t1 values (1,2);1 row created.SQL> insert into t1 values (2,3);1 row created.SQL> insert into t1 values (3,4);1 row created.SQL>
SQL> select * from t1 where a=nvl(&a,a) and b=nvl(&b,b);
Enter value for a: null
Enter value for b: 2
old 1: select * from t1 where a=nvl(&a,a) and b=nvl(&b,b)
new 1: select * from t1 where a=nvl(null,a) and b=nvl(2,b) A B
---------- ----------
1 2