Facing "ORA-01722 Invalid Number" error when trying to access CHAR data type column with numeric value without quotes
The reality is - you *always* had this problem, you just got very very lucky with Oracle 10g in that you did not see it. Here's an example:
SQL> create table t1 ( x1 varchar2(10), y1 int );
Table created.
SQL> create table t2 ( x2 varchar2(10), y2 int );
Table created.
SQL>
SQL> insert into t1 values ('12',1);
1 row created.
SQL> insert into t1 values ('13',1);
1 row created.
SQL> insert into t1 values ('14',1);
1 row created.
SQL> insert into t1 values ('15',1);
1 row created.
SQL>
SQL> insert into t2 values ('12',5);
1 row created.
SQL> insert into t2 values ('13',5);
1 row created.
SQL> insert into t2 values ('14',5);
1 row created.
SQL> insert into t2 values ('15',5);
1 row created.
SQL> insert into t2 values ('blah',2);
1 row created.
SQL>
SQL> exec dbms_stats.gather_table_stats('','T1')
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats('','T2')
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> select /*+ leading(t1 t2) use_nl(t2) */ *
2 from t1, t2
3 where t1.x1 = t2.x2
4 and t2.x2 > 0
5 and t1.y1 = 1;
X1 Y1 X2 Y2
---------- ---------- ---------- ----------
12 1 12 5
13 1 13 5
14 1 14 5
15 1 15 5
4 rows selected.
SQL>
SQL> select /*+ leading(t2 t1) use_nl(t1) */ *
2 from t1, t2
3 where t1.x1 = t2.x2
4 and t2.x2 > 0
5 and t1.y1 = 1;
ERROR:
ORA-01722: invalid number
Depending on which plan the optimizer took, you *always* were running this risk. What has most likely happened is that 12c has chosen a different plan, and you've run into this problem.
The bottom line is - mis-matched data types where you are relying on the database to make a conversion rather than it being done explicitly in code, then you are always going to have problems.
As an intermim, all I can suggest is setting optimizer_features_enable to a lower value until you can rectify the code and/or database structures.