0

I have a query that consists of nullable parameter. If input parameter is null, then it should select all value.

Previously it was working with Oracle 18c. But now it doesn't work with oracle 19c.

It doesn't work if data in the table is null

Any idea why or how ?

SELECT NAME, SURNAME FROM MY_TABLE WHERE NAME = NVL(?,NAME);

I tried this one and results is the same

SELECT NAME, SURNAME FROM MY_TABLE WHERE NAME = NAME;

Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
Gerry Gry
  • 182
  • 5
  • 17
  • This is because you cannot use `=` with NULL, because `something = NULL` is not true or false. but unknown. A condition in the `WHERE` clause, however, must be true for the row to get selected. This is the reason it cannot work for names that are null. Not in Oracle 19c, not in 18c nor any other version. You must be mistaken. – Thorsten Kettner Nov 08 '21 at 06:42
  • @ThorstenKettner, yeah i realized it is my mistake about the version. Anyay I'm not looking on how to solve the problem, I just want to understand why, so can you post your answer and I will accept yours – Gerry Gry Nov 08 '21 at 13:48

1 Answers1

1

I would use an explicit NULL check in the WHERE clause:

SELECT NAME, SURNAME
FROM MY_TABLE
WHERE NAME = ? OR ? IS NULL;

As to why your previous version with NVL has stopped working, maybe it has to do with a change in how parameter binding in prepared statements.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360