Wednesday, January 13, 2010

Disabling specifc PL/SQL Warning messages

I've been reading through Oracle PL/SQL Programming, Fifth Edition, which I won when Steven Feuerstein came to the Sydney Oracle Meetup back in August.Lucky me.

One useful nugget I picked out was to do with PL/SQL Warnings. These were a great innovation in 10g where the compiler threw out messages when you were using dodgy code.

I've tried to be good and have all the warnings enabled. One thing bugged me though, and that was irritating messages that warned "PLW-07203: parameter 'P_VAL' may benefit from use of the NOCOPY compiler hint". I know that, unless there's a large volume of data involved, then the memory benefits of the hint aren't that great. There's probably a bit of a performance benefit if we don't need to duplicate the variable, but again so small that I've never needed to worry about it.

Reading the book, I found you could actually disable the messages for individual errors. My login.sql has now been amended to
  ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL','DISABLE:07203';

It is documented, but it isn't particularly clear. Quick demo

create or replace package test_warn is
    procedure caller;
end;
/

create or replace package body test_warn is
  procedure called (p_val in out varchar2) is
  begin
      if to_number(p_val) < 5 then
          select to_char(sysdate) into p_val from dual;
      end if;
  end called;
  --
    procedure caller is
        v_val varchar2(200) := '0';
    begin
        for i in 1..20 loop
            called (v_val);
            dbms_output.put_line(v_val);
            v_val := to_char(i);
        end loop;
    end caller;
    --
end;
/
 
ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';
ALTER PACKAGE test_warn compile;
show errors package body test_warn
Errors for PACKAGE BODY TEST_WARN:
LINE/COL ERROR
-------- -----------------------------------------------------------------
2/21     PLW-07203: parameter 'P_VAL' may benefit from use of the NOCOPY
         compiler hint

ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL','DISABLE:07203';
ALTER PACKAGE test_warn compile;
show errors package body test_warn

No errors.

3 comments:

mathewbutler said...

Hi Gary,

This looks like the warning is disabled for the entire session involved in the compile.

Are you aware of any means to disable the warning for a specific usage?

Let's say I have two warnings in a package and I only want to disable the warning for one of these usages.

Ideally, I'd like to add meta-data to the proc/function so that the compiler does not give a warning, and the next developer can see in the code that this is a deliberate usage.

Language supported meta-data would be a more robust means of achieving this rather than a comment, which is the fall back approach.

Are you happy with the new edition? Recommended? This one is on my reading list, but still looking for additional positive reviews.

Cheers,

Mathew Butler

SydOracle said...

You can compile specific objects with different levels of warning. I don't think it gets any lower level than that, or allows for any meta-data. Maybe something could be done with a database trigger, but that's not much better than comments.

ALTER PACKAGE test_warn compile plsql_warnings='ENABLE:ALL','DISABLE:07203';
Package altered.
ALTER PACKAGE test_warn compile plsql_warnings='DISABLE:ALL','ENABLE:07203';
SP2-0809: Package altered with compilation warnings

I'll do a fuller review of the book in a couple of weeks. The earlier part is fairly introductory so if you know PL/SQL it takes dedication to read it to pick up new details. The later part (security, performance, objects) is more interesting for me.

mathewbutler said...

Thanks Gary - appreciate the response.

I'll look out forthe full review of the book.

M.