12cR2 tightens up ORA-01841 for zero year ANSI dates, but not for Oracle SQL syntax

In moving some more code from an 11gR2 database to a 12cR2 database, I found another change where a piece of code that works in 11gR2 doesn’t compile in 12cR2.

In this instance a view was being created with a projected date column which used the ANSI DATE syntax. Here is a simplified test script:

CREATE OR REPLACE VIEW test1 AS
SELECT date '0000-01-01' date_col
FROM dual
/
DROP VIEW test
/

CREATE OR REPLACE VIEW test2 AS
SELECT TO_DATE('0000-01-01','YYYY-MM-DD') date_col
FROM dual
/

Running this on 11gR2 gives:

SQL>CREATE OR REPLACE VIEW test1 AS
  2  SELECT date '0000-01-01' date_col
  3  FROM   dual
  4  /

View created.

SQL>CREATE OR REPLACE VIEW test2 AS
  2  SELECT TO_DATE('0000-01-01','YYYY-MM-DD') date_col
  3  FROM   dual
  4  /

View created.

Now running this on 12cR2 gives:

SQL> CREATE OR REPLACE VIEW test1 AS
  2  SELECT date '0000-01-01' date_col
  3  FROM   dual
  4  /
SELECT date '0000-01-01' date_col
            *
ERROR at line 2:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0


SQL> CREATE OR REPLACE VIEW test2 AS
  2  SELECT TO_DATE('0000-01-01','YYYY-MM-DD') date_col
  3  FROM   dual
  4  /

View created.

The date is zero and thus the error message is correct in 12cR2 for the ANSI DATE syntax.

About Oracle: DBA_DEPENDENCY_COLUMNS

A colleague asked if there was a way to do column level dependency tracking recently. He wanted to know for a given view, which tables and the columns on those tables, it was dependent upon, without, of course, reading through the code.

I was vaguely aware that since 11gR1 Oracle has been tracking fine grained (column) dependencies, but couldn’t find a way of seeing the details stored, until I found this interesting article from Rob Van Wijk:

About Oracle: DBA_DEPENDENCY_COLUMNS

I passed the details on to our DBA who implemented it and it seemed to work, for us. Your mileage may vary, of course.

Some¬†comments on Rob’s blog post, bearing in mind, of course, that it was written in 2008 and refers to 11gR1:

  1. D_ATTRS contains values other than “000100000A”. I’ve observed this in a basic 12c install and a production 11gR2 install
  2. D_ATTRS is commented in $ORACLE_HOME/rdbms/admin/dcore.bsq as “/* Finer grain attr. numbers if finer grained */”
  3. D_REASON is commented in $ORACLE_HOME/rdbms/admin/dcore.bsq as “/* Reason mask of attrs causing invalidation */”.¬† On my basic 12c installation, all rows contain NULL for this value although on a production 11gR2 database I observed a handful of rows with values in this column.
  4. Noted from the comments against Rob’s article is the opportunity to vote for this feature on OTN here