Compile-Time Warnings for Oracle DB 10g - PLW-05005: function string returns without value at line string
(Page 4 of 4 )
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 (
2 check_in IN BOOLEAN)
3 RETURN VARCHAR2
4 AS
5 BEGIN
6 IF check_in
7 THEN
8 RETURN 'abc';
9 ELSE
10 DBMS_OUTPUT.put_line (
11 'Here I am, here I stay');
12 END IF;
13 END no_return;
14 /
SP2-0806: Function created with compilation warnings
SQL> SHOW ERRORS
Errors for FUNCTION NO_RETURN:
LINE/COL ERROR
-------- ---------------------------------
1/1 PLW-05005: function NO_RETURN
returns without value at line 13
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 */
1 CREATE OR REPLACE PROCEDURE plw6002
2 AS
3 l_checking BOOLEAN := FALSE;
4 BEGIN
5 IF l_checking
6 THEN
7 DBMS_OUTPUT.put_line ('Never here...');
8 ELSE
9 DBMS_OUTPUT.put_line ('Always here...');
10 GOTO end_of_function;
11 END IF;
12 <<end_of_function>>
13 NULL;
14 14* END plw6002;
Oracle shows the following compile-time warnings for this program:
LINE/COL ERROR
-------- --------------------------------- 5/7 PLW-06002: Unreachable code
7/7 PLW-06002: Unreachable code
13/4 PLW-06002: Unreachable code
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 */
CREATE OR REPLACE FUNCTION plw6002 RETURN VARCHAR2
AS
BEGIN
RETURN NULL;
DBMS_OUTPUT.put_line ('Never here...');
END plw6002;
/
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 benefit 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 */
CREATE OR REPLACE PACKAGE plw7203
IS
TYPE collection_t IS TABLE OF VARCHAR2 (100);
PROCEDURE proc (collection_in IN OUT collection_t);
END plw7203;
/
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
leaving your data in an uncertain state.
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 */
CREATE OR REPLACE FUNCTION plw7204
RETURN PLS_INTEGER
AS
l_count PLS_INTEGER;
BEGIN
SELECT COUNT(*) INTO l_count
FROM employee
WHERE salary = '10000';
RETURN l_count;
END plw7204;
/
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.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
|
 This article is excerpted from chapter 20 of the book Oracle PL/SQL Programming, Fourth Edition, written by Steven Feuerstein and Bill Pribyl (O'Reilly; ISBN: 0596009771). Check it out today at your favorite bookstore. Buy this book now.
|
|
