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.
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
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; /