Why comparing varchar/number works

user_7996932 0
Viewed 62

Why this block gives no error with NULL; inside. And raises error (ORA-06502: PL/SQL: numeric or value error) with any other instruction inside?

BEGIN
  IF 'x' = 1 THEN
    NULL;
  --dbms_output.put_line('test');
  END IF;
END;
plsql oracle sql
1 Answers

What I think is happening in your case is that you have the compilation parameter PLSQL_OPTIMIZE_LEVEL set to a value that's high enough to allow the compiler to optimise your code.

So, when you have NULL; inside your IF statement, it can be rewritten to remove that IF statement when compiled.

However, with something other than NULL; present inside the IF statement, it can no longer be removed, so the check 'x' = 1 is then made. Oracle will convert character values to numbers if it's being compared to a number, so in your case that means the comparison fails because x is not a number.

E.g.:

The following works:

ALTER SESSION SET plsql_optimize_level = 2;

BEGIN
  IF 'x' = 1 THEN
    NULL;
    --dbms_output.put_line('test');
  END IF;
END;
/

But the following fails:

ALTER SESSION SET plsql_optimize_level = 1;

BEGIN
  IF 'x' = 1 THEN
    NULL;
    --dbms_output.put_line('test');
  END IF;
END;
/

The only difference is the PLSQL_OPTIMIZE_LEVEL value - 2 or higher, and the compiler will optimize away the irrelevant IF statement, 1 or lower and it won't.

user_44793090
Related