sqloracleoracleexception

Why doesn't Oracle throw an error here


I'm looking at a script I didn't write here. and it looks something like this:

SELECT
...
AND (
       A.FIELD IN
       (
        ...
        ...
       )
       OR B.FIELD IN
       (
        ...
        ...
       ) 
    )
...

On it's own the script runs fine. No errors. But when I change it to:

SELECT
...
AND B.FIELD IN
       (
        ...
        ...
       ) 
...

Oracle throws an ORA-01722: invalid number. How come it doesn't throw it for the first query though?

Updated

A.FIELD is a number B.FIELD is a VARCHAR2

The value is working against is a number. So I understand and agree with the error, but I wondered why it isn't thrown in the first query. But the second it is.


Solution

  • From Oracle's docs:

    When evaluating a logical expression, PL/SQL uses short-circuit evaluation. That is, PL/SQL stops evaluating the expression as soon as the result can be determined. This lets you write expressions that might otherwise cause an error.

    See http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/fundamentals.htm#sthref481 for further explanation and examples.

    They also have this separate documentation specifically mentioning that for OR: http://www.oracle-base.com/articles/misc/short-circuit-evaluation-in-plsql.php