plgnamex.pkg

Oracle PL/SQL procedure generator (second generator type)

CREATE OR REPLACE PACKAGE plgnamex
/*
|| Overview: The plgnamex package converts names designed for
||           a case-insensitive language/environment (PL/SQL code,
||           the data dictionary, etc.) into names that conform
||           to the Java standard:
||
||           1. Names of classes are capitalized.
||           2. Names of methods and members are not capitalized.
||           3. In all cases, discrete words within a name are
||              capitalized.
||           4. Underscores, dashes, etc. are generally not used
||              to distinguish words within a name.
||
||           Examples
||
||           ORACLE            PL/SQL
||           ----------------  -----------------
||           lineitem          LineItem
||           calc_ord_total    calcOrdTotal
||
|| How plgnamex works:
||
|| 1. Populate a seed table with atomic words...
||
|| FINISH!
*/
IS
   c_table    CONSTANT CHAR (1)    := 'T';
   c_column   CONSTANT CHAR (1)    := 'C';
   c_upper    CONSTANT CHAR (1)    := 'U';
   c_lower    CONSTANT CHAR (1)    := 'L';
   c_initcap  CONSTANT CHAR (1)    := 'I';

   TYPE wordtabtype IS TABLE OF plgnamex_word%ROWTYPE
      INDEX BY BINARY_INTEGER;

   wordtab             wordtabtype;
   word_count          PLS_INTEGER;

   PROCEDURE addwords (
      word_in IN VARCHAR2,
      action_in IN VARCHAR2 := c_initcap,
      updondup_in IN BOOLEAN := TRUE,
      delim_in IN VARCHAR2 := ','      
      );
   
   PROCEDURE addfile (
      dir_in IN VARCHAR2,
      file_in IN VARCHAR2,
      action_in IN VARCHAR2 := c_initcap,
      updondup_in IN BOOLEAN := TRUE
      );
         
   FUNCTION tojava (
      NAME_IN       IN   VARCHAR2,
      nametype_in   IN   VARCHAR2 := c_column
   )
      RETURN VARCHAR2;

   /* Perform replacements from end of string. This is called by
      tojava if the sweep from the start of the string results in
      a failure.  */
      FUNCTION tojava_bw (
      NAME_IN       IN   VARCHAR2,
      nametype_in   IN   VARCHAR2 := c_column
   )
      RETURN VARCHAR2;

   FUNCTION matchingrow (NAME_IN IN VARCHAR2)
      RETURN PLS_INTEGER;
END;
/
CREATE OR REPLACE PACKAGE BODY plgnamex
IS
   CURSOR word_cur
   IS
      SELECT *
        FROM plgnamex_word
       ORDER BY wordlen DESC;

   PROCEDURE addword (
      word_in IN VARCHAR2,
      action_in IN VARCHAR2 := c_initcap,
      updondup_in IN BOOLEAN := TRUE
      )
   IS
      v_word plgnamex_word.word%TYPE;
   BEGIN
      IF action_in = c_initcap
      THEN
         v_word := INITCAP (word_in);
      ELSIF action_in = c_upper
      THEN
         v_word := UPPER (word_in);
      ELSIF action_in = c_lower
      THEN
         v_word := LOWER (word_in);
      ELSE
         v_word := action_in;
      END IF;   
      
      INSERT INTO plgnamex_word
         (word, fword, wordlen) 
      VALUES
         (RTRIM (word_in), RTRIM (v_word), LENGTH (word_in));
   EXCEPTION
      WHEN DUP_VAL_ON_INDEX
      THEN
         IF updondup_in
         THEN
            UPDATE plgnamex_word
               SET fword = v_word
             WHERE word = word_in;
         END IF;        
   END;   

   PROCEDURE addwords (
      word_in IN VARCHAR2,
      action_in IN VARCHAR2 := c_initcap,
      updondup_in IN BOOLEAN := TRUE,
      delim_in IN VARCHAR2 := ','
      )
   IS
      startloc PLS_INTEGER := 1;
      nextloc PLS_INTEGER := 1;
      wordlen PLS_INTEGER := LENGTH (word_in);
      
      v_word plgnamex_word.word%TYPE;
   BEGIN
      IF INSTR (word_in, delim_in) = 0
      THEN
         addword (word_in, action_in, updondup_in);
      ELSE
         WHILE nextloc != 0 AND startloc < wordlen
         LOOP
            nextloc := INSTR (word_in, delim_in, startloc);
            IF nextloc = 0
            THEN
               v_word := SUBSTR (word_in, startloc);
            ELSE
               v_word := SUBSTR (word_in, startloc, nextloc - startloc);
               startloc := nextloc + 1;
            END IF;
            addword (v_word, action_in, updondup_in);
         END LOOP;
      END IF;
   END;
   
   PROCEDURE addfile (
      dir_in IN VARCHAR2,
      file_in IN VARCHAR2,
      action_in IN VARCHAR2 := c_initcap,
      updondup_in IN BOOLEAN := TRUE      
      )
   IS
      fid     UTL_FILE.file_type;
      line    VARCHAR2 (1000);
   BEGIN
      fid := UTL_FILE.fopen (dir_in, file_in, 'R');

      LOOP
         UTL_FILE.get_line (fid, line);
         addword (line, action_in, updondup_in);
      END LOOP;

      UTL_FILE.fclose (fid);
   EXCEPTION
      WHEN OTHERS
      THEN
         UTL_FILE.fclose (fid);
   END;
         

   FUNCTION tojava (
      NAME_IN       IN   VARCHAR2,
      nametype_in   IN   VARCHAR2 := c_column
   )
      RETURN VARCHAR2
   IS
      retval     VARCHAR2 (32767) := LOWER (NAME_IN);
      namelen    PLS_INTEGER      := LENGTH (NAME_IN);
      v_name     VARCHAR2 (32767) := UPPER (NAME_IN);
      indx       PLS_INTEGER;
      wordloc    PLS_INTEGER;
      startloc   PLS_INTEGER      := 1;
   BEGIN
      LOOP
         indx := wordtab.FIRST;
         wordloc := 0;

         /* Skip over underscores */
         WHILE SUBSTR (v_name, startloc, 1) = '_'
         LOOP
            startloc := startloc + 1;
         END LOOP;
         
         WHILE indx <= word_count
         LOOP
            wordloc := INSTR (v_name, wordtab (indx).word, startloc);
            EXIT WHEN wordloc = startloc;
            indx := indx + 1;
         END LOOP;

         IF wordloc != startloc
         THEN
            v_name := 
               'Unable to find match starting at ' ||
                  SUBSTR (NAME_IN, startloc);
            RAISE NO_DATA_FOUND;
         ELSE /* Found a match to replace */
            IF startloc = 1 /* keep leading word in lowercase */
            THEN
               IF nametype_in = c_table
               THEN
                  retval := INITCAP (retval);
               END IF;
            ELSE
               retval :=
                 SUBSTR (retval, 1, startloc - 1) || wordtab (indx).fword ||
                    SUBSTR (retval, startloc + wordtab (indx).wordlen);
            END IF;

            startloc := startloc + wordtab (indx).wordlen;
         END IF;

         EXIT WHEN startloc > namelen;
      END LOOP;

      RETURN retval;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN 
         RETURN NVL (tojava_bw (name_in, nametype_in), 
            '***' || name_in || ': ' || v_name);
   END;

   FUNCTION tojava_bw (
      NAME_IN       IN   VARCHAR2,
      nametype_in   IN   VARCHAR2 := c_column
   )
      RETURN VARCHAR2
   IS
      retval     VARCHAR2 (32767) := LOWER (NAME_IN);
      namelen    PLS_INTEGER      := LENGTH (NAME_IN);
      v_name     VARCHAR2 (32767) := UPPER (NAME_IN);
      v_len      PLS_INTEGER;
      indx       PLS_INTEGER;
      wordloc    PLS_INTEGER;
   BEGIN
      LOOP
         indx := wordtab.FIRST;
         wordloc := 0;

         v_name := RTRIM (v_name, '_');
         v_len := LENGTH (v_name);
         
         WHILE indx <= word_count
         LOOP
            wordloc := INSTR (
               v_name,
               wordtab (indx).word, 
               v_len - wordtab(indx).wordlen + 1);
            EXIT WHEN wordloc > 0;
            indx := indx + 1;
         END LOOP;

         IF wordloc = 0
         THEN
            RAISE NO_DATA_FOUND;
         ELSE                
            IF wordloc = 1   
            THEN
               IF nametype_in = c_table
               THEN
                  retval := INITCAP (retval);
               END IF;
               EXIT;
            ELSE
               retval :=
                 SUBSTR (retval, 1, wordloc - 1) || 
                 wordtab (indx).fword ||
                 SUBSTR (retval, wordloc + wordtab (indx).wordlen);
            END IF;

            v_name := SUBSTR (v_name, 1, LENGTH (v_name) - wordtab (indx).wordlen);
         END IF;
      END LOOP;

      RETURN retval;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN RETURN NULL;
   END;

   FUNCTION matchingrow (NAME_IN IN VARCHAR2)
      RETURN PLS_INTEGER
   IS
      retval   PLS_INTEGER;
      indx     PLS_INTEGER;
   BEGIN
      indx := wordtab.FIRST;

      WHILE indx <= word_count
      LOOP
         IF NAME_IN = wordtab (indx).word
         THEN
            retval := indx;
            indx := word_count;
         END IF;

         indx := indx + 1;
      END LOOP;

      RETURN retval;
   END;

BEGIN
   word_count := 0;

   FOR rec IN word_cur
   LOOP
      wordtab (word_cur%rowcount) := rec;
      word_count := word_count + 1;
   END LOOP;
END;
/