plgdoir.spb

Oracle PL/SQL procedure generator (second generator type)

      END IF;
   END;

   FUNCTION tabinfo (
      drv IN VARCHAR2,
      tab IN VARCHAR2,
      infotype_in IN PLG_doir.infotype%TYPE,
      seq_in IN PLG_doir.infoseq%TYPE := 1,
      sch IN VARCHAR2 := NULL,
      user_only IN BOOLEAN := FALSE
      )
      RETURN PLG_doir.info%TYPE
   IS
      rec       PLG_doir%ROWTYPE;
   BEGIN
      gettabrec (drv, tab, infotype_in, seq_in, sch, user_only, rec);
      RETURN rec.info;
   END;

   PROCEDURE getcolrec (
      drv IN VARCHAR2,
      tab IN VARCHAR2,
      col IN VARCHAR2,
      infotype_in IN PLG_doir.infotype%TYPE,
      seq_in IN PLG_doir.infoseq%TYPE := 1,
      sch IN VARCHAR2 := NULL,
      user_only IN BOOLEAN := FALSE,
      tabinfo_out OUT PLG_doir%ROWTYPE,
      restrict_objid IN VARCHAR2 := NULL
      )
   IS
      v_user_only                   PLGadmin.identifier := PLGbool.stg (user_only);
      v_drv     PLGadmin.identifier := UPPER (drv);
      tryitagain                    BOOLEAN;

      retval    PLG_doir%ROWTYPE;
   BEGIN
      OPEN colinfo_cur (drv, sch, tab, col, infotype_in, seq_in, restrict_objid, v_user_only);
      FETCH colinfo_cur INTO tabinfo_out;
      tryitagain := colinfo_cur%notfound;
      CLOSE colinfo_cur;

      IF tryitagain
      THEN
         v_drv := just_like_drv (drv);

         IF v_drv IS NOT NULL
         THEN
		      OPEN colinfo_cur (v_drv, sch, tab, col, infotype_in, seq_in, restrict_objid, v_user_only);
		      FETCH colinfo_cur INTO tabinfo_out;
		      CLOSE colinfo_cur;
         END IF;
      END IF;
   END;

   FUNCTION colval (
      drv IN VARCHAR2,
      tab IN VARCHAR2,
      col IN VARCHAR2,
      infotype_in IN PLG_doir.infotype%TYPE,
      seq_in IN PLG_doir.infoseq%TYPE := 1,
      sch IN VARCHAR2 := NULL
      )
      RETURN PLG_doir.info%TYPE
   IS
      rec       PLG_doir%ROWTYPE;
   BEGIN
      getcolrec (drv, tab, col, infotype_in, seq_in, sch, FALSE, rec);
      RETURN rec.info;
   END;

   FUNCTION isactive (
      drv IN VARCHAR2,
      tab IN VARCHAR2,
      infotype_in IN PLG_doir.infotype%TYPE,
      sch IN VARCHAR2 := NULL
      )
      RETURN BOOLEAN
   IS
      v_truefalse                   PLG_doir.info%TYPE;
   BEGIN
      v_truefalse := tabinfo (drv, tab, infotype_in, sch => sch);

      IF v_truefalse IS NULL
      THEN
         RETURN NULL;
      ELSE
         RETURN PLGbool.valnow (v_truefalse, c_truefalse);
      END IF;
   END;

   FUNCTION intbool (intger IN INTEGER)
      RETURN BOOLEAN
   IS
   BEGIN
      IF NVL (intger, 0) = 0
      THEN
         RETURN FALSE;
      ELSE
         RETURN TRUE;
      END IF;
   END;

   FUNCTION boolint (bool IN BOOLEAN)
      RETURN INTEGER
   IS
   BEGIN
      IF bool
      THEN
         RETURN 1;
      ELSE
         RETURN 0;
      END IF;
   END;

   FUNCTION descindex (
      drv IN VARCHAR2,
      tab IN VARCHAR2,
      sch IN VARCHAR2 := NULL
      )
      RETURN VARCHAR2
   IS

      /* Returns string in format INDEX.FUNCTION.DATATYPE */
      rec       PLG_doir%ROWTYPE;
   BEGIN
      gettabrec (drv, tab, c_pkydesc, sch => sch, tabinfo_out => rec);

      /*
      || Have to get attrname and attrtype for this record
      || and concatenate with periods if I am going to get
      || this to work.
      */
      RETURN  (rec.info || '.' || rec.attrname || '.' || rec.attrtype);
   END;


   /* Same for fkydesccols */
   FUNCTION pkycol (
      drv IN VARCHAR2,
      tab IN VARCHAR2,
      pos IN INTEGER,
      sch IN VARCHAR2 := NULL
      )
      RETURN PLG_doir.info%TYPE
   IS
      rec       PLG_doir%ROWTYPE;
   BEGIN
      gettabrec (drv, tab, c_pkycol, pos, sch, tabinfo_out => rec);
      RETURN rec.info;
   END;

   FUNCTION loadtype (
      drv IN VARCHAR2,
      tab IN VARCHAR2,
      sch IN VARCHAR2 := NULL
      )
      RETURN VARCHAR2
   IS
   BEGIN
      RETURN tabinfo (drv, tab, c_preload, sch => sch);
   END;

   FUNCTION loadany (drv IN VARCHAR2, tab IN VARCHAR2, sch IN VARCHAR2 := NULL)
      RETURN BOOLEAN
   IS
      retval    BOOLEAN;
   BEGIN
      RETURN    loadall (drv, tab, sch)
             OR loadincr (drv, tab, sch);
   END;

   FUNCTION loadall (drv IN VARCHAR2, tab IN VARCHAR2, sch IN VARCHAR2 := NULL)
      RETURN BOOLEAN
   IS
   BEGIN
      RETURN NVL (tabinfo (drv, tab, c_preload, sch => sch) = c_loadall, FALSE);
   END;

   FUNCTION loadincr (
      drv IN VARCHAR2,
      tab IN VARCHAR2,
      sch IN VARCHAR2 := NULL
      )
      RETURN BOOLEAN
   IS
   BEGIN
      RETURN NVL (
                tabinfo (drv, tab, c_preload, sch => sch) = c_loadincr,
                FALSE
             );
   END;


   /* Retrieve Exception Handling Information */
   PROCEDURE unpack_excinfo (
      excval IN VARCHAR2,
      exctype OUT PLGadmin.identifier,
      raisemech OUT PLGadmin.dbmaxvc2,
      handlemech OUT PLGadmin.dbmaxvc2,
      setcontext OUT BOOLEAN,
      successval OUT VARCHAR2,
      failureval OUT VARCHAR2
      )
   IS
   BEGIN
      exctype :=
         PLGstr.betwn (excval, c_delim, c_delim, 0, inclusive => FALSE);
      raisemech :=
         PLGstr.betwn (excval, c_delim, c_delim, 1, inclusive => FALSE);
      handlemech :=
         PLGstr.betwn (excval, c_delim, c_delim, 2, inclusive => FALSE);
      setcontext :=
         PLGbool.valnow (
            PLGstr.betwn (excval, c_delim, c_delim, 3, inclusive => FALSE),
            c_truefalse
         );
      successval :=
         PLGstr.betwn (excval, c_delim, c_delim, 4, inclusive => FALSE);
      failureval :=
         PLGstr.betwn (
            excval,
            c_delim,
            c_delim,
            5,
            inclusive => FALSE,
            gotoend => TRUE
         );
   END;

   PROCEDURE unpack_excinfo (excval IN VARCHAR2, rec IN OUT exc_rectype)
   IS
   BEGIN
      unpack_excinfo (
         excval,
         rec.exctype,
         rec.raisemech,
         rec.handlemech,
         rec.setcontext,
         rec.successval,
         rec.failureval
      );
   END;

   FUNCTION excinfo (drv IN VARCHAR2, tab IN VARCHAR2, sch IN VARCHAR2 := NULL)
      RETURN exc_rectype
   IS
      v_excval PLGadmin.dbmaxvc2;
      retval   exc_rectype;
   BEGIN
      v_excval := tabinfo (drv, tab, c_exchdlg, sch => sch);
      unpack_excinfo (v_excval, retval);
      RETURN retval;
   END;

   FUNCTION PLVexc (drv IN VARCHAR2, tab IN VARCHAR2, sch IN VARCHAR2 := NULL)
      RETURN BOOLEAN
   IS
      rec exc_rectype;
   BEGIN
      rec := excinfo (drv, tab, sch);
      RETURN NVL (rec.exctype = c_plvexc, FALSE);
   END;

   FUNCTION PLVexcn (drv IN VARCHAR2, tab IN VARCHAR2, sch IN VARCHAR2 := NULL)
      RETURN INTEGER
   IS
   BEGIN
      RETURN boolint (PLVexc (drv, tab, sch));
   END;

   FUNCTION psexc (drv IN VARCHAR2, tab IN VARCHAR2, sch IN VARCHAR2 := NULL)
      RETURN BOOLEAN
   IS
      rec exc_rectype;
   BEGIN
      rec := excinfo (drv, tab, sch);
      RETURN NVL (rec.exctype = c_psexc, FALSE);
   END;

   FUNCTION retcd (drv IN VARCHAR2, tab IN VARCHAR2, sch IN VARCHAR2 := NULL)
      RETURN BOOLEAN
   IS
      rec exc_rectype;
   BEGIN
      rec := excinfo (drv, tab, sch);
      RETURN NVL (rec.exctype = c_retcd, FALSE);
   END;

   FUNCTION raisemech (
      drv IN VARCHAR2,
      tab IN VARCHAR2,
      sch IN VARCHAR2 := NULL
      )
      RETURN VARCHAR2
   IS
      rec exc_rectype;
   BEGIN
      rec := excinfo (drv, tab, sch);
      RETURN rec.raisemech;
   END;

   FUNCTION handlemech (
      drv IN VARCHAR2,
      tab IN VARCHAR2,
      sch IN VARCHAR2 := NULL
      )
      RETURN VARCHAR2
   IS
      rec exc_rectype;
   BEGIN
      rec := excinfo (drv, tab, sch);
      RETURN rec.handlemech;
   END;

   FUNCTION failureval (
      drv IN VARCHAR2,
      tab IN VARCHAR2,
      sch IN VARCHAR2 := NULL
      )
      RETURN VARCHAR2
   IS
      rec exc_rectype;
   BEGIN
      rec := excinfo (drv, tab, sch);

      IF rec.failureval IS NULL
      THEN
         IF rec.exctype = c_retcd
         THEN
            rec.failureval := 1;
         ELSIF rec.exctype = c_psexc
         THEN
            rec.failureval := 'SQLCODE';
         ELSIF rec.exctype = c_plvexc
         THEN
            rec.failureval := 'SQLCODE';
         END IF;
      END IF;

      RETURN rec.failureval;
   END;

   FUNCTION failuremsg (
      drv IN VARCHAR2,
      tab IN VARCHAR2,
      sch IN VARCHAR2 := NULL
      )
      RETURN VARCHAR2
   IS
      rec exc_rectype;
   BEGIN
      rec := excinfo (drv, tab, sch);

      IF rec.failuremsg IS NULL
      THEN
         rec.failuremsg := 'SQLERRM';
      END IF;

      RETURN rec.failuremsg;
   END;

   FUNCTION successval (
      drv IN VARCHAR2,
      tab IN VARCHAR2,
      sch IN VARCHAR2 := NULL
      )
      RETURN VARCHAR2
   IS
      rec exc_rectype;
   BEGIN
      rec := excinfo (drv, tab, sch);

      IF rec.successval IS NULL
      THEN
         IF rec.exctype = c_retcd
         THEN
            rec.successval := 0;
         ELSIF rec.exctype = c_psexc
         THEN
            rec.successval := 'SQLCODE';
         ELSIF rec.exctype = c_plvexc
         THEN
            rec.successval := 'SQLCODE';
         END IF;
      END IF;

      RETURN rec.successval;
   END;

   FUNCTION setting_context (
      drv IN VARCHAR2,
      tab IN VARCHAR2,
      sch IN VARCHAR2 := NULL
      )
      RETURN BOOLEAN
   IS
      rec exc_rectype;
   BEGIN
      rec := excinfo (drv, tab, sch);
      RETURN NVL (rec.setcontext, FALSE);
   END;


   /* Primary key information */
   FUNCTION seqname (drv IN VARCHAR2, tab IN VARCHAR2, sch IN VARCHAR2 := NULL)
      RETURN VARCHAR2
   IS
   BEGIN
      RETURN tabinfo (drv, tab, c_sequence, sch => sch);
   END;

   FUNCTION pkyproc (drv IN VARCHAR2, tab IN VARCHAR2, sch IN VARCHAR2 := NULL)
      RETURN VARCHAR2
   IS
   BEGIN
      RETURN tabinfo (drv, tab, c_pkygenproc, sch => sch);
   END;

   FUNCTION pkyfunc (drv IN VARCHAR2, tab IN VARCHAR2, sch IN VARCHAR2 := NULL)
      RETURN VARCHAR2
   IS
   BEGIN
      RETURN tabinfo (drv, tab, c_pkygenfunc, sch => sch);
   END;

   FUNCTION genpky (drv IN VARCHAR2, tab IN VARCHAR2, sch IN VARCHAR2 := NULL)
      RETURN BOOLEAN
   IS
   BEGIN
      RETURN  ( seqname (drv, tab, sch) IS NOT NULL
             OR pkyproc (drv, tab, sch) IS NOT NULL
             OR pkyfunc (drv, tab, sch) IS NOT NULL);
   END;

   FUNCTION genpkyonly (
      drv IN VARCHAR2,
      tab IN VARCHAR2,
      sch IN VARCHAR2 := NULL
      )
      RETURN BOOLEAN
   IS
   BEGIN
      RETURN isactive (drv, tab, c_genpkyonly, sch => sch);
   END;

   FUNCTION company (drv IN VARCHAR2, tab IN VARCHAR2, sch IN VARCHAR2 := NULL)
      RETURN VARCHAR2
   IS
   BEGIN
      RETURN tabinfo (drv, tab, c_company, sch => sch);
   END;

   FUNCTION usingxmn (
      drv IN VARCHAR2,
      tab IN VARCHAR2,
      sch IN VARCHAR2 := NULL
      )
      RETURN BOOLEAN
   IS
   BEGIN
      RETURN isactive (drv, tab, c_usingxmn, sch => sch);
   END;

   FUNCTION appprefix (
      drv IN VARCHAR2,
      tab IN VARCHAR2,
      sch IN VARCHAR2 := NULL
      )
      RETURN VARCHAR2
   IS
   BEGIN
      RETURN tabinfo (drv, tab, c_appprefix, sch => sch);
   END;

   /* Column level queries */
   FUNCTION ispkycol (
      drv IN VARCHAR2,
      tab IN VARCHAR2,
      col IN VARCHAR2,
      sch IN VARCHAR2 := NULL
      )
      RETURN BOOLEAN
   IS
      v_col     PLGadmin.identifier := UPPER (col);
      v_pkycol  PLGadmin.identifier;
      v_pos     PLS_INTEGER := 1;
      retval    BOOLEAN := FALSE;
   BEGIN

      /* Loop through PKY columns and look for a match. */
      LOOP
         v_pkycol := pkycol (drv, tab, v_pos, sch);
         EXIT WHEN v_pkycol IS NULL;
         retval := v_pkycol = v_col;
         EXIT WHEN retval;
         v_pos := v_pos + 1;
      END LOOP;

      RETURN retval;
   END;

   FUNCTION ispkycoln (
      drv IN VARCHAR2,
      tab IN VARCHAR2,
      col IN VARCHAR2,
      sch IN VARCHAR2 := NULL
      )
      RETURN INTEGER
   IS
   BEGIN
      RETURN boolint (ispkycol (drv, tab, col, sch));
   END;

   FUNCTION havehidden (
      drv IN VARCHAR2,
      tab IN VARCHAR2,
      sch IN VARCHAR2 := NULL
      )