Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

sql query with bind variable NULL

ora_1978Sep 4 2021

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
This post has been answered by Jan Gorkow on Sep 4 2021
Jump to Answer

Comments

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
Answer

Hi @ora-1978 ,
here's my test which works as expected:
image.pngBe 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

Marked as Answer by ora_1978 · Sep 4 2021
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
;
1 - 5

Post Details

Added on Sep 4 2021
#sql
5 comments
2,547 views
1 mention