Warnings Available in Oracle Database 10g

In the following sections, let’s take a look at most of the compile-time warnings that were introduced in Oracle Database 10g Release 1. I will offer an example of the type of code that will elicit the warning and also point out some interesting behavior (where present) in the way that Oracle has implemented compile-time warnings.

PLW-05000: mismatch in NOCOPY qualification between specification and body

The NOCOPY compiler hint tells Oracle that, if possible, you would like it to not make a copy of your IN OUT arguments. This can improve the performance of programs that pass large data structures, such as collections or CLOBs.

You need to include the NOCOPY hint in both the specification and the body of your program (relevant for packages and object types). If the hint is not present in both, Oracle will apply whatever is specified in the specification.

Here is an example of code that will generate this warning:

  /* File on web: plw5000.sql * /
  CREATE OR REPLACE PACKAGE plw5000
  IS 
     TYPE collection_t IS
        TABLE OF VARCHAR2 (100);

     PROCEDURE proc (
        collection_in IN OUT NOCOPY
           collection_t);
  END plw5000;
  /
  CREATE OR REPLACE PACKAGE BODY plw5000
  IS
     PROCEDURE proc (
        collection_in IN OUT
           collection_t)
        IS
        BEGIN
          DBMS_OUTPUT.PUT_LINE ('Hello!');
        END proc;
  END plw5000;
  /

Compile-time warnings will display as follows:

  SQL> SHOW ERRORS PACKAGE BODY plw5000
  Errors for PACKAGE BODY PLW5000:

  LINE/COL ERROR
  -------- --------------------------------
  3/20     PLW-05000: mismatch in NOCOPY
           qualification between
           specification and body

  3/20     PLW-07203: parameter
           'COLLECTION_IN' may benefit
           from use of the NOCOPY
           compiler hint