plgval.w73

Oracle PL/SQL procedure generator (second generator type)

CREATE OR REPLACE PACKAGE BODY PLGval
IS 

/*----------------------------------------------------------------
||                  PL/Generator from Quest Software 
||----------------------------------------------------------------
||    File: plgval.spb
||  Author: Steven Feuerstein 
||
|| This is a part of the PL/Generator Code library.
|| Copyright (C) 1997-1998 Quest Software, Inc.
|| All rights reserved.
||
|| For more information, call Quest Software at 1-800-REVEAL4
|| or check out our Web page: www.Quest Software.com
||
-----------------------------------------------------------------*/

/***************** Modification History **************************
|| Date     By     Description
|| -------- ------ -----------------------------------------------
|| 10/98    SEF    Add special exception handling for cursors
|| 2/98     SEF    Created 
******************************************************************/
   g_invalid_idents ident_tabtype;
   g_tabcol_conflicts tabcol_tabtype;

   CURSOR tab_cur (nm VARCHAR2, sch VARCHAR2) 
   IS
      SELECT object_name, owner
        FROM /* dba_ */ ALL_OBJECTS
       WHERE object_name LIKE UPPER (nm)
         AND object_type = 'TABLE'
         AND owner = UPPER (sch)
       ORDER BY owner, object_name;


/* Check identifiers for exceeding maximum length */

   FUNCTION invalid_idents (
      drv IN VARCHAR2, 
      tab IN VARCHAR2 := '%', 
      sch IN VARCHAR2 := USER,
      maxlen IN INTEGER := 30
      )
   RETURN ident_tabtype
   IS
      cur PLS_INTEGER := DBMS_SQL.OPEN_CURSOR;
      fdbk PLS_INTEGER;
      v_subst PLGadmin.dbmaxvc2;
      v_value PLGadmin.dbmaxvc2;

      curs PLGdoir.curinfo_tabtype;

      retval ident_tabtype;

      CURSOR ident_cur
      IS
         SELECT *
           FROM oir_identifier
          WHERE driver = UPPER (drv)
          ORDER BY ident_query;

      PROCEDURE bindvar (qry IN VARCHAR2, holder IN VARCHAR2, val IN VARCHAR2)
      IS
      BEGIN
         IF INSTR (qry, ':' || holder) > 0
         THEN
            DBMS_SQL.BIND_VARIABLE (cur, holder, val);
         END IF;
      END;

      PROCEDURE subst_and_check 
         (tab IN VARCHAR2, sch IN VARCHAR2, str IN OUT VARCHAR2, descrip IN VARCHAR2)
      IS
         v_row PLS_INTEGER;
      BEGIN
         PLGgen.substitute_string (drv, str, sch => sch);

         IF LENGTH (str) > maxlen
         THEN
            v_row := NVL (retval.LAST, 0) + 1;
            retval (v_row).sch := sch;
            retval (v_row).tab := tab;
            retval (v_row).subst := str;
            retval (v_row).descrip := descrip;
         END IF;
      END;

      PROCEDURE setup_and_check (
         obj IN VARCHAR2,
         sch IN VARCHAR2,
         subststr IN OUT VARCHAR2,
         descripstr IN VARCHAR2
         )
      IS
         v_row PLS_INTEGER := curs.FIRST;
      BEGIN
         /* OVERKILL: execute the check for N numbers of cursors, just IN CASE there is a nested
            reference to a cursor-related value. */

         SAVEPOINT override_plgdoir_cur_entries;
         LOOP
            IF v_row IS NOT NULL
            THEN
               /* Override normal settings. */
               PLGdoir.setdynvarchar2 (drv, 'CURNAME', '''' || curs(v_row).curname || '''');
               PLGdoir.setdynvarchar2 (drv, 'COLLIST', '''' || curs(v_row).collist || '''');
            END IF;

            subst_and_check (tab, sch, subststr, descripstr);

            v_row := curs.NEXT (v_row);

            EXIT WHEN v_row IS NULL;
         END LOOP;
         ROLLBACK TO override_plgdoir_cur_entries;
      END;

   BEGIN
      /* Load up customized cursors */
      curs := PLGdoir.curinfo (drv, tab, sch);

      FOR tab_rec IN tab_cur (tab, sch)
      LOOP
         FOR ident_rec IN ident_cur
         LOOP
            ident_rec.subst := UPPER (ident_rec.subst);

            IF ident_rec.ident_query IS NULL
            THEN
               setup_and_check (
                  tab_rec.object_name, 
                  tab_rec.owner, 
                  ident_rec.subst, 
                  ident_rec.descrip);
            ELSE
               DBMS_SQL.PARSE (cur,
                  ident_rec.ident_query,
                  DBMS_SQL.NATIVE);

               DBMS_SQL.DEFINE_COLUMN (cur, 1, v_value, 2000);

               bindvar (ident_rec.ident_query, 'OBJNAME', tab_rec.object_name);
               bindvar (ident_rec.ident_query, 'SCHEMA', tab_rec.owner);
               
               fdbk := DBMS_SQL.EXECUTE (cur);
               LOOP
                  EXIT WHEN DBMS_SQL.FETCH_ROWS (cur) = 0;
                  DBMS_SQL.COLUMN_VALUE (cur, 1, v_value);

                  v_subst :=
                     REPLACE (
                        ident_rec.subst, 
                        '[OBJNAME]', 
                        tab_rec.object_name);

                  v_subst :=
                     REPLACE (
                        v_subst, 
                        '[' || UPPER (ident_rec.ident_list) || ']', 
                        v_value);

                  setup_and_check (tab_rec.object_name, tab_rec.owner, v_subst, ident_rec.descrip);
               END LOOP;
            END IF;
         END LOOP;
      END LOOP;
      DBMS_SQL.CLOSE_CURSOR (cur);
      RETURN retval;
   END;

   PROCEDURE load_invalid_idents (
      drv IN VARCHAR2, 
      tab IN VARCHAR2 := '%', 
      sch IN VARCHAR2 := USER,
      maxlen IN INTEGER := 30
      )
   IS
   BEGIN
      g_invalid_idents := invalid_idents (drv, tab, sch, maxlen);
   END;
   
   PROCEDURE show_invalid_idents (
      drv IN VARCHAR2, 
      tab IN VARCHAR2 := '%', 
      sch IN VARCHAR2 := USER,
      maxlen IN INTEGER := 30)
   IS
      currsch PLGadmin.identifier;
      currtab PLGadmin.identifier;

      v_tab ident_tabtype;

      FUNCTION newobj (sch IN VARCHAR2, tab IN VARCHAR2) RETURN BOOLEAN
      IS
         retval BOOLEAN := currsch IS NULL or currtab IS NULL;
      BEGIN
         IF NOT retval
         THEN
            retval := currsch != sch OR currtab != tab;
         END IF;
         IF retval
         THEN
            currsch := sch;
            currtab := tab;
         END IF;
         RETURN retval;
      END;

      PROCEDURE disp_header (sch IN VARCHAR2, tab IN VARCHAR2)
      IS
      BEGIN
         DBMS_OUTPUT.PUT_LINE (RPAD ('-', 75, '-'));
         DBMS_OUTPUT.PUT_LINE ('Identifiers with > ' || maxlen || ' chars in ' || sch || '.' || tab);
         DBMS_OUTPUT.PUT_LINE (RPAD ('-', 75, '-'));
      END;

   BEGIN
      v_tab := invalid_idents (drv, tab, sch, maxlen);

      IF v_tab.COUNT > 0
      THEN
         FOR rowind IN v_tab.FIRST .. v_tab.LAST
         LOOP
            IF newobj (v_tab(rowind).sch, v_tab(rowind).tab)
            THEN
               disp_header (v_tab(rowind).sch, v_tab(rowind).tab);
            END IF;
            DBMS_OUTPUT.PUT_LINE (v_tab(rowind).subst);
            DBMS_OUTPUT.PUT_LINE ('   (' || v_tab(rowind).descrip || ')');
         END LOOP;

         DBMS_OUTPUT.PUT_LINE ('');
         DBMS_OUTPUT.PUT_LINE (RPAD ('*', 75, '*'));
         DBMS_OUTPUT.PUT_LINE ('REGARDING GENERATED IDENTIFIERS WITH INVALID NAMES:');
         DBMS_OUTPUT.PUT_LINE ('');
         DBMS_OUTPUT.PUT_LINE ('   Some of the invalid identifiers listed above may NOT actually');
         DBMS_OUTPUT.PUT_LINE ('   appear in your generated code and therefore not cause compile ');
         DBMS_OUTPUT.PUT_LINE ('   errors. If any identifiers were flagged as invalid, however,');
         DBMS_OUTPUT.PUT_LINE ('   you SHOULD consider changing your naming conventions to avoid');
         DBMS_OUTPUT.PUT_LINE ('   potential problems.');
         DBMS_OUTPUT.PUT_LINE ('');
         DBMS_OUTPUT.PUT_LINE (RPAD ('*', 75, '*'));
      END IF;
   END;

   FUNCTION num_invalid_idents RETURN INTEGER
   IS
   BEGIN
      RETURN g_invalid_idents.COUNT;
   END;

   PROCEDURE get_invalid_ident (
      nth IN INTEGER,
      sch OUT VARCHAR2,
      tab OUT VARCHAR2,
      ident OUT VARCHAR2,
      descr OUT VARCHAR2
      )
   IS
   BEGIN
      PLGerr.assert (
         nth BETWEEN 1 AND num_invalid_idents,
         'PLGval: the index is out of range for the Invalid Identifier List.');
    
      sch   := g_invalid_idents(nth).sch;
      tab   := g_invalid_idents(nth).tab;
      ident := g_invalid_idents(nth).subst;
      descr := g_invalid_idents(nth).descrip;
   END;

/* Validate table-column name conflict */   

   FUNCTION tabcol_conflicts (
      drv IN VARCHAR2, 
      tab IN VARCHAR2 := '%', 
      sch IN VARCHAR2 := USER,
      maxlen IN INTEGER := 30
      )
   RETURN tabcol_tabtype
   IS
      v_row PLS_INTEGER;
      retval tabcol_tabtype;

   BEGIN
      FOR tab_rec IN tab_cur (tab, sch)
      LOOP
         IF PLGcols.iscolumn (tab_rec.object_name, tab_rec.object_name, tab_rec.owner)
         THEN
            IF PLGdoir.alias_prefix (drv,
                  tab_rec.object_name,
                  'FLD', /* A better away around this hard-coding? */
                  tab_rec.owner) 
               ||
               PLGdoir.alias_suffix (drv,
                  tab_rec.object_name,
                  'FLD', /* A better away around this hard-coding? */
                  tab_rec.owner) 
               IS NULL
            THEN
               v_row := NVL (retval.LAST, 0) + 1;
               retval (v_row).sch := tab_rec.owner;
               retval (v_row).tab := tab_rec.object_name;
            END IF;
         END IF;
      END LOOP;

      RETURN retval;
   END;

   PROCEDURE load_tabcol_conflicts (
      drv IN VARCHAR2, 
      tab IN VARCHAR2 := '%', 
      sch IN VARCHAR2 := USER,
      maxlen IN INTEGER := 30
      )
   IS
   BEGIN
      g_tabcol_conflicts := tabcol_conflicts (drv, tab, sch);
   END;
   
   PROCEDURE show_tabcol_conflicts (
      drv IN VARCHAR2, 
      tab IN VARCHAR2 := '%', 
      sch IN VARCHAR2 := USER
      )
   IS
      v_tab tabcol_tabtype;

      PROCEDURE disp_header
      IS
      BEGIN
         DBMS_OUTPUT.PUT_LINE (RPAD ('-', 75, '-'));
         DBMS_OUTPUT.PUT_LINE ('Tables with Columns of Same Name and No Field Prefix');
         DBMS_OUTPUT.PUT_LINE (RPAD ('-', 75, '-'));
      END;

   BEGIN
      v_tab := tabcol_conflicts (drv, tab, sch);

      IF v_tab.COUNT > 0
      THEN
         disp_header;
         FOR rowind IN v_tab.FIRST .. v_tab.LAST
         LOOP
            DBMS_OUTPUT.PUT_LINE (v_tab(rowind).sch || '.' || v_tab(rowind).tab);
         END LOOP;
      END IF;
   END;

   FUNCTION num_tabcol_conflicts RETURN INTEGER
   IS
   BEGIN
      RETURN g_tabcol_conflicts.COUNT;
   END;

   PROCEDURE get_tabcol_conflicts (
      nth IN INTEGER,
      sch OUT VARCHAR2,
      tab OUT VARCHAR2
      )
   IS
   BEGIN
      PLGerr.assert (
         nth BETWEEN 1 AND num_tabcol_conflicts,
         'PLGval: the index is out of range for the Table-Column Conflicts List.');
    
      sch   := g_tabcol_conflicts(nth).sch;
      tab   := g_tabcol_conflicts(nth).tab;
   END;
   
END PLGval;
/