Is there any way to get my query results to print NULL value as (null)?

I've tried the NVL function, NVL(col2, 'null') but this won't work since there's a mismatch on datatypes. I can get the NULL value printed by not concatenating the columns and save the results as csv, but that does not give me the formatting I want.

The reason why you got an error message has to do with automatic datatype conversions, that oracle will do.

the concatanation "varchar2||number||varchar2" compares two different datatypes. Since a string is expected for the || operator to work, the number will be implicitly converted to varchar2. Meaning the database executes: varchar2||TO_CHAR(number)||varchar2. Including default nls conversion.

The NVL function works slightly differently. It is overloaded to accept several datatypes, like varchar2, number, date and some more.

That means there are at least 3 versions

NVL(varchar2,varchar2) return varchar2

NVL(number,number) return number

NVL(date,date) return date

The parser now looks at the first parameter. In your case that was a number. That means the NVL(number,number) return number version is used.

The second parameter that you added is a string. In this case oracle tries to implicitly convert this string into a number. But this failed (to_number('null') => error) and you got the error message.

If this string parameter can be converted into a number, then the statement will succeed.