Why does NVL work over COALESCE in Oracle?

Question

I have the following column:

...
some_column NUMBER(1, 0)  DEFAULT NULL NULL,
...

which is used to hold a nullable Integer value.

Now I have a row with that column filled. I'm performing a patch using Spring's JdbcTemplate, meaning that I want to update the column only if the new value is not null:

UPDATE my_table SET some_column = COALESCE(?, some_column) WHERE...

This fails with:

Caused by: java.sql.SQLSyntaxErrorException: ORA-00932: inconsistent datatypes: expected CHAR got NUMBER

when I pass a Java null to ?. This means that COALESCE is not happy, since I passed two different types into it. My initial assumption is that Spring/JdbcTemplate somehow does not pass SQL null to the database, because a direct update to the database with:

UPDATE my_table SET some_column = COALESCE(null, some_column) WHERE...

works OK. However, when I replace the query with:

UPDATE my_table SET some_column = NVL(?, some_column) WHERE...

I get what I want with JdbcTemplate. What's happening, where's the difference?

Update:

Java code I'm using is as follows:

I have:

public class MyClass {
    private MyEnum enum;
    // Getters and setters
}

and MyEnum:

public enum MyEnum implements Serializable {
    SOME_VAL (0),
    SOME_OTHER_VAL (1),
    
    ...

    private final int status;

    MyEnum (int status) {
        this.status = status;
    }

    public int getStatus() {
        return status;
    }

getJdbcTemplate().update("UPDATE my_table SET some_column = COALESCE(?, some_column) WHERE...", myClass.getMyEnum() == null ? null : myClass.getMyEnum().getStatus());

Answer 1

From the NVL documentation:

The arguments expr1 and expr2 can have any data type. If their data types are different, then Oracle Database implicitly converts one to the other. If they cannot be converted implicitly, then the database returns an error. The implicit conversion is implemented as follows:

• If expr1 is character data, then Oracle Database converts expr2 to the data type of expr1 before comparing them and returns VARCHAR2 in the character set of expr1.
• If expr1 is numeric, then Oracle Database determines which argument has the highest numeric precedence, implicitly converts the other argument to that data type, and returns that data type.

From the COALESCE documentation:

Oracle Database uses short-circuit evaluation. The database evaluates each expr value and determines whether it is NULL, rather than evaluating all of the expr values before determining whether any of them is NULL.

If all occurrences of expr are numeric data type or any non-numeric data type that can be implicitly converted to a numeric data type, then Oracle Database determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to that data type, and returns that data type.

You will notice that NVL explicitly states it will perform an implicit conversion so that expr2 is the same data type as expr1 whereas COALESCE (although slightly confusingly worded) does not mention performing an implicit conversion (except for numeric data types) and will expect that all expressions in its argument list are the same data type.

Your query for NVL is effectively converted to:

UPDATE my_table
SET    some_column = CAST(
                       NVL(
                         :your_string_bind_variable,
                         CAST( some_column AS VARCHAR2 )
                       )
                       AS NUMBER
                     )
WHERE...

but your COALESCE function is:

UPDATE my_table
SET    some_column = COALESCE(
                       :your_string_bind_variable,
                       some_column
                     )
WHERE...

and expr1 and expr2 have different data types and the query raises an exception.

---

Assuming that there are no other columns being modified, you do not need to perform an UPDATE if the value is NULL as it is not going to change anything and could re-write your Java code as:

MyEnum enumValue = myClass.getMyEnum();
if ( enumValue != null )
{
  getJdbcTemplate().update(
    "UPDATE my_table SET some_column = ? WHERE...",
    enumValue.getStatus()
  );
}

Answer 2

Rewrite that UPDATE statement explicitely so that you do not change the row at all if the parameter is NULL.

This is from the preformance perspective much better than to update same to same (even such no-update produce undo must be logged.

This will also solve your problem with coalesce (as you do not used it)

UPDATE my_table SET some_column =  ?  
WHERE ? is not NULL /* do NOT update if the new value is NULL */
and ...

You must pass the same value twice or use named bind variable.

The root cause of the problem is that you pass the null parameter as a VARCHAR data type.

You may verify it if you run the following statment (note the explicite casting) you get the observed error

update tab
set some_column = COALESCE(cast (null as varchar2(10)), some_column) WHERE id = 1
;
-- SQL Error: ORA-00932: inconsistent datatypes: expected CHAR got NUMBER

So the surest way is to explicitely pass the data type of the paramater as well. Here is the example using SqlParameterValue

String sql = """update tab
set some_column = COALESCE(?, some_column) WHERE id = 1""";

// this fails
def updCnt = jdbcTemplate.update(sql, new SqlParameterValue(Types.VARCHAR,null));

// this works fine
def updCnt = jdbcTemplate.update(sql, new SqlParameterValue(Types.INTEGER,null));