Oracle Observations

December 14, 2007

PLSQL_Warnings in 11g

Filed under: 11g,PL/SQL — bigdaveroberts @ 4:05 pm

In preparation for the on/off/on upgrade of our application from Oracle 8.1.7 to 9.2.8, I thought it would be interesting to migrate the PL/SQL to my installation of an 11g Oracle database that came out of the UKOUG conference OTN install fest, and also, out of interest see what warnings come out of the PL/SQL compiler when you set PLSQL_Warnings=’enable:all’ compilation option.

Pleasingly out of almost 45,000 lines of source, only one new error was generated:

SQL> show errors;
Errors for PACKAGE BODY P_BODY:

LINE/COL ERROR
——– —————————————————————–
211/5 PLS-00593: default value of parameter “P_PARAM” in body must match that of spec

Here the only problem was that while Oracle 8i is happy for a default parameter value to be specified only in the package body definition, Oracle 11g requires the default parameter to be specified in both the package and the package body.

Eg. while the following code works in 8i, it doesn’t in later releases:

SQL> create or replace PACKAGE P_BODY IS
2 PROCEDURE delete_actual_data (
3 p_parameter IN VARCHAR2
4 );
5 END;
6 /

Package created.

SQL> create or replace PACKAGE body P_BODY IS
2 PROCEDURE delete_actual_data (
3 p_parameter IN VARCHAR2 := ‘N’
4 ) IS
5 BEGIN
6 NULL;
7 END;
8 END;
9
10 /

Package body created.

A small anomaly:

If a package does include errors or warnings, then if the package and body is compiled, then the errors are not displayed, only the following cryptic error message:

SQL> alter PACKAGE P_BODY compile plsql_warnings=’enable:all’;

SP2-0809: Package altered with compilation warnings

SQL> show errors;
SP2-0564: Object “p_bodyplsql_warnings=’enable:all'” is INVALID, it may not be described.

However if only the body is compiled, then the errors and warnings are successfully displayed.

So that leaves me with a selection of PLW warnings to evaluate and/or fix:

PLW-05005: function P_FUNCTION returns without value at line 244
PLW-06002: Unreachable code
PLW-06006: uncalled procedure “P_PROCEDURE” is removed.
PLW-06009: procedure “P_PROCEDURE” OTHERS handler does not end in RAISE or RAISE_APPLICATION_ERROR
PLW-06010: keyword “NAME” used as a defined name
PLW-06010: keyword “REF” used as a defined name
PLW-06010: keyword “RESULT” used as a defined name
PLW-07202: bind type would result in conversion away from column type
PLW-07203: parameter ‘P_PARAMETER’ may benefit from use of the NOCOPY compiler hint
PLW-07204: conversion away from column type may result in sub-optimal query plan

A superficial review of the error messages manual indicates that the number of potential warnings has grown substantially from the 9 and 11 warnings of 10g releases 1 and 2, to a total of 36 documented warnings in 11g release 1. With 3 of the 8 warning types received being new ones from 11g.

Oracle 10 R1 manual page:

http://download.oracle.com/docs/cd/B14117_01/server.101/b10744/plwus.htm#sthref64

Oracle 10 R2 manual page:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14219/plwus.htm#ERRMG53664

Oracle 11 R1 manual page:

http://download.oracle.com/docs/cd/B28359_01/server.111/b28278/plwus.htm

Advertisements

Blog at WordPress.com.