ORA-43907: Potential behavior change with Oracle RU 19.28.0

As I said multiple times, we have the best and most knowledgeable customers out there. Julian Frey is one of them, and there is a good and legit reason why he is an ACE Director since (I think) two years. Julian alerted me the other day about an ORA-43907: Potential behavior change with Oracle RU 19.28.0. And I guess, some of you would like to learn about this, too.

Photo by Shannon Potter on Unsplash

What you may see?

Oracle Database 19c Release Update 19.28 contains a fix for Bug 36386077 – ORA-43907 NEEDS TO BE RAISED FOR CONVERSION FUNCTIONS IF DEFAULT VALUE IS NOT LITERAL OR BIND. And as it looks like, this fix is responsible for a behavior change in some cases. As of now, I can’t tell how often this may happen, and how likely it is that your application will be affected. But since neither Julian nor I could find any documentation as of now on My Oracle Support, I’d like to describe in more detail what Julian made me aware of.

Here is a simplified test case Julian shared with me:

alter session set nls_date_format = 'DD.MM.YYYY HH24:MI:SS';

SELECT TO_DATE('Sept 17, 2025, 11:30.'
          DEFAULT TO_DATE(sysdate,'DD.MM.YYYY HH24:MI:SS')
            ON CONVERSION ERROR, 'Month dd, YYYY, HH:MI A.M.') "Value"
 FROM DUAL;

When you run this script in 19.27 or earlier, it will complete with a result, e.g.:

Value
-------------------
18.09.2025 15:11:47

But when you run it in 19.28 (or newer), it will fail with the following error:

ORA-43907: Argument in DEFAULT ... ON CONVERSION ERROR clause must be a literal or bind variable.

https://docs.oracle.com/error-help/db/ora-43907/
                                    43907. 0000 -  "Argument in DEFAULT ... ON CONVERSION ERROR clause must be a literal or bind variable."
*Document: YES
*Cause:    An attempt was made to provide an argument to the DEFAULT ... ON
           CONVERSION ERROR clause that was not a literal or bind variable.
*Action:   Provide an argument that is either a literal or bind variable.
                                    Error at Line: 2 Column: 18

Again, let me emphasize that I can’t predict how likely it is that you may hit this. But this blog post may help to find a quick explanation in case you search for the error message.

Why does it happen?

As I mentioned before, this change is happening because of the inclusion of the fix for:

• Bug 36386077 – ORA-43907 NEEDS TO BE RAISED FOR CONVERSION FUNCTIONS IF DEFAULT VALUE IS NOT LITERAL OR BIND

from RU 19.28.0 onwards. The fix is present from 23.6.0 onwards, too, and it is in Oracle ADB-S as well.

A similar fix got introduced earlier for 23ai:

• Bug 33624635 – ORA-43907 NEEDS TO BE RAISED FOR TO_NUMBER WHEN CONVERSION ERROR HANDLE IS PRESENT

but isn’t in 19c.

These fixes were necessary as conversion functions should only allow literals or bind expressions as return value argument.
With the fixes in case other return values were used, it correctly signals now an ORA-43907.

I did confirm with oradiff.oracle.com that the fix for Bug 36386077 is included from 19.28 and 23.6 onwards.

Revert to the old behavior

If you really need to revert to the previous behavior, a _fix_control option is available after you applied the fix for bug:

Bug 38952945 – ADD _FIX_CONTROL TO DISABLE FIX FOR 36386077 AND 33624635

Currently, it is unknown when this additional fix adding the _fix_control will be available in an RU. You may need to check with oradiff.oracle.com whether it is included.

Further Links and Information

• Julian Frey’s Blog
• Julian Frey’s ACE Director Profile
• Bug 36386077 – ORA-43907 needs to be raised for conversion functions if default value is not literal or bind

–Mike