On12c I have noticed that the 'OR' is validating both the conditions in irrespective of whether first condition is true which was not the case in 10g. Below example should make it clear,
Table - TEST_OR
colA number(10),
ColB varchar(10));
PL/SQL Block
SET serveroutput ON
DECLARE
lv_a NUMBER(10);
lv_b VARCHAR2(10) :='aaaa';
BEGIN
SELECT colA INTO lv_a
FROM TEST_OR
WHERE lv_b = 'aaaa' OR colA = lv_b;
END;
Oracle 10g behaviour - PL/SQL block is successfully executed.
Oracle 12c behaviour - PL/SQL block fails with below error while validating condition ColA = lv_b as ColA is of datatype number.
ORA-01722: invalid number
ORA-06512: at line 7
.
I would like to know if there is any way to force the query to work the 10g way though I know that the piece of code is wrong , however these are legacy codes and client cannot bear the cost in identifying such queries and fixing them.