This warning makes me happy. A function that does not return a value is a very badly designed program. This is a warning that I would recommend you ask Oracle to treat as an error with the “ERROR:5005” syntax in your PLSQL_WARNINGS setting.
You already saw one example of such a function—no_return. That was a very obvious chunk of code; there wasn’t a single RETURN in the entire executable section. Your code will, of course, be more complex. The fact that a RETURN may not be executed could well be hidden within the folds of complex conditional logic.
At least in some of these situations, though, Oracle will still detect the problem. The following program demonstrates one of those situations:
SQL> CREATE OR REPLACE FUNCTION no_return (
SP2-0806: Function created with compilation warnings
SQL> SHOW ERRORS
Oracle has detected a branch of logic that will not result in the execution of a RETURN, so it flags the program with a warning. The plw5005.sql file on the book’s web site contains even more complex conditional logic, demonstrating that the warning is raised for less trivial code structures as well.
PLW-06002: unreachable code
Oracle will now perform static (compile-time) analysis of your program to determine if any lines of code in your program will never be reached during execution. This is extremely valuable feedback to receive, but you may find that the compiler warns you of this problem on lines that do not, at first glance, seem to be unreachable. In fact, Oracle notes in the description of the action to take for this error that you should “disable the warning if much code is made unreachable intentionally and the warning message is more annoying than helpful.” I will come back to this issue at the end of the section.
You already saw an example of this compile-time warning in the “A quick example” section at the beginning of this section. Now consider the following code:
/* File on web: plw6002.sql */
Oracle shows the following compile-time warnings for this program:
I see why line 7 is marked as unreachable: l_checking is set to FALSE, and so line 7 can never run. But why is line 5 marked “unreachable.” It seems as though, in fact, that code would always be run! Furthermore, line 13 will always be run as well because the GOTO will direct the flow of execution to that line through the label. Yet it is tagged as unreachable.
The reason for this behavior is simple: the unreachable code warning is generated after optimization of the code. To determine unreachability, the compiler has to translate the source code into an internal representation so that it can perform the necessary analysis of the control flow.
The compiler does not give you false positives; when it says that line N is unreachable, it is telling you that the line truly will never be executed, accurately reflecting the optimized code.
There are currently scenarios of unreachable code that are not flagged by the compiler. Here is one example:
/* File on web: plw6002.sql */
Certainly, the call to DBMS_OUTPUT.PUT_LINE is unreachable, but the compiler does not currently detect that state. This scenario, and others like it, may be covered in future releases of the compiler.
PLW-07203: parameter 'string' may beneﬁt from use of the NOCOPY compiler hint
As mentioned earlier in relation to PLW-05005, use of NOCOPY with complex, large IN OUT parameters can improve the performance of programs under certain conditions. This warning will flag programs whose IN OUT parameters might benefit from NOCOPY. Here is an example of such a program:
/* File on web: plw7203.sql */
This is another one of those warnings that will be generated for lots of programs and may become a nuisance. The warning/recommendation is certainly valid, but for most programs the impact of this optimization will not be noticeable. Furthermore, you are unlikely to switch to NOCOPY without making other changes in your code to handle situations where the program terminates before completing, possibly
PLW-07204: conversion away from column type may result in sub-optimal query plan
This warning will surface when you call a SQL statement from within PL/SQL and rely on implicit conversions within that statement. Here is an example:
/* File on web: plw7204.sql */
The salary column is numeric, but I am comparing it to a string value. The optimizer may well disable the use of an index on salary because of this implicit conversion.
Related tightly to this warning is PLW-7202: bind type would result in conversion away from column type.
Please check back next week for the continuation of this article.
blog comments powered by Disqus