You can convert the Number column to Character and then check for NVL.
with data as ( select 'col1' col1, 1 col2, 'col3' col3 from dual union all select 'col1' col1, null col2, 'col3' col3 from dual ) select col1 || '|' || nvl(to_char(col2), 'null') || '|' || col3 from data; COL1||'|'||NVL(TO_CHAR(COL2),'NULL')||'|'||COL3 -------------------------------------------------- col1|1|col3 col1|null|col3
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.
select 'ABC'||NVL(cast(null as number), '123') || 'DEF' from dual; ABC123DEF
What oracle does here, are TWO implicit datatype conversions.
first conversion: change the second parameter of nvl from string to number ('123' => 123).
second conversion change the number output of nvl from number to string (123 => '123')
Of cause that is a lot of unessesary stuff to do. Best is ALWAYS to use explizit datatype conversion.