plgdoir.spb

Oracle PL/SQL procedure generator (second generator type)

      RETURN BOOLEAN
   IS
      rec       PLG_doir%ROWTYPE;
   BEGIN
      getcolrec (drv, tab, '%', c_hidden, 1, sch, FALSE, rec);
      RETURN rec.objid IS NOT NULL;
   END;

   FUNCTION ishidden (
      drv IN VARCHAR2,
      tab IN VARCHAR2,
      col IN VARCHAR2,
      sch IN VARCHAR2 := NULL
      )
      RETURN BOOLEAN
   IS
   BEGIN
      RETURN NVL (
                colval (drv, tab, col, c_hidden, 1, sch => sch) = c_true,
                FALSE
             );
   END;

   FUNCTION isupdcol (
      drv IN VARCHAR2,
      tab IN VARCHAR2,
      col IN VARCHAR2,
      sch IN VARCHAR2 := NULL
      )
      RETURN BOOLEAN
   IS
   BEGIN
      RETURN NVL (
                colval (drv, tab, col, c_updcol, 1, sch => sch) = c_true,
                FALSE
             );
   END;

   FUNCTION colfunc (
      drv IN VARCHAR2,
      tab IN VARCHAR2,
      col IN VARCHAR2,
      sch IN VARCHAR2 := NULL
      )
      RETURN PLG_doir.info%TYPE
   IS
   BEGIN
      RETURN colval (drv, tab, col, c_colfunc, 1, sch => sch);
   END;


   /* Driver foundation elements or not? */
   PROCEDURE drvfoundation
   IS
   BEGIN
      g_drvfoundation := TRUE;
   END;

   PROCEDURE nodrvfoundation
   IS
   BEGIN
      g_drvfoundation := FALSE;
   END;

   FUNCTION isdrvfoundation
      RETURN BOOLEAN
   IS
   BEGIN
      RETURN g_drvfoundation;
   END;

   /* Set information values */
   PROCEDURE ins (
      drv IN VARCHAR2,
      sch IN VARCHAR2,
      objname_in IN VARCHAR2,
      objtype_in IN VARCHAR2,
      attrname_in IN VARCHAR2,
      attrtype_in IN VARCHAR2,
      infotype_in IN PLG_doir.infotype%TYPE,
      seq_in IN PLG_doir.infoseq%TYPE,
      info_in IN PLG_doir.info%TYPE,
      uc_attrname IN BOOLEAN := TRUE,
      doc_in IN PLG_doir.doc%TYPE := '*IGNORE',
      upd_attrtype IN BOOLEAN := FALSE
      )
   IS
      v_driver  PLG_doir.driver%TYPE := UPPER (drv);
      v_owner   PLG_doir.owner%TYPE
         := UPPER (NVL (sch, g_schema));
      v_objname PLG_doir.objname%TYPE
         := UPPER (NVL (objname_in, c_global));
      v_objtype PLG_doir.objtype%TYPE
         := UPPER (objtype_in);
      v_attrname                    PLG_doir.attrname%TYPE
         := PLGadmin.ifelse (uc_attrname, UPPER (attrname_in), attrname_in);
      v_attrtype                    PLG_doir.attrtype%TYPE
         := UPPER (attrtype_in);
      v_infotype                    PLG_doir.infotype%TYPE
         := UPPER (infotype_in);
      v_infoseq PLG_doir.infoseq%TYPE := UPPER (seq_in);
      v_doc     PLG_doir.doc%TYPE
         := PLGadmin.ifelse (doc_in = '*IGNORE', NULL, doc_in);
      v_upd_attrtype                VARCHAR2(3)
         := PLGadmin.ifelse (upd_attrtype, c_true, c_false);
      v_objid   PLG_doir.objid%TYPE;

      CURSOR upd_cur
      IS
         SELECT objid
           FROM PLG_doir
          WHERE driver = v_driver
            AND owner = v_owner
            AND objname = v_objname
            AND objtype = v_objtype
            AND attrname = v_attrname
            AND  ( attrtype = v_attrtype
                OR v_upd_attrtype = c_true)
            AND infotype = v_infotype
            AND infoseq = v_infoseq
            FOR UPDATE NOWAIT
          ORDER BY objid desc;
   BEGIN
      /* Does the row already exist? */
      OPEN upd_cur;
      FETCH upd_cur INTO v_objid;

      IF v_objid > 0 AND NOT isdrvfoundation
         -- 99.2 Avoid writing over/UPDATE of user global settings.
      /*
          ((NOT isdrvfoundation AND v_objid > 0) OR
           (isdrvfoundation AND v_objid < 0))
      */
      THEN
         UPDATE PLG_doir
            SET info = info_in,
                doc = v_doc,
                attrtype = v_attrtype,
                changed_on = SYSDATE,
                changed_by = g_schema
          WHERE objid = v_objid;
      ELSIF    v_objid < 0
            OR v_objid IS NULL
            OR (v_objid > 0 AND isdrvfoundation)
      THEN

         /* Do an insert. If negative, that means we are creating an override
            on the global setting, so the only change will be the new POSITIVE sequence
            number. If driver-foundation is turned ON, then we will insert with a
            negative value. */
         IF     isdrvfoundation
            -- AND v_objid IS NULL
         THEN
            SELECT -1 * PLG_doir_seq.nextval
              INTO v_objid
              FROM dual;
         ELSE
            SELECT PLG_doir_seq.nextval
              INTO v_objid
              FROM dual;
         END IF;

         INSERT INTO PLG_doir (
             driver,
             objid,
             owner,
             objname,
             objtype,
             attrname,
             attrtype,
             infotype,
             infoseq,
             info,
             doc,
             created_on,
             created_by,
             changed_on,
             changed_by
          )
              VALUES (
                 v_driver,
                 v_objid,
                 v_owner,
                 v_objname,
                 v_objtype,
                 v_attrname,
                 v_attrtype,
                 v_infotype,
                 v_infoseq,
                 info_in,
                 v_doc,
                 SYSDATE,
                 g_schema,
                 SYSDATE,
                 g_schema
              );
      END IF;
   EXCEPTION
      WHEN OTHERS
      THEN
         IF SQLCODE = -54 /* Record locked in SELECT FOR UPDATE */
         THEN
            NULL; /* Just don't perform the update. */
         ELSE
	         PLGerr.raise (
	            PLGerr.insert_failure,
	            SQLERRM ||
	            '-' ||
	            v_driver ||
	            '-' ||
	            v_objid ||
	            '-' ||
	            v_owner ||
	            '-' ||
	            v_objname ||
	            '-' ||
	            v_objtype ||
	            '-' ||
	            v_attrname ||
	            '-' ||
	            v_attrtype ||
	            '-' ||
	            v_infotype ||
	            '-' ||
	            v_infoseq ||
	            '-' ||
	            info_in ||
	            '-' ||
	            v_doc
	         );
         END IF;
   END;

   PROCEDURE settabinfo (
      drv IN VARCHAR2,
      tab IN VARCHAR2,
      infotype_in IN PLG_doir.infotype%TYPE,
      info_in IN PLG_doir.info%TYPE,
      seq_in IN PLG_doir.infoseq%TYPE := 1,
      sch IN VARCHAR2 := NULL,
      verify_data IN BOOLEAN := FALSE,
      objtype_in IN VARCHAR2 := c_table,
      aname IN VARCHAR2 := NULL,
      atype IN VARCHAR2 := NULL
      )
   IS
      v_val     PLGadmin.identifier;
      v_info    PLG_doir.info%TYPE := info_in;
      v_num     PLS_INTEGER;
      perform_insert                BOOLEAN := TRUE;

      FUNCTION index_name (drv IN VARCHAR2, tab IN VARCHAR2, sch IN VARCHAR2)
         RETURN VARCHAR2
      IS
         retval    PLGadmin.identifier;
         indtab    PLGinds.ind_tabtype;
         coltab    PLGinds.indcol_tabtype;
         colrec    PLGinds.indcol_rectype;
         v_colmatch                    PLGadmin.identifier;
      BEGIN
         PLGinds.fortab (tab, indtab, coltab, sch, PLGinds.c_unique_nopky);


         /* If null, then get first unique index which is NOT
            the primary key. */
         IF     info_in IS NULL
            AND indtab.COUNT > 0
         THEN
            retval := indtab (indtab.first).index_name;
         ELSIF indtab.COUNT > 0
         THEN

            /* Is it an index? If not, check for matching first column. */
            v_info := UPPER (v_info);

            FOR rowind IN indtab.first .. indtab.last
            LOOP
               IF v_info = indtab (rowind).index_name
               THEN
                  retval := v_info;
                  EXIT;

               /* Bad boy! Unconditional exit! */
               ELSIF v_colmatch IS NULL

               /* Check for column match. */
               THEN
                  colrec := PLGinds.nthcol (coltab, rowind, 1);

                  IF v_info = colrec.column_name
                  THEN

 /* Don't use column name. Cannot identify later... */
 v_colmatch := indtab (rowind).index_name;

                  /* colrec.column_name; */
                  END IF;
               END IF;
            END LOOP;

            IF     retval IS NULL
               AND v_colmatch IS NOT NULL
            THEN
               retval := v_colmatch;
            END IF;
         END IF;

         RETURN retval;
      END;
   BEGIN
      PLGpriv.assert (tab, sch, PLGpriv.modobj);

      IF verify_data
      THEN
         IF infotype_in = c_sequence
         THEN
            OPEN seq_cur (schnn (sch), info_in);
            FETCH seq_cur INTO v_val;
            perform_insert := seq_cur%found;
            CLOSE seq_cur;
         ELSIF infotype_in = c_pkycol
         THEN
            OPEN col_cur (schnn (sch), tab, info_in);
            FETCH col_cur INTO v_val;
            perform_insert := col_cur%found;
            CLOSE col_cur;
         ELSIF infotype_in = c_pkydesc
         THEN
            v_info := index_name (drv, tab, sch);
            perform_insert := v_info IS NOT NULL;
         ELSIF     infotype_in = c_preload
               AND v_info IN (c_loadall, c_loadincr)
         THEN
            v_num := PLGpky.numcols (tab, sch);
            perform_insert := v_num = 1;

            IF NOT perform_insert
            THEN
               perform_insert :=     pkycol (drv, tab, 1, sch) IS NOT NULL
             AND pkycol (drv, tab, 2, sch) IS NULL;
            END IF;
         END IF;
      END IF;

      IF perform_insert
      THEN
         ins (
            drv,
            sch,
            tab,
            objtype_in,
            NVL (aname, c_global),
            NVL (atype, c_global),
            infotype_in,
            seq_in,
            v_info
         );
      END IF;
   END;

   PROCEDURE multitabset (
      drv IN VARCHAR2,
      tab IN VARCHAR2,
      const IN PLG_doir.infotype%TYPE,
      val IN PLG_doir.info%TYPE,
      pos IN PLG_doir.infoseq%TYPE := 1,
      sch IN VARCHAR2,
      ver IN BOOLEAN := FALSE,
      convert_val IN BOOLEAN := TRUE,
      remove_conflicts IN VARCHAR2 := NULL,
      objtype_in IN VARCHAR2 := c_table,
      aname IN VARCHAR2 := NULL,
      atype IN VARCHAR2 := NULL,
      remove_only IN BOOLEAN := FALSE
      )
   IS
      v_tab     PLGadmin.identifier := UPPER (tab);
      seglist   PLGiseg.seglist_rectype;

      PROCEDURE cleanup (tab IN VARCHAR2)
      IS
      BEGIN
         IF remove_conflicts IS NOT NULL
         THEN
            FOR itemind IN 1 .. PLGiseg.numitems (seglist)
            LOOP
               del (drv, tab, PLGiseg.item (seglist, itemind), sch => sch);
            END LOOP;
         END IF;
      END;
   BEGIN
      IF convert_val
      THEN
         v_tab := stdname (tab, val, TRUE);
      END IF;

      IF remove_conflicts IS NOT NULL
      THEN
         PLGiseg.loadstg (remove_conflicts, numsegs => 1, seglist => seglist);
      END IF;

      IF v_tab = c_global
      THEN
         ins (
            drv,
            sch,
            objname_in => c_global,
            objtype_in => c_table,
            attrname_in => c_global,
            attrtype_in => c_global,
            infotype_in => const,
            seq_in => 1,
            info_in => val
         );

      ELSIF    v_tab != UPPER (tab)
            OR INSTR (tab, '%') > 0
      THEN
         FOR rec IN tab_cur (tab, sch)
         LOOP
            cleanup (rec.object_name);

            IF NOT remove_only
            THEN
               settabinfo (
                  drv,
                  rec.object_name,
                  const,
                  PLGadmin.ifelse (
 convert_val,
 stdname (rec.object_name, val, FALSE),
 val
                  ),
                  pos,
                  rec.owner,
                  ver,
                  objtype_in => objtype_in,
                  aname => aname,
                  atype => atype
               );
            END IF;
         END LOOP;
      ELSE
         cleanup (tab);

         IF NOT remove_only
         THEN
            settabinfo (
               drv,
               tab,
               const,
               PLGadmin.ifelse (convert_val, UPPER (val), val),
               pos,
               sch => sch,
               verify_data => ver,
               aname => aname,
               atype => atype
            );
         END IF;
      END IF;
   END;

   PROCEDURE reggen (
      drv IN VARCHAR2,
      tab IN VARCHAR2,
      sch IN VARCHAR2,
      target_schema IN VARCHAR2 := NULL
      )
   IS
   BEGIN
      ins (
         drv,
         sch => sch,
         objname_in => tab,
         objtype_in => c_reggen,
         attrname_in => drv,
         attrtype_in => c_global,
         infotype_in => c_global,
         seq_in => 1,
         info_in => target_schema
      );
   END;

   PROCEDURE setappprefix (
      drv IN VARCHAR2,
      tab IN VARCHAR2,
      str IN VARCHAR2,
      sch IN VARCHAR2 := NULL
      )
   IS
   BEGIN
      multitabset (
         drv,
         tab,
         c_appprefix,
         str,
         1,
         sch,
         TRUE,
         convert_val => FALSE
      );
   END;

   PROCEDURE setdescindex (
      drv IN VARCHAR2,
      tab IN VARCHAR2,
      unique_index IN VARCHAR2 := NULL,
      indexfunc IN VARCHAR2 := NULL,
      func_datatype IN VARCHAR2 := 'VARCHAR2',
      sch IN VARCHAR2 := NULL
      )

   /* Unique_index argument can be name of unique index or name of leading column.
      IF NULL, then uses the first unique index which is NOT the PK. */
   IS
   BEGIN
      multitabset (
         drv,
         tab,