Oracle 19. date century. difference in behaviour between 2 updates

This is somewhat unexpected, but at least it is documented.

The documentation on DECODE (https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/DECODE.html#GUID-39341D91-3442-4730-BD34-D3CF5D4701CE) states:

Oracle automatically converts the return value to the same data type as the first result. If the first result has the data type CHAR or if the first result is null, then Oracle converts the return value to the data type VARCHAR2.

So what happens is that in

DECODE (d1, TRUNC(d), NULL, TRUNC(d))

the first result is NULL, so the DECODE result becomes VARCHAR2. If your session is set to nlsdateformat = 'dd.mm.yy' for instance, then the date 1900-01-01 gets converted into the string '01.01.00'. Now you want to update d1 with this value, and d1 is a DATE, so yet another conversion takes place, and the string '01.01.00' becomes the date 2000-01-01.

The solution to get the procedure independent from session date format settings is to cast the NULL explicitly to the desired data type:

DECODE (d1, TRUNC(d), CAST(NULL AS DATE), TRUNC(d)).

The corrected procedure:

CREATE OR REPLACE PROCEDURE ppp(d IN DATE)
IS
BEGIN
  UPDATE ppdates
    set d1 = DECODE (d1, TRUNC(d), CAST(NULL AS DATE), TRUNC(d))
      , d2 = DECODE (d , NULL    , d2  , TRUNC(d));
END;

But this also shows that it is a very bad idea to work with two-digit years. With yy in NLSDATEFORMAT, any implicit conversion will mutilate the date's year to two digits, which can end up in undesired results as shown in this example. So, don't only get your procedure session-independent, but also get rid of any two-digit year handling.