Comment from Steven Feuerstein

In the Oracle Magazine of januari/februari 2005 Steven Feuerstein wrote an excellent article about Refactoring for PL/SQL Developers. In this article he uses technologies known in the Java community: Refactoring of code, and reapplies these in PL/SQL. Both languages are programming languages so it is perfectly OK to interchange the goodies and best-practises of both languages to write better code (in any language).

Enter the blog for my comment and the comment I got back from Steven.

My comment:
Excellent article.
I think there remains one piece of hard-coding that could be refactored: the
declaration of the variables checkline and againstline (VARCHAR2(32767).
Because the length is dependend on the maximum linesize in utl_file, it
should be refactored according your list of opportunities for improvement.
To make it completely independend you could declare a subtype (i.e.
max_linesize_type varchar(32767) in package utl_file_constants and call this
subtype in function eqfiles (declare checkline
utl_file_constants.max_linesize_type; …).

Comment from Steven Feuerstein:
You are absolutely right, Harm. This is a perfect scenario for such a subtype. I will update my package as follows:

CREATE OR REPLACE PACKAGE utl_file_constants
IS
   c_read_only      CONSTANT VARCHAR2 (1) := 'R';
   c_write_only     CONSTANT VARCHAR2 (1) := 'W';
   c_append         CONSTANT VARCHAR2 (1) := 'A';
   c_min_linesize   CONSTANT PLS_INTEGER  := 1;
   c_def_linesize   CONSTANT PLS_INTEGER  := 1024;
   c_max_linesize   CONSTANT PLS_INTEGER  := 32767;

   SUBTYPE max_linesize_t IS VARCHAR2 (32767);
   SUBTYPE def_linesize_t IS VARCHAR2 (1024);

END utl_file_constants;
/

Warm regards,
Steven Feuerstein
www.stevenfeuerstein.com
www.qnxo.com