plgtype.spb

Oracle PL/SQL procedure generator (second generator type)

/* Formatted on 2001/05/25 15:44 (Quest Software Formatter v4.4.0) */
CREATE OR REPLACE PACKAGE BODY PLGtype
IS
   
/*----------------------------------------------------------------
||                  PL/Vision Professional
||----------------------------------------------------------------
||    File: plgtype.spb
||  Author: Steven Feuerstein
||
|| This is a part of the PL/Generator Code library.
|| Copyright (C) 1996-1999 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
||
-----------------------------------------------------------------*/
   /* Private Elements */

   FUNCTION matchon (val_in IN VARCHAR2, val1 IN VARCHAR2, val2 IN VARCHAR2)
      RETURN BOOLEAN
   IS
   BEGIN
      RETURN (UPPER (val_in) IN (val1, val2));
   END;

   
/* Public Elements */

   FUNCTION stg (val_in IN BOOLEAN)
      RETURN VARCHAR2
   IS
   BEGIN
      RETURN c_boolean;
   END;

   FUNCTION stg (val_in IN DATE)
      RETURN VARCHAR2
   IS
   BEGIN
      RETURN c_date;
   END;

   FUNCTION stg (val_in IN NUMBER)
      RETURN VARCHAR2
   IS
   BEGIN
      RETURN c_number;
   END;

   FUNCTION stg (val_in IN VARCHAR2, len_in IN INTEGER := NULL)
      RETURN VARCHAR2
   IS
   BEGIN
      RETURN (   c_varchar2
              || PLGadmin.ifelse (
                    len_in IS NULL,
                    NULL,
                       '('
                    || NVL (len_in, PLGadmin.vcmax_len)
                    || ')'
                 )
             );
   END;

   FUNCTION fulltype (abbrev_in IN VARCHAR2)
      RETURN VARCHAR2
   IS
      v_abbrev   VARCHAR2 (100) := UPPER (abbrev_in);
      v_len      INTEGER        := TO_CHAR (PLGadmin.vcmax_len);
      retval     VARCHAR2 (100) := NULL;
   BEGIN
      IF v_abbrev = c_b
      THEN
         retval := c_boolean;
      ELSIF v_abbrev = c_d
      THEN
         retval := c_date;
      ELSIF v_abbrev = c_n
      THEN
         retval := c_number;
      ELSIF v_abbrev LIKE    c_v
                          || '%'
      THEN
         IF v_abbrev != c_v
         THEN
            v_len := SUBSTR (v_abbrev, 2);
         END IF;

         retval :=    c_varchar2
                   || '('
                   || v_len
                   || ')';
      END IF;

      RETURN retval;
   END;

   FUNCTION isboolean (stg_in IN VARCHAR2)
      RETURN BOOLEAN
   IS
   BEGIN
      RETURN matchon (UPPER (stg_in), c_boolean, c_b);
   END;

   FUNCTION isdate (stg_in IN VARCHAR2)
      RETURN BOOLEAN
   IS
      v_stg   PLGadmin.identifier := UPPER (stg_in);
   BEGIN
      RETURN (   v_stg IN (c_date, c_d)
              OR SUBSTR (v_stg, 1, 1) = c_d
             );
   END;

   FUNCTION isnumber (stg_in IN VARCHAR2)
      RETURN BOOLEAN
   IS
      v_paren   INTEGER             := INSTR (stg_in, '(');
      v_stg     PLGadmin.identifier := UPPER (stg_in);
   BEGIN
      v_stg := PLGadmin.ifelse (
                  v_paren = 0,
                  v_stg,
                  PLGstr.betwn (v_stg, 1,   v_paren
                                          - 1)
               );
      RETURN (   v_stg IN (c_number,
                           c_n,
                           'FLOAT',
                           'DECIMAL',
                           'INTEGER',
                           'POSITIVE',
                           'BINARY_INTEGER'
                          )
              OR SUBSTR (v_stg, 1, 1) = c_n
             );
   END;

   FUNCTION ismslabel (stg_in IN VARCHAR2)
      RETURN BOOLEAN
   IS
   BEGIN
      RETURN matchon (UPPER (stg_in), c_mslabel, c_m);
   END;

   FUNCTION israw (stg_in IN VARCHAR2)
      RETURN BOOLEAN
   IS
      v_stg   PLGadmin.identifier := UPPER (stg_in);
   BEGIN
      RETURN (UPPER (stg_in) IN (c_raw, c_r, 'LONG RAW'));
   END;

   FUNCTION ischar (stg_in IN VARCHAR2)
      RETURN BOOLEAN
   IS
      v_paren   INTEGER             := INSTR (stg_in, '(');
      v_stg     PLGadmin.identifier := UPPER (stg_in);
   BEGIN
      v_stg := PLGadmin.ifelse (
                  v_paren = 0,
                  v_stg,
                  PLGstr.betwn (v_stg, 1,   v_paren
                                          - 1)
               );
      RETURN (   v_stg IN (c_char, c_c)
              OR SUBSTR (v_stg, 1, 1) = c_c
             );
   END;

   FUNCTION isvarchar2 (stg_in IN VARCHAR2)
      RETURN BOOLEAN
   IS
      v_paren   INTEGER             := INSTR (stg_in, '(');
      v_stg     PLGadmin.identifier := UPPER (stg_in);
   BEGIN
      v_stg := PLGadmin.ifelse (
                  v_paren = 0,
                  v_stg,
                  PLGstr.betwn (v_stg, 1,   v_paren
                                          - 1)
               );
      RETURN (   v_stg IN (c_varchar2, c_v)
              OR SUBSTR (v_stg, 1, 1) = c_v
             );
   END;

   FUNCTION isstring (stg_in IN VARCHAR2)
      RETURN BOOLEAN
   /* V50 or VARCHAR2(50) or CHAR(45) */
   IS
      v_stg   PLGadmin.identifier := UPPER (stg_in);
   BEGIN
      RETURN (   isvarchar2 (stg_in)
              OR ischar (stg_in)
             );
   END;

   FUNCTION len (stg_in IN VARCHAR2)
      RETURN INTEGER
   /* Valid formats:
      v                      n
      varchar2               number
      char                   nN
      vN                     number(N)
      varchar2(n)            d             date
      char(n)                dN            dateN
   */
   IS
      v_stg     VARCHAR2 (100) := UPPER (stg_in);
      v_len     VARCHAR2 (100);
      v_left    INTEGER;
      v_right   INTEGER;
      retval    INTEGER        := NULL;

      FUNCTION ilen (stg IN VARCHAR2, st IN INTEGER, nd IN INTEGER := NULL)
         RETURN INTEGER
      IS
         ind      INTEGER;
         retval   INTEGER;
      BEGIN
         ind :=   INSTR (stg, ',')
                - 1;
         RETURN (TO_NUMBER (
                    PLGstr.betwn (
                       stg,
                       st,
                       PLGadmin.ifelse (ind = -1, nd, LEAST (ind, nd))
                    )
                 )
                );
      END;
   BEGIN
      IF    v_stg IN (c_v, c_c, c_n)
         OR v_stg LIKE    c_varchar2
                       || '(%'
         OR v_stg LIKE    c_char
                       || '(%'
         OR v_stg LIKE    c_number
                       || '(%'
         OR v_stg LIKE    c_date
                       || '%'
      THEN
         v_left := INSTR (v_stg, '(');

         IF v_left > 0
         THEN
            v_right := INSTR (stg_in, ')');

            IF v_right > v_left
            THEN
               retval := ilen (stg_in,   v_left
                                       + 1,   v_right
                                            - 1);
            END IF;
         END IF;
      ELSIF SUBSTR (v_stg, 1, 1) IN (c_v, c_n, c_c, c_d)
      THEN
         retval := ilen (v_stg, 2, LENGTH (v_stg));
      END IF;

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

   FUNCTION name (stg_in IN VARCHAR2)
      RETURN VARCHAR2
   /* Returns just declaration datatype name */
   IS
      retval   PLGadmin.identifier;
   BEGIN
      IF isboolean (stg_in)
      THEN
         retval := c_boolean;
      ELSIF isdate (stg_in)
      THEN
         retval := c_date;
      ELSIF ismslabel (stg_in)
      THEN
         retval := c_mslabel;
      ELSIF israw (stg_in)
      THEN
         retval := c_raw;
      ELSIF isnumber (stg_in)
      THEN
         retval := c_number;
      ELSIF isvarchar2 (stg_in)
      THEN
         retval := c_varchar2;
      ELSIF ischar (stg_in)
      THEN
         retval := c_char;
      END IF;

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

   FUNCTION decl (type_in IN VARCHAR2)
      RETURN VARCHAR2
   /* Returns valid datatype declaration. */
   IS
      type_part   PLGadmin.identifier := UPPER (type_in);
      size_part   PLGadmin.identifier;
      v_start     INTEGER;
   BEGIN
      v_start := INSTR (type_part, '(');

      IF v_start = 0
      THEN
         /* Just a number? */
         v_start :=
                NVL (LENGTH (TRANSLATE (type_part, '*0123456789,', '*')), 0)
              + 1;

         IF v_start > 1
         THEN
            size_part :=    '('
                         || SUBSTR (type_part, v_start)
                         || ')';
            type_part := SUBSTR (type_part, 1,   v_start
                                               - 1);
         END IF;
      ELSE
         size_part := SUBSTR (type_in, v_start);
         type_part := SUBSTR (type_part, 1,   v_start
                                            - 1);
      END IF;

      IF type_part = c_b
      THEN
         type_part := c_boolean;
      ELSIF type_part = c_d
      THEN
         type_part := c_date;
      ELSIF type_part = c_n
      THEN
         type_part := c_number;
      ELSIF type_part = c_v
      THEN
         type_part := c_varchar2;

         IF size_part = '()'
         THEN
            size_part :=    '('
                         || TO_CHAR (PLGadmin.vcmax_len)
                         || ')';
         END IF;
      END IF;

      RETURN    type_part
             || REPLACE (size_part, '()', NULL);
   END;

   /* 2000.2.9 Returns TRUE if the column has a datatype new to 8, 8i or 9i */
   FUNCTION is_adv_type (type_in IN VARCHAR2)
      RETURN BOOLEAN
   IS
      l_type   VARCHAR2 (100) := UPPER (type_in);
   BEGIN
      RETURN (type_in ('VARRAY', 'TABLE', 'LOB', 'CLOB', 'BLOB', 'NCLOB', 'BFILE'));
   END;

   FUNCTION is_long_type (type_in IN VARCHAR2)
      RETURN BOOLEAN
   IS
   BEGIN
      RETURN (UPPER (type_in) = 'LONG');
   END;
END PLGtype;
/