sql query with bind variable NULL

ora_1978

create table null_test (eno number, ename varchar2(20), salary number);

insert into null_test values (1, 'Peter',1000);

insert into null_test values (null, 'john',null);

insert into null_test values (3, null,3000);

commit;

Query Required:

One single select query

select * from null_test where eno = :p_eno ; -- :p_eno when assigned NULL ,I want all three records to get displayed

select * from null_test where eno = :p_eno ; -- :p_eno when 1.I want all one record with eno 1 should get displayed

same for varchar2 fields ename

Regards,

Vinodh

Jan Gorkow

Hi @ora_1978 ,

here's my test which works as expected:

Be aware, that the second query is case sensitive. There is no ename 'peter' in the table. You have to search for 'Peter' to get the row. Of course you can modify the query so it is no longer case sensitive.

Best regards

Jan

Jan Gorkow

Hi @ora_1978 ,

check this out:

SELECT *
 FROM null_test
 WHERE eno = :p_eno OR :p_eno IS NULL;

SELECT *
 FROM null_test
 WHERE ename = :p_ename OR :p_ename IS NULL;

Best regards

Jan

ora_1978

No records coming while passing NULL to bind variable and no records coming when i pass value example peter .

Jan Gorkow

Hi @ora_1978 ,

here's my test which works as expected:

Be aware, that the second query is case sensitive. There is no ename 'peter' in the table. You have to search for 'Peter' to get the row. Of course you can modify the query so it is no longer case sensitive.

Best regards

Jan

Solomon Yakobson

WHERE eno = :p_eno OR :p_eno IS NULL;

will not use index on eno, assuming there is one:

SQL> create index null_test_idx1 on null_test(eno);


Index created.


SQL> variable p_eno number
SQL> explain plan for select * from null_test where eno = :p_eno or :p_eno is null;


Explained.


SQL> select * from table(dbms_xplan.display);


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
Plan hash value: 3898976153


---------------------------------------------------------------------------------------
| Id  | Operation                 | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |           |     1 |    68 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS STORAGE FULL| NULL_TEST |     1 |    68 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   1 - storage(:P_ENO IS NULL OR "ENO"=TO_NUMBER(:P_ENO))
       filter(:P_ENO IS NULL OR "ENO"=TO_NUMBER(:P_ENO))


Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


18 rows selected.


SQL>

Compare with:

SQL> explain plan for select * from null_test where eno = :p_eno union all select * from null_test where :p_eno is null;


Explained.


SQL> select * from table(dbms_xplan.display);


PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
Plan hash value: 3654053033


-------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                |     4 |   272 |     5   (0)| 00:00:01 |
|   1 |  UNION-ALL                           |                |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| NULL_TEST      |     1 |    68 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | NULL_TEST_IDX1 |     1 |       |     1   (0)| 00:00:01 |
|*  4 |   FILTER                             |                |       |       |            |          |
|   5 |    TABLE ACCESS STORAGE FULL         | NULL_TEST      |     3 |   204 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   3 - access("ENO"=TO_NUMBER(:P_ENO))
   4 - filter(:P_ENO IS NULL)


Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


22 rows selected.


SQL>

Or we could use hint use_concat with or_predicates argument, however I don't recall if it is yet documented:

SQL> explain plan for select /*+ use_concat(or_predicates(1)) */ * from null_test where eno = :p_eno or :p_eno is null;


Explained.


SQL> select * from table(dbms_xplan.display);


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
Plan hash value: 3906413706


--------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                |     4 |   272 |     3   (0)| 00:00:01 |
|   1 |  CONCATENATION                        |                |       |       |            |          |
|*  2 |   FILTER                              |                |       |       |            |          |
|   3 |    TABLE ACCESS STORAGE FULL          | NULL_TEST      |     3 |   204 |     2   (0)| 00:00:01 |
|*  4 |   FILTER                              |                |       |       |            |          |
|   5 |    TABLE ACCESS BY INDEX ROWID BATCHED| NULL_TEST      |     1 |    68 |     1   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN                  | NULL_TEST_IDX1 |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   2 - filter(:P_ENO IS NULL)
   4 - filter(LNNVL(:P_ENO IS NULL))
   6 - access("ENO"=TO_NUMBER(:P_ENO))


Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


24 rows selected.


SQL>

SY.

Frank Kulash

Hi, @ora_1978

No records coming while passing NULL to bind variable and no records coming when i pass value example peter .

The solution Jan posted works when I try it. Post a complete test script (including how you define and initialize the bind variable) that fails.

Another way is:

SELECT  *
FROM	null_test
WHERE	DECODE (:p_eno,  eno, 1,   NULL, 1) = 1
;