plgbool.spb

Oracle PL/SQL procedure generator (second generator type)

CREATE OR REPLACE PACKAGE BODY PLGbool
IS
/*----------------------------------------------------------------
||                  PL/Vision Professional 
||----------------------------------------------------------------
||    File: PLGbool.spb
||  Author: Steven Feuerstein
||
|| This is a part of the PL/Vision Professional Code library.
|| Copyright (C) 1996-99 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
||
-----------------------------------------------------------------*/
   c_pkg CONSTANT PLGadmin.identifier := 'plgbool';

   val_list VARCHAR2(1000);
   g_tval val_list%TYPE := PLGadmin.surrounded (c_tval, c_delim);
   g_fval val_list%TYPE := PLGadmin.surrounded (c_fval, c_delim);
   g_nval val_list%TYPE := PLGadmin.surrounded (c_nval, c_delim);
   g_tvalnum INTEGER := c_tvalnum;
   g_fvalnum INTEGER := c_fvalnum;
   
   PROCEDURE sett (val IN VARCHAR2 := c_tval) IS
   BEGIN
      g_tval := PLGadmin.surrounded (NVL (val, c_tval), c_delim);
   END;
   
   PROCEDURE sett (val IN INTEGER := c_tvalnum) IS
   BEGIN
      g_tvalnum := NVL (val, c_tvalnum);
   END;
   
   FUNCTION tval RETURN VARCHAR2
   IS BEGIN RETURN PLGstr.betwn (g_tval, 1, INSTR (g_tval, c_delim, 1, 2), FALSE); END;

   FUNCTION tvalnum RETURN INTEGER
   IS BEGIN RETURN g_tvalnum; END;

   PROCEDURE setf (val IN VARCHAR2 := c_fval) IS
   BEGIN  
      g_fval := PLGadmin.surrounded (NVL (val, c_fval), c_delim); 
   END;

   PROCEDURE setf (val IN INTEGER := c_fvalnum) IS
   BEGIN 
      g_fvalnum := NVL (val, c_fvalnum); 
   END;
   
   FUNCTION fval RETURN VARCHAR2
   IS BEGIN RETURN PLGstr.betwn (g_fval, 1, INSTR (g_fval, c_delim, 1, 2), FALSE); END;

   FUNCTION fvalnum RETURN INTEGER
   IS BEGIN RETURN g_fvalnum; END;

   PROCEDURE setn (val IN VARCHAR2 := c_nval) IS
   BEGIN 
      g_nval := PLGadmin.surrounded (NVL (val, c_nval), c_delim);  
   END;

   FUNCTION nval RETURN VARCHAR2
   IS BEGIN RETURN PLGstr.betwn (g_nval, 1, INSTR (g_nval, c_delim, 1, 2), FALSE); END;
    
   FUNCTION stg (bool IN BOOLEAN, tfvals IN VARCHAR2 := NULL) RETURN VARCHAR2
   IS
      v_tval PLGadmin.identifier := tval;     
      v_fval PLGadmin.identifier := fval;
      v_nval PLGadmin.identifier := nval;
      loc INTEGER;     
      retval PLGadmin.identifier;
   BEGIN
      IF tfvals IS NOT NULL
      THEN
         loc := INSTR (tfvals, c_delim);
         v_tval := SUBSTR (tfvals, 1, loc-1);
         v_fval := SUBSTR (tfvals, loc+1);
      END IF;

      IF bool IS NULL THEN retval := v_nval;    
      ELSIF bool THEN retval := v_tval;    
      ELSIF NOT bool THEN retval := v_fval;    
      END IF; 
      RETURN retval;
   END;   

   FUNCTION to_char (bool IN BOOLEAN) RETURN VARCHAR2
   IS
   BEGIN
      RETURN stg (bool);
   END;   

   FUNCTION literalstg (bool IN BOOLEAN) RETURN VARCHAR2
   IS
   BEGIN
      IF bool IS NULL THEN RETURN NULL;    
      ELSIF bool THEN RETURN 'TRUE';
      ELSIF NOT bool THEN RETURN 'FALSE';
      END IF;
   END;   

   FUNCTION num (bool IN BOOLEAN) RETURN INTEGER IS
   BEGIN
      IF bool IS NULL 
      THEN 
         RETURN NULL;    
         
      ELSIF bool
      THEN 
         RETURN tvalnum;
         
      ELSIF NOT bool 
      THEN 
         RETURN fvalnum;
         
      ELSE 
         RETURN NULL;
      END IF;
   END;   
       
   FUNCTION valnow (stg IN VARCHAR2, tfvals IN VARCHAR2) RETURN BOOLEAN
   IS
      v_tval val_list%TYPE;     
      v_fval val_list%TYPE;
      loc INTEGER;     
      v_val PLGadmin.identifier := PLGadmin.surrounded (RTRIM (stg), c_delim);
   BEGIN
      IF tfvals IS NULL
      THEN
         v_tval := g_tval;
         v_fval := g_fval;
      ELSE
         loc := INSTR (tfvals, c_delim);
         v_tval := c_delim || SUBSTR (tfvals, 1, loc);
         v_fval := SUBSTR (tfvals, loc) || c_delim;
      END IF;

      IF INSTR (v_tval, v_val) > 0
      THEN 
         RETURN TRUE;
         
      ELSIF INSTR (v_fval, v_val) > 0
      THEN
         RETURN FALSE; 
         
      ELSIF INSTR (g_nval, v_val) > 0 OR stg IS NULL 
      THEN 
         RETURN NULL; 
         
      ELSE
         RETURN NULL; 
      END IF;
   END;   
       
   FUNCTION val (stg IN VARCHAR2) RETURN BOOLEAN
   IS
      v_val PLGadmin.identifier := PLGadmin.surrounded (stg, c_delim);
   BEGIN
      IF INSTR (g_tval, v_val) > 0
      THEN 
         RETURN TRUE;
         
      ELSIF INSTR (g_fval, v_val) > 0
      THEN
         RETURN FALSE; 
         
      ELSIF INSTR (g_nval, v_val) > 0 OR stg IS NULL 
      THEN 
         RETURN NULL; 
         
      ELSE
         RETURN NULL; 
      END IF;
   END;   
       
   FUNCTION val (num IN INTEGER) RETURN BOOLEAN
   IS
   BEGIN
      IF num IS NULL
      THEN 
         RETURN NULL;
         
      ELSIF num = tvalnum
      THEN
         RETURN TRUE;  
         
      ELSIF num = fvalnum
      THEN
         RETURN FALSE;  
         
      ELSE
         RETURN NULL; 
      END IF;
   END;   

   FUNCTION val (val1 IN BOOLEAN, oper IN VARCHAR2) RETURN BOOLEAN
   IS
      retval BOOLEAN := FALSE;
   BEGIN
     IF val1 IS NULL and oper = c_isnull
     THEN
        retval := TRUE;
     ELSIF val1 IS NOT NULL AND  oper = c_isnotnull
     THEN
        retval := TRUE;
     ELSE
        retval := FALSE;
     END IF;
     RETURN retval;
   END;

   FUNCTION val (val1 IN DATE, oper IN VARCHAR2) RETURN BOOLEAN
   IS
      retval BOOLEAN := FALSE;
   BEGIN
     IF val1 IS NULL and oper = c_isnull
     THEN
        retval := TRUE;
     ELSIF val1 IS NOT NULL AND  oper = c_isnotnull
     THEN
        retval := TRUE;
     ELSE
        retval := FALSE;
     END IF;
     RETURN retval;
   END;

   FUNCTION val (val1 IN NUMBER, oper IN VARCHAR2) RETURN BOOLEAN
   IS
      retval BOOLEAN := FALSE;
   BEGIN
      IF val1 IS NULL and oper = c_isnull
     THEN
        retval := TRUE;
     ELSIF val1 IS NOT NULL AND  oper = c_isnotnull
     THEN
        retval := TRUE;
     ELSE
        retval := FALSE;
     END IF;
     RETURN retval;
   END;

   FUNCTION val (val1 IN VARCHAR2, oper IN VARCHAR2) RETURN BOOLEAN
   IS
      retval BOOLEAN := FALSE;
   BEGIN
      IF val1 IS NULL and oper = c_isnull
     THEN
        retval := TRUE;
     ELSIF val1 IS NOT NULL AND  oper = c_isnotnull
     THEN
        retval := TRUE;
     ELSE
        retval := FALSE;
     END IF;
     RETURN retval;
   END;

   FUNCTION val (val1 IN BOOLEAN, val2 IN BOOLEAN, oper IN VARCHAR2)
      RETURN BOOLEAN
   IS
      retval BOOLEAN := FALSE;
   BEGIN
      IF oper IN (c_isnull, c_isnotnull)
     THEN
        RETURN val (val1, oper);
      ELSIF val1 IS NULL or val2 IS NULL
     THEN
        retval := FALSE;
     ELSIF UPPER (oper) IN (c_eq, 'EQ')
     THEN
        retval := val1 = val2;
     END IF;
     RETURN retval;
   END;

   FUNCTION val (val1 IN DATE, val2 IN DATE, oper IN VARCHAR2)
      RETURN BOOLEAN
   IS
      retval BOOLEAN := FALSE;
   BEGIN
      IF oper IN (c_isnull, c_isnotnull)
     THEN
        RETURN val (val1, oper);
      ELSIF val1 IS NULL or val2 IS NULL
     THEN
        retval := FALSE;
     ELSIF UPPER (oper) IN (c_eq, 'EQ')
     THEN
        retval := val1 = val2;
     ELSIF UPPER (oper) IN (c_gt, 'GT')
     THEN
        retval := val1 > val2;
     ELSIF UPPER (oper) IN (c_lt, 'LT')
     THEN
        retval := val1 < val2;
     ELSIF UPPER (oper) IN (c_ge, 'GE')
     THEN
        retval := val1 >= val2;
     ELSIF UPPER (oper) IN (c_le, 'LE')
     THEN
        retval := val1 <= val2;
     END IF;
     RETURN retval;
   END;

   FUNCTION val (val1 IN NUMBER, val2 IN NUMBER, oper IN VARCHAR2)
      RETURN BOOLEAN
   IS
      retval BOOLEAN := FALSE;
   BEGIN
      IF oper IN (c_isnull, c_isnotnull)
      THEN
        RETURN val (val1, oper);
      ELSIF val1 IS NULL or val2 IS NULL
      THEN
        retval := FALSE;
     ELSIF UPPER (oper) IN (c_eq, 'EQ')
     THEN
        retval := val1 = val2;
     ELSIF UPPER (oper) IN (c_gt, 'GT')
     THEN
        retval := val1 > val2;
     ELSIF UPPER (oper) IN (c_lt, 'LT')
     THEN
        retval := val1 < val2;
     ELSIF UPPER (oper) IN (c_ge, 'GE')
     THEN
        retval := val1 >= val2;
     ELSIF UPPER (oper) IN (c_le, 'LE')
     THEN
        retval := val1 <= val2;
     END IF;
     RETURN retval;
   END;

   FUNCTION val (val1 IN VARCHAR2, val2 IN VARCHAR2, oper IN VARCHAR2)
      RETURN BOOLEAN
   IS
      retval BOOLEAN := FALSE;
   BEGIN
      IF oper IN (c_isnull, c_isnotnull)
     THEN
        RETURN val (val1, oper);
      ELSIF val1 IS NULL or val2 IS NULL
     THEN
        retval := FALSE;
     ELSIF UPPER (oper) IN (c_eq, 'EQ')
     THEN
        retval := val1 = val2;
     ELSIF UPPER (oper) IN (c_gt, 'GT')
     THEN
        retval := val1 > val2;
     ELSIF UPPER (oper) IN (c_lt, 'LT')
     THEN
        retval := val1 < val2;
     ELSIF UPPER (oper) IN (c_ge, 'GE')
     THEN
        retval := val1 >= val2;
     ELSIF UPPER (oper) IN (c_le, 'LE')
     THEN
        retval := val1 <= val2;
     ELSIF oper = c_like
     THEN
        retval := val1 LIKE val2;
     END IF;
     RETURN retval;
   END;
       
END PLGbool;
/