plgobj.spb

Oracle PL/SQL procedure generator (second generator type)

CREATE OR REPLACE PACKAGE BODY PLGobj
IS
/*----------------------------------------------------------------
||                  PL/Vision Professional 
||----------------------------------------------------------------
||    File: PLGobj.spb
||  Author: Steven Feuerstein
||
|| This is a part of the PL/Vision Professional Code library.
|| Copyright (C) 1996-99 RevealNet, Inc.
|| All rights reserved.
||
|| For more information, call RevealNet at 1-800-REVEAL4
|| or check out our Web page: www.revealnet.com
||
-----------------------------------------------------------------*/
   c_pkg CONSTANT PLGadmin.identifier := 'PLGobj';

   /* Standard trc and log flags */
   v_trc BOOLEAN := FALSE;
   v_log BOOLEAN := FALSE;

   /* Constants and variables for "Include code?" */
   c_include CONSTANT CHAR(1) := 'Y';
   c_noinclude CONSTANT CHAR(1) := 'N';
   v_inclall BOOLEAN := FALSE;
   v_sql_inclall CHAR(1) := c_noinclude;

   CURSOR obj_cur (nm VARCHAR2, tp VARCHAR2, sch VARCHAR2) 
   IS
      SELECT *
        FROM /* sys.dba_ */ ALL_OBJECTS
       WHERE object_name LIKE nm
         AND object_type LIKE tp
         AND owner LIKE sch
         AND (v_sql_inclall = c_include OR 
              object_type IN
                ('PACKAGE', 'PACKAGE BODY', 'PROCEDURE',
                 'FUNCTION', 'TRIGGER')) 
       ORDER BY owner,
          DECODE (object_type, 
             'PACKAGE', 1, 
             'PACKAGE BODY', 2,
             'PROCEDURE', 3,
             'FUNCTION', 4,
             'TRIGGER', 5, 
             6),
          object_name;

   /*--------------- Private Modules -----------------*/

   FUNCTION cased (name_in IN VARCHAR2) RETURN VARCHAR2
   IS
   BEGIN
      RETURN UPPER (name_in);
   END;

   PROCEDURE tokenize 
      (name1_inout IN OUT VARCHAR2,
       name2_out OUT VARCHAR2,
       char_in IN VARCHAR2 := '.')
   IS
      col_loc INTEGER;
   BEGIN
      col_loc := INSTR (name1_inout, char_in);
      IF col_loc = 1
      THEN
         name2_out := NULL;
         name1_inout := SUBSTR (name1_inout, 2);

      ELSIF col_loc > 1
      THEN
         name2_out := 
            UPPER (SUBSTR (name1_inout, 1, col_loc-1));
         name1_inout := 
            UPPER (SUBSTR (name1_inout, col_loc+1));
      ELSE
         name2_out := NULL;
      END IF;
   END;

   PROCEDURE convert_type (type_inout IN OUT VARCHAR2) IS
   BEGIN
      type_inout := UPPER (type_inout);

      IF type_inout IN (c_pkg_spec, c_package, 'PS', 'SPEC', 'SPECIFICATION')
       THEN type_inout := c_package;
      ELSIF type_inout IN (c_pkg_body, 'BODY', 'PB', c_package_body)
       THEN type_inout := c_package_body;
      ELSIF type_inout = c_entire_pkg THEN type_inout := 'PACKAGE%';
      ELSIF type_inout IN (c_clu, c_cluster) THEN type_inout := c_cluster;
      ELSIF type_inout IN (c_func, c_function, 'FUNC') THEN type_inout := c_function;
      ELSIF type_inout IN (c_ind, c_index) THEN type_inout := c_index;
      ELSIF type_inout IN (c_proc, c_procedure, 'PROC') THEN type_inout := c_procedure;
      ELSIF type_inout IN (c_seq, c_sequence) THEN type_inout := c_sequence;
      ELSIF type_inout IN (c_syn, c_synonym) THEN type_inout := c_synonym;
      ELSIF type_inout IN (c_tab, c_table) THEN type_inout := c_table;
      ELSIF type_inout IN (c_trig, c_trigger, 'TRIG') THEN type_inout := c_trigger;
      ELSIF type_inout IN (c_vu, c_view) THEN type_inout := c_view;

      ELSIF type_inout IN ('ALL', '%') OR type_inout IS NULL THEN type_inout := '%';
      
      ELSE
         type_inout := NULL;
      END IF;
   END;

   FUNCTION convtype (type_in IN VARCHAR2) RETURN VARCHAR2
   IS   
      retval PLGadmin.identifier := type_in;
   BEGIN
      convert_type (retval);
      RETURN retval;
   END;

   FUNCTION objfromnum (num_in IN INTEGER) RETURN VARCHAR2
   IS
      retval PLGadmin.identifier;
   BEGIN
      If num_in = 5 
      THEN 
         retval := c_synonym;

      ELSIF num_in = 7 
      THEN
         retval := c_procedure;

      ELSIF num_in = 8 
      THEN
         retval := c_function;

      ELSIF num_in = 9 
      THEN
         retval := c_package;

      ELSIF num_in = 11 
      THEN
         retval := c_package_body;

      ELSIF num_in = 12 
      THEN
         retval := c_trigger;

      END IF;
      RETURN retval;
   END;

   FUNCTION convcase (nm IN VARCHAR2) RETURN VARCHAR2
   IS   /* 99.2.8 Just return the name */
   BEGIN
      RETURN nm;
      /*
      -- If double quotes, then do not upper-case. 
      IF SUBSTR (nm, 1, 1) != '"'      
      THEN
         RETURN UPPER (nm);
      ELSE
        RETURN LTRIM (RTRIM (nm, '"'));
      END IF;
      */
   END;
         
/*--------------- Public Modules -----------------*/

/* Include Code Toggle */

   PROCEDURE inclall
   IS
   BEGIN
      v_inclall := TRUE;
      v_sql_inclall := c_include;
   END inclall;

   PROCEDURE noinclall
   IS
   BEGIN
      v_inclall := FALSE;
      v_sql_inclall := c_noinclude;
   END noinclall;

   FUNCTION including_all RETURN BOOLEAN
   IS
      retval BOOLEAN := v_inclall;
   BEGIN
      RETURN retval;
   END including_all;

   FUNCTION iscode (type_in IN VARCHAR2) RETURN BOOLEAN
   IS
      retval BOOLEAN := FALSE;
   BEGIN
      RETURN (type_in IN 
         (c_package, c_package_body, c_procedure, c_function));
   END iscode;

   PROCEDURE convert
      (name_inout IN OUT VARCHAR2, 
       type_inout IN OUT VARCHAR2,
       schema_inout IN OUT VARCHAR2)
   IS
      v_name PLGadmin.identifier := name_inout;
      col_loc INTEGER;
      v_schema PLGadmin.identifier;
      v_name2 PLGadmin.identifier;
      v_dblink PLGadmin.identifier;
      v_type NUMBER;
      objnum NUMBER;
   BEGIN
      IF v_name IS NULL
      THEN
         RAISE no_name_specified;
      END IF;

      IF type_inout IS NULL
      THEN
         tokenize (v_name, type_inout, ':');
      END IF;

      IF INSTR (v_name, '%') > 0 OR
         INSTR (v_name, '.') > 0 
      THEN
         name_inout := v_name;
         tokenize (name_inout, schema_inout);
         IF schema_inout IS NULL
         THEN
            schema_inout := USER;
         END IF;

         IF INSTR (type_inout, '%') = 0
         THEN
            convert_type (type_inout);

         ELSIF type_inout IS NULL /* 10/98 */
         THEN
            convert (name_inout, type_inout, schema_inout);
         END IF; 
      ELSE
         BEGIN
            DBMS_UTILITY.NAME_RESOLVE
               (cased (v_name), 1, 
                v_schema,
                v_name,
                v_name2,
                v_dblink,
                v_type,
                objnum);

         EXCEPTION
            WHEN OTHERS
            THEN     
               objnum := NULL;
         END;

         IF objnum IS NULL
         THEN
            /* No object found */
            schema_inout := NULL;
            name_inout := NULL;
            type_inout := NULL;
         ELSE
            schema_inout := v_schema;
            name_inout := NVL (v_name, v_name2);

            IF INSTR (type_inout, '%') > 0
            THEN
               NULL;
            ELSIF v_type = 9 AND type_inout IS NOT NULL
            THEN
               /* Package, so user may have specified
                  either specification or body. 
               */
               convert_type (type_inout);
            ELSE 
               type_inout := objfromnum (v_type);
            END IF;
         END IF;
      END IF;
   END convert;

   FUNCTION convspec (spec IN VARCHAR2, resolve_synonym IN BOOLEAN := FALSE) 
      RETURN spec_type
   /* Format of spec is T:O.N where T is the type and is optional (only
      really needed for package spec/body ambiguity, O is owner and
      optional with the default being USER and N is the name. All of
      these can also be wildcarded. */
   IS
      v_typeloc INTEGER := INSTR (spec, ':');
      v_dotloc INTEGER := INSTR (spec, '.');
      retval spec_type;
      
      CURSOR obj_cur (nm VARCHAR2, tp VARCHAR2, sch VARCHAR2) 
      IS
         SELECT object_name, object_type, owner
           FROM /* sys.dba_ */ ALL_OBJECTS
          WHERE (object_name LIKE nm OR object_name LIKE UPPER (nm))
            AND object_type LIKE tp
            AND owner LIKE sch
            AND OWNER != 'PUBLIC'
          ORDER BY owner, object_name;
      obj_rec obj_cur%ROWTYPE;
   BEGIN
      IF v_typeloc > 0
      THEN
         retval.type := SUBSTR (spec, 1, v_typeloc-1);
         PLGobj.convert_type (retval.type);
      END IF;

      IF v_dotloc > 0
      THEN
         retval.owner := UPPER (PLGstr.betwn (spec, v_typeloc+1, v_dotloc-1));
         retval.name := convcase (SUBSTR (spec, v_dotloc+1));
      ELSE
         retval.name := convcase (SUBSTR (spec, v_typeloc+1));
         
         /* First assume current USER, then wildcard if that fails. */
         OPEN obj_cur (retval.name, NVL (retval.type, '%'), USER);
         FETCH obj_cur INTO obj_rec; 
         IF obj_cur%NOTFOUND
         THEN
            CLOSE obj_cur;
            OPEN obj_cur (retval.name, NVL (retval.type, '%'), '%');
            FETCH obj_cur INTO obj_rec;
         END IF;
         CLOSE obj_cur;         
         retval.owner := NVL (obj_rec.owner, USER);
      END IF;

      /* If type is not set and there are no wildcards % in name and owner,
         look up the type. */
      IF retval.type IS NULL AND INSTR (retval.owner||retval.name, '%') = 0
      THEN
         OPEN obj_cur (retval.name, '%', retval.owner);
         FETCH obj_cur INTO obj_rec;
         CLOSE obj_cur;         
         retval.type := obj_rec.object_type;
      END IF;

      --PLGobj.convert_type (retval.type);

      /* 11/97 */
      IF resolve_synonym AND retval.type = c_synonym
      THEN
         DECLARE
            CURSOR syn_cur 
            IS
               SELECT table_owner, table_name FROM /* dba_ */ ALL_SYNONYMS
                WHERE owner = retval.owner
                  AND synonym_name = retval.name;
            syn_rec syn_cur%ROWTYPE;
         BEGIN
            OPEN syn_cur; 
            FETCH syn_cur INTO syn_rec;
            CLOSE syn_cur; 
            /*
            DBMS_OUTPUT.PUT_LINE ('synonym table owner ' || syn_rec.table_owner);
            DBMS_OUTPUT.PUT_LINE ('synonym table ' || syn_rec.table_name);
            */
            retval := convspec (syn_rec.table_owner || '.' || syn_rec.table_name);
            /*
            retval.owner := syn_rec.table_owner;
            retval.name := syn_rec.table_name;
            */
         END;
      END IF;

      RETURN retval;
   END;   

   FUNCTION lastobj 
      (status_in IN VARCHAR2 := '%', owner_in IN VARCHAR2 := USER) 
   RETURN /* sys.dba_ */ ALL_OBJECTS%ROWTYPE
   IS
       CURSOR last_cur
       IS
          SELECT *
            FROM /* sys.dba_ */ ALL_OBJECTS
           WHERE owner LIKE UPPER (owner_in)
             AND last_ddl_time =
              (SELECT MAX (last_ddl_time)
                 FROM /* sys.dba_ */ ALL_OBJECTS
                WHERE owner LIKE UPPER (owner_in)
                  AND status LIKE UPPER (status_in));  
       curr_rec /* sys.dba_ */ ALL_OBJECTS%ROWTYPE;  
   BEGIN
       OPEN last_cur;
       FETCH last_cur INTO curr_rec;
       RETURN curr_rec;
   END;

   FUNCTION last_modified (spec IN VARCHAR2, resolve_synonym IN BOOLEAN := FALSE)
   RETURN DATE
   IS
       CURSOR last_cur (o VARCHAR2, n VARCHAR2, t VARCHAR2)
       IS
          SELECT last_ddl_time
            FROM /* sys.dba_ */ ALL_OBJECTS
           WHERE owner = o
             AND object_name = n
             AND object_type = t;

       v_spec spec_type;
       retval DATE;
   BEGIN
      v_spec := convspec (spec, resolve_synonym);
      OPEN last_cur (v_spec.owner, v_spec.name, v_spec.type);
      FETCH last_cur INTO retval;
      CLOSE last_cur;
      RETURN retval;
   END;

   FUNCTION last_modified (
      sch IN VARCHAR2, tab IN VARCHAR2, resolve_synonym IN BOOLEAN := FALSE)
   RETURN DATE
   IS
   BEGIN
      RETURN last_modified (sch || '.' || tab, resolve_synonym);
   END;

   FUNCTION definer (spec IN VARCHAR2, resolve_synonym IN BOOLEAN := FALSE)
   RETURN VARCHAR2
   IS
       v_spec spec_type;
   BEGIN
   -- 05/2001: returns USER if object does not exist!
      v_spec := convspec (spec, resolve_synonym);
      RETURN v_spec.owner;
   END;

END PLGobj;
/