Then I execute it twice with different optimization settings. What error is returned for the executions ?

alter session set plsql_optimize_level =0;

alter procedure plch_test compile;

exec plch_test;

alter session set plsql_optimize_level =2;

alter procedure plch_test compile;

exec plch_test;

In the first case, with no optimization, the "date error" path is taken as there isn't a 30th of February.

In the second case, you may wonder what effect the optimization might have. The assignment of the number 10 to v_num doesn't need to be in the loop. This isn't a cursor loop or a WHILE loop, so the code inside the loop is guaranteed to be executed. If it was executed once prior to entering the loop, it would result in a value_error.

In fact, a date error is still returned. Possibly there is no such optimization. Or possibly it is done in a way that means it is only executed once, in the correct sequence, but is not repeated. Tests shown in AskTom indicate that there is some optimization happening.

Ultimately, the PL/SQL optimizing compiler is a black box. It would be nice to assume that it wouldn't change the behaviour of the code. But I can't help looking at the procedure and wondering, would it be "wrong" (or more specifically a bug) if an optimization switched it to return a value error.

5 comments:

First a correction: you forgot the "is" in the create line.Then a suggestion: use 02 instead of Feb in'v_date := to_date(i||'-02-2011','dd-mm-yyyy'); so people can test your code without "nls_language" problems.

I would suggest that the optimization is "smart" enought to expect an exception at that point (v_num) so it will not take that out of the loop. I think that it's great that the "funcionality" is the same (as we hope when using different optimizations settings).

Perhaps the mental model of 'in' and 'out' of the loop is invalid. There might be a 'first pass' and a 'subsequent pass' with the optimizations performed for the latter.All guesswork - unless someone wants to parse the DIANA code.