Advanced Oracle PL/SQL Programming with Packages

3.4. Improving the User Interface

A couple of weeks go by before I encounter another need for twice. Then I need to call it for lower-UPPER conversion on a company name. So I put this line in my program:

v_full_name := twice (comp_rec.short_name, 'lu');

but when I execute the program, the full name is not in lower-UPPER format. It is all uppercased and, as I trace my way back to the data, that is just how the company short name is stored in the database. It doesn't seem to be doing any conversion at all.

Frustrated, I decide to head back to the source code. Of course, I can't remember where I stored the source code on disk. It was just a dinky little program. And it's generally not too easy to view the source code as it exists in the USER_SOURCE data dictionary view. Fortunately, I have built a PL/Vision package named PLVvu (more about this in Chapter 14) to view the code and so I execute that program to refresh my memory:

SQL> exec PLVvu.code('twice'); ----------------------------------------------------------- Code for FUNCTION TWICE ----------------------------------------------------------- Line# Source ----------------------------------------------------------- 1 FUNCTION twice 2 (string_in IN VARCHAR2, action_in IN VARCHAR2) 3 RETURN VARCHAR2 4 IS 5 BEGIN 6 IF action_in = 'UL' 7 THEN 8 RETURN (UPPER (string_in) || LOWER (string_in)); 9 ELSIF action_in = 'LU' 10 THEN 11 RETURN (LOWER (string_in) || UPPER (string_in)); 12 ELSIF action_in = 'N' 13 THEN 14 RETURN string_in ...