Wednesday, December 07, 2005

PL/SQL warnings in 10GR2

It's been over a month since my last post. I'm going to have to try to improve on that.
I've got several ideas, but not much free time to flesh them out.

I have been playing around with a sandbox install of XE, my first exposure to 10GR2.
The first thing I noticed was that GROUP BY often (mostly ?) does NOT sort in the GROUPed order.

Yes, I know that without an ORDER BY it was never guaranteed, but it is a wake-up call, and I can start getting into better habits now.

Often though, while the order isn't necessary, it can be useful. For example, when processing clients are using DBMS_APPLICATION_INFO to record the 'current' one, when they are ordered you get a feel for how far through the job it is.
Now I'll have to think about whether it is worth doing the additional sort to keep that feeling.

Now with XE, I've turn the PLSQL compiler warnings on by default.
Hopefully something else to get me into good habits.

However, I've found one where the warning is incorrect. Anyone know any others ?


USER@xe> alter session set plsql_warnings='ENABLE:ALL';

Session altered.

USER@xe> create or replace FUNCTION tf_to_char (p_boolean IN BOOLEAN) RETURN VARCHAR2 IS
2 BEGIN
3 --Converts a boolean (true/false/null) to a string which can be used for output
4 IF p_boolean THEN
5 RETURN 'TRUE';
6 ELSIF NOT p_boolean THEN
7 RETURN 'FALSE';
8 END IF;
9 return 'NULL';
10 END tf_to_char;
11 .
USER@xe> /

SP2-0806: Function created with compilation warnings

USER@xe> show errors
Errors for FUNCTION TF_TO_CHAR:

LINE/COL ERROR
-------- -----------------------------------------------------------------
6/9 PLW-06002: Unreachable code
USER@xe>

USER@xe> declare
2 v_test boolean;
3 begin
4 dbms_output.put_line(tf_to_char(v_test));
5 v_test:=true;
6 dbms_output.put_line(tf_to_char(v_test));
7 v_test := false;
8 dbms_output.put_line(tf_to_char(v_test));
9 end;
10 .
USER@xe> /
NULL
TRUE
FALSE

PL/SQL procedure successfully completed.

No comments: