Home arrow Oracle arrow Page 4 - Compile-Time Warnings for Oracle DB 10g

PLW-05005: function string returns without value at line string - Oracle

In this fourth part of a nine-part series on managing PL/SQL code, you will learn about the compile-time warnings that were introduced in Oracle Database 10g Release 1. 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). Copyright © 2006 O'Reilly Media, Inc. All rights reserved. Used with permission from the publisher. Available from booksellers or direct from O'Reilly Media.

TABLE OF CONTENTS:
  1. Compile-Time Warnings for Oracle DB 10g
  2. PLW-05001: previous use of 'string' (at line string) conflicts with this use
  3. PLW-05004: identifier string is also declared in STANDARD or is a SQL built-in
  4. PLW-05005: function string returns without value at line string
By: O'Reilly Media
Rating: starstarstarstarstar / 2
November 08, 2007

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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.



 
 
>>> More Oracle Articles          >>> More By O'Reilly Media
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

ORACLE ARTICLES

- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: