Oracle/PL SQL/SQL null comparison on where clause

Question

Just a question about dealing will null values in a query.

For example I have the following table with the following fields and values

TABLEX Column1 1         2         3       4         5       --------- Column2 null A B C null 

I'm passing a variableY on a specific procedure. Inside the procedure is a cursor like this

CURSOR c_results IS SELECT * FROM TABLEX where column2 = variableY 

now the problem is variableY can be either null, A, B or C if the variableY is null i want to select all record where column2 is null, else where column2 is either A, B or C.

I cannot do the above cursor/query because if variableY is null it won't work because the comparison should be

CURSOR c_results IS SELECT * FROM TABLEX where column2 IS NULL 

What cursor/query should I use that will accomodate either null or string variable.

Sorry if my question is a bit confusing. I'm not that good in explaining things. Thanks in advance.

Answer 1

Either produce different SQL depending on the contents of that parameter, or alter your SQL like this:

WHERE (column2 = variableY) OR (variableY IS NULL AND column2 IS NULL) 

Answer 2

Oracle's Ask Tom says:

where decode( col1, col2, 1, 0 ) = 0  -- finds differences 

or

where decode( col1, col2, 1, 0 ) = 1  -- finds sameness - even if both NULL 

Safely Comparing NULL Columns as Equal