If you remove the materialize hint (see query below), the coalesce will result in a "ORA-43918: This argument must be a literal" error (tested on database versions 12.2 and 18XE).
This is caused by the first "to_number ... default null on conversion error" statement, but I'm not really sure why. I found a reference
that suggests the cursor_sharing_exact hint may fix this, but that doesn't seem to apply to this case.
Also not sure why the materialize hint works here, to me it seems to have nothing to do with it. But thanks Arnoud, you saved the day by suggesting that fix :-)