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.


2 comments:
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
Thanks Gary - appreciate the response.
I'll look out forthe full review of the book.
M.
Post a Comment