How to pass null value in bind variable

Question

This is a sample query that shows I'm trying to get the book called 'sql for everyone' with a begin_date '2018-02-02' where authName is null. This query is perfectly fine. It returns a row similar to the one in the screenshot below.

SELECT
  bookName,
  begin_date,
  authName
FROM bookwh
WHERE bookName = :bname
AND begin_date = :dt
AND authName IS NULL

My question is how can I pass null as a bind variable? such as, and authName = :a_null I should be able to pass null

Answer 1

NULL is a special case in a WHERE clause and doesn't work when testing for equality. With nulls, you can only test for IS NULL or IS NOT NULL.

One way to accomplish what you want is to wrap the equality check in an nvl wrapper. The nvl() function takes 2 parameters. The first parameter is a value (in your case, a bind parameter) and the second value is the default value in case the first parameter is null.

AND authName = nvl(:your_bind_parameter, authName)

Another option is to use an OR statement.

AND (authName = :your_bind_parameter OR authName IS NULL)

NVL() a clean way to handle null bind parameters. Both of these methods have performance implications so check your explain plans.

For a more thorough explanation of your options, check out this blog on the Oracle website: https://blogs.oracle.com/sql/optional-search-parameters:-getting-rid-of-those-pesky-or-conditions.

Answer 2

Following code worked for me select * from table_name where ((:parameter is not null and authName = :parameter) or (:parameter is null and authName is null));