It appears that in Oracle, 'a'||NULL give you a, which is not my experience in other DBMSs.
This means that an expression like coalesce('id: '||id,'missing') won’t work, since the first expression is never NULL.
I suppose I can use a CASE … END expression to get the job done, but that’s starting to go over the top.
How can I get the concatenation to return NULL, or is there some other simple trick I should be using?
Oracle exhibits mixed behavior with NULLs. On the one hand, there are several functions like GREATEST, LEAST, etc.. that will return NULL if a single operand is NULL. On the other hand, aggregations like MAX, MIN, SUM, AVG will conveniently ignore NULLs during aggregation. Similarly, string operators treat NULL as an empty string. In fact, '' is equivalent to NULL for a string datatype. We regularly rely on this to assemble concatenated strings with optional/conditional, dynamically generated additions. You wouldn't want an email body to be blank because some piece of your message body that you spliced in happened to be NULL.
In terms of coalesce('id: '||id,'missing'), here are some options:
DECODE(id,NULL,'missing','id: '||id) -- works in SQL only, not in PL/SQL
CASE WHEN (id IS NULL) THEN 'missing' ELSE 'id: '||id END
NVL(NULLIF('id: '||id,'id: '),'missing')