plgvar.spb

Oracle PL/SQL procedure generator (second generator type)

CREATE OR REPLACE PACKAGE BODY PLGvar
IS
/*----------------------------------------------------------------
||                  PL/Vision Professional 
||----------------------------------------------------------------
||    File: plgvar.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 PLGadmin.identifier := 'plgvar';

   PROCEDURE pl (str IN VARCHAR2)
   IS
   BEGIN
      IF LENGTH (str) > 80
      THEN
         DBMS_OUTPUT.PUT_LINE (substr (str, 1, 80));
         pl (substr (str, 81));
      ELSE
         DBMS_OUTPUT.PUT_LINE (str);
      END IF;     
   END;

/* Public Modules */

   PROCEDURE closecur (newcur IN OUT INTEGER, oldcur IN INTEGER,
      msg_in IN VARCHAR2 := NULL)
   IS
   BEGIN
      IF oldcur IS NULL
      THEN
         DBMS_SQL.CLOSE_CURSOR (newcur);
      END IF;
   END;

   PROCEDURE assign (
      expr IN VARCHAR2, 
      var IN OUT BOOLEAN,
      dyncur IN INTEGER := NULL,
      use_plgerr IN BOOLEAN := TRUE)
   IS
      v_cur INTEGER := dyncur;
      v_var PLGadmin.identifier;
      fdbk BINARY_INTEGER;
   BEGIN
      --initcur (v_cur, 'assign boolean from expr: ' || expr);
      DBMS_SQL.parse (v_cur,
         'BEGIN :var := PLGbool.stg(' || expr || '); END;', 
         DBMS_SQL.NATIVE);
      DBMS_SQL.BIND_VARIABLE (v_cur, 'var', 'a', 100);
      fdbk := DBMS_SQL.EXECUTE (v_cur);
      DBMS_SQL.VARIABLE_VALUE (v_cur, 'var', v_var);
      closecur (v_cur, dyncur, '   set to ' || v_var);
      IF PLGbool.val (v_var)
      THEN
         var := TRUE;
      ELSE
         var := FALSE;
      END IF;
   EXCEPTION
      WHEN OTHERS
      THEN 
         IF use_plgerr
         THEN
             PLGerr.raise (
                PLGerr.invalid_assign,
                'Error ' || SQLCODE || ' Invalid assignment for Boolean "' || expr);
         ELSE
            RAISE;
         END IF; 
   END;
   
   PROCEDURE assign (expr IN VARCHAR2, var IN OUT DATE,
      dyncur IN INTEGER := NULL,
      use_plgerr IN BOOLEAN := TRUE)
   IS
      v_cur INTEGER := dyncur;
      fdbk BINARY_INTEGER;
   BEGIN
      --initcur (v_cur, 'assign date from expr: ' || expr);
      DBMS_SQL.parse (v_cur, 'BEGIN :var := TO_DATE(' || expr || '); END;', DBMS_SQL.NATIVE);
      DBMS_SQL.BIND_VARIABLE (v_cur, 'var', SYSDATE);
      fdbk := DBMS_SQL.execute (v_cur);
      DBMS_SQL.VARIABLE_VALUE (v_cur, 'var', var);
      closecur (v_cur, dyncur, '   set to ' || TO_CHAR (var));
   EXCEPTION
      WHEN OTHERS
      THEN 
         IF use_plgerr
         THEN
             PLGerr.raise (
                PLGerr.invalid_assign, 
                'Error ' || SQLCODE || ' Invalid assignment for Date "' || expr);
         ELSE
            RAISE;
         END IF; 
   END;

   PROCEDURE assign (expr IN VARCHAR2, var IN OUT NUMBER,
      dyncur IN INTEGER := NULL,
      use_plgerr IN BOOLEAN := TRUE)
   IS
      v_cur INTEGER := dyncur;
      fdbk BINARY_INTEGER;
   BEGIN
      --initcur (v_cur, 'assign number from expr: ' || expr);
      DBMS_SQL.parse (v_cur, 'BEGIN :var := TO_NUMBER(' || expr || '); END;', DBMS_SQL.NATIVE);
      DBMS_SQL.BIND_VARIABLE (v_cur, 'var', 1);
      fdbk := DBMS_SQL.EXECUTE (v_cur);
      DBMS_SQL.VARIABLE_VALUE (v_cur, 'var', var);
      closecur (v_cur, dyncur, '   set to ' || TO_NUMBER (var));
   EXCEPTION
      WHEN OTHERS
      THEN 
         IF use_plgerr
         THEN
             PLGerr.raise (
                PLGerr.invalid_assign, 
                'Error ' || SQLCODE || ' Invalid assignment for Number "' || expr);
         ELSE
            RAISE;
         END IF; 
   END;

   PROCEDURE assign (expr IN VARCHAR2, var IN OUT VARCHAR2,
      dyncur IN INTEGER := NULL,
      use_plgerr IN BOOLEAN := TRUE)
   IS
      v_cur INTEGER := dyncur;
      fdbk BINARY_INTEGER;
   BEGIN
      --initcur (v_cur, 'assign varchar2 from expr: ' || expr);
      DBMS_SQL.parse (v_cur, 'BEGIN :var := ' || expr || '; END;', DBMS_SQL.NATIVE);
      DBMS_SQL.BIND_VARIABLE (v_cur, 'var', 'a', PLGadmin.dbmax_len);
      fdbk := DBMS_SQL.EXECUTE (v_cur);
      DBMS_SQL.VARIABLE_VALUE (v_cur, 'var', var);
      closecur (v_cur, dyncur, '   set to ' || var);
   EXCEPTION
      WHEN OTHERS
      THEN 
         IF use_plgerr
         THEN
             PLGerr.raise (
                PLGerr.invalid_assign, 
                'Error ' || SQLCODE || ' Invalid assignment for String "' || expr);
         ELSE
            RAISE;
         END IF; 
   END;

   PROCEDURE copyto (val IN VARCHAR2, nm IN VARCHAR2,
      dyncur IN INTEGER := NULL,
      showerr IN BOOLEAN := FALSE,
      use_plgerr IN BOOLEAN := TRUE)
   IS
      v_cur INTEGER := dyncur;
      fdbk BINARY_INTEGER;
   BEGIN
      --initcur (v_cur, 'copyto varchar2 from val "' || val || '" to ' || nm);
      DBMS_SQL.parse 
         (v_cur, 
          'BEGIN ' || nm || ' := ''' || val || '''; END;', DBMS_SQL.NATIVE);
      fdbk := DBMS_SQL.EXECUTE (v_cur);
      closecur (v_cur, dyncur);
   EXCEPTION
      WHEN OTHERS
      THEN  
         IF showerr
         THEN
            pl (
               'PLGvar.copy error on "' || val || '" to "' || nm || '": ' || SQLERRM);
         END IF;
         IF use_plgerr
         THEN
            PLGerr.raise (
               PLGerr.invalid_copyto, '"' || val || '" to "' || nm || '": ' || SQLERRM);
         ELSE
            RAISE;
         END IF; 
   END;
END PLGvar;
/