5

Is there a way to check is a list of values is NULL in PL/SQL?

I have something along the lines of:

SELECT * FROM myTable t WHERE t.dataId IN (:myList) OR :myList IS NULL

At run-time, the :myList symbol is substituted for a list of strings e.g.

SELECT * FROM myTable t WHERE t.dataId IN ('a', 'b', 'c') OR  ('a', 'b', 'c') IS NULL

I've realised that ('a', 'b', 'c') IS NULL is invalid PL/SQL, so I wondered if there is another way to check a list of values evaluate to NULL.

The behaviour I'm attempting to emulate would evaluate ('a', 'b', 'c') to NOT NULL. I'm trying to avoid creating another variable (e.g. :myListFlag) which would return '' if the list was empty.

Alex
  • 5,364
  • 9
  • 54
  • 69

2 Answers2

7

Maybe COALESCE will help you:

It evaluates a list of values left to right and returns the first value that is NOT NULL. If all values are NULL, it evaluates to NULL, which is a single value that you can check for IS NULL.

 SELECT * FROM myTable t WHERE t.dataId IN (:myList) OR COALESCE(:myList) IS NULL
trapicki
  • 1,881
  • 1
  • 18
  • 24
  • 4
    Thanks! I think this function may be the solution. I've realised that if :myList is empty it returns `''` so I think the solution would be to use `COALESCE(:myList, '') IS NULL`. Does that look right? – Alex Aug 08 '13 at 13:03
  • @Alex: I do not have a Oracle DB at hand to test that. Please report if it works. – trapicki Aug 08 '13 at 15:00
  • 2
    I used the form COALESCE(:myList, NULL) IS NULL but COALESCE(:myList, '') IS NULL would also work since Oracle treats '' as NULL as per http://stackoverflow.com/a/15357813/908677 or http://searchoracle.techtarget.com/answer/Oracle-treats-empty-strings-as-NULL – Elijah Lofgren Sep 29 '16 at 17:56
  • COALESCE(:myList) still works, it just expects comma separated values. It might give you a lint error, but when you run the code, it goes through because :myList will actually be a list or null. – Thanos T. M. Feb 13 '20 at 19:47
0

I am not familiar with the following syntax doing what you expect:

WHERE t.dataId IN (:myList)

In general, variable substitution results in a single value. So, if the value is 'a,b,c', then this will match a value of 'a,b,c', but not 'a'.

You can do this with other expressions. The most general (i.e. works in most databases) is something like:

where ','||:myList||',' like '%,'||t.data_id||',%'

With such a structure, you can readily say:

where ','||:myList||',' like '%,'||t.data_id||',%' and :myList is not null

There are definitely other ways of doing this. A quick search on the web comes up with things like this.

Community
  • 1
  • 1
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I'm using the Spring NamedParameterJdbcTemplate class, an example is here:http://stackoverflow.com/questions/14433438/how-to-use-a-list-of-string-in-namedparameterjdbctemplate-to-get-results – Alex Aug 08 '13 at 12:17
  • @Alex . . . That's cool. I'm glad this functionality is starting to be supported. My recommendation would be to convert the list to a string and pass that as another parameter which you can compare to `NULL` (or `''`). – Gordon Linoff Aug 08 '13 at 15:33