gentecode.sql

Oracle PL/SQL procedure generator

   dbms_output.put_line ('AS  ');
   dbms_output.put_line (' CURSOR table_cur IS ');
   dbms_output.put_line (' SELECT * FROM ' || table_name_arg);
   dbms_output.put_line (' WHERE ');
   dbms_output.put_line (pktextline);
   dbms_output.put_line (' FOR UPDATE ; ');
   dbms_output.put_line (' CurrentRow ' || table_name_arg || '%ROWTYPE ;');
   dbms_output.put_line ('BEGIN');
   dbms_output.put_line (' OPEN table_cur ;');
   dbms_output.put_line (' FETCH table_cur INTO CurrentRow ;');
   dbms_output.put_line (' IF table_cur%NOTFOUND THEN ');
   dbms_output.put_line ('    RAISE CAD_DB_EXCEPTIONS.NODATADELETED ;');
   dbms_output.put_line ('END IF;');
   dbms_output.put_line ('DELETE FROM ' || table_name_arg ||
           ' WHERE CURRENT OF table_cur; '
   );
   dbms_output.put_line (' ReturnValue := 1 ;');
   dbms_output.put_line (' SQLStatus := SQLCODE ; ');
   dbms_output.put_line (' SQLMesage := SQLERRM ; ');
   dbms_output.put_line (' CLOSE table_cur ;');
   dbms_output.put_line ('EXCEPTION -- Exception ');
   dbms_output.put_line ('WHEN OTHERS THEN');
   dbms_output.put_line ('    SQLStatus  := SQLCODE; ');
   dbms_output.put_line ('    SQLMessage := SQLERRM; ');
   dbms_output.put_line ('    ReturnValue := 0; ');
   dbms_output.put_line ('    CLOSE table_cur ;');
   dbms_output.put_line ('END DELETE' || table_name_arg || ';');
   dbms_output.put_line ('--');
END;
/












-- ============Packaged procedures

CREATE OR REPLACE PROCEDURE mk_insert_proc (
   ownername_arg      IN   VARCHAR2,
   package_name_arg   IN   VARCHAR2,
   table_name_arg     IN   VARCHAR2
)
/*
||   Name       : mk_insert_proc
||   Created on : June 8th 1997
||   Comments   : Packaged Insert Procedure Generator
*/

AS
   CURSOR colname_cur
   IS
      SELECT column_name
        FROM all_tab_columns
       WHERE owner = ownername_arg
         AND table_name = table_name_arg
       ORDER BY column_id;
BEGIN
   dbms_output.put_line (' CREATE OR REPLACE PROCEDURE INSERT' || table_name_arg || '( '
   );

   FOR rowrec IN colname_cur
   LOOP
      dbms_output.put_line (rowrec.column_name || '_arg IN  ' || table_name_arg || '.' ||
              rowrec.column_name ||
              '%TYPE,'
      );
   END LOOP;

   dbms_output.put_line (' ReturnValue OUT INTEGER ');
   dbms_output.put_line (') ');
   dbms_output.put_line (' /* ');
   dbms_output.put_line (' ||   Name       : INSERT' || table_name_arg);
   dbms_output.put_line (' ||   Created on ' || SYSDATE);
   dbms_output.put_line (' ||   Comments   : Packaged procedure automatically generated  '
   );
   dbms_output.put_line (' ||   using the PL/Vision building blocks ');
   dbms_output.put_line (' */ ');
   dbms_output.put_line ('IS  ');
   dbms_output.put_line (' SQLMessage VARCHAR2(80) ; ');
   dbms_output.put_line (' SQLStatus  INTEGER      ; ');
   dbms_output.put_line ('BEGIN');
   dbms_output.put_line (package_name_arg || '.INSERT' || table_name_arg || '(');

   FOR rowrec IN colname_cur
   LOOP
      dbms_output.put_line (rowrec.column_name || '_arg ,');
   END LOOP;

   dbms_output.put_line (' ReturnValue, SQLMessage , SQLStatus ');
   dbms_output.put_line (');');
   dbms_output.put_line (' ReturnValue := 1 ;');
   dbms_output.put_line (' SQLStatus := SQLCODE ; ');
   dbms_output.put_line (' SQLMessage := SQLERRM ; ');
   dbms_output.put_line ('EXCEPTION -- Exception ');
   dbms_output.put_line ('WHEN OTHERS THEN');
   dbms_output.put_line ('    SQLStatus  := SQLCODE; ');
   dbms_output.put_line ('    SQLMessage := SQLERRM; ');
   dbms_output.put_line ('    ReturnValue := 0; ');
   dbms_output.put_line ('END INSERT' || table_name_arg || ';');
   dbms_output.put_line ('/');
END;
/


CREATE OR REPLACE PROCEDURE mk_update_proc (
   ownername_arg      IN   VARCHAR2,
   package_name_arg   IN   VARCHAR2,
   table_name_arg     IN   VARCHAR2
)
AS
   CURSOR colname_cur
   IS
      SELECT column_name
        FROM all_tab_columns
       WHERE owner = ownername_arg
         AND table_name = table_name_arg
       ORDER BY column_id;
BEGIN
   dbms_output.put_line (' CREATE OR REPLACE PROCEDURE UPDATE' || table_name_arg || '( '
   );

   FOR rowrec IN colname_cur
   LOOP
      dbms_output.put_line (rowrec.column_name || '_arg IN  ' || table_name_arg || '.' ||
              rowrec.column_name ||
              '%TYPE,'
      );
   END LOOP;

   dbms_output.put_line (' ReturnValue OUT INTEGER ');
   dbms_output.put_line (') ');
   dbms_output.put_line (' /* ');
   dbms_output.put_line (' ||   Name       : INSERT' || table_name_arg);
   dbms_output.put_line (' ||   Filename   : ');
   dbms_output.put_line (' ||   Created on ' || SYSDATE);
   dbms_output.put_line (' ||   Comments   : Packaged procedure automatically generated  '
   );
   dbms_output.put_line (' ||   using the PL/Vision building blocks ');
   dbms_output.put_line (' */ ');
   dbms_output.put_line ('IS  ');
   dbms_output.put_line (' SQLMessage VARCHAR2(80) ; ');
   dbms_output.put_line (' SQLStatus  INTEGER      ; ');
   dbms_output.put_line ('BEGIN');
   dbms_output.put_line (package_name_arg || '.UPDATE' || table_name_arg || '(');

   FOR rowrec IN colname_cur
   LOOP
      dbms_output.put_line (rowrec.column_name || '_arg ,');
   END LOOP;

   dbms_output.put_line (' ReturnValue, SQLMessage , SQLStatus ');
   dbms_output.put_line (');');
   dbms_output.put_line ('EXCEPTION -- Exception ');
   dbms_output.put_line ('WHEN OTHERS THEN');
   dbms_output.put_line ('    SQLStatus  := SQLCODE; ');
   dbms_output.put_line ('    SQLMessage := SQLERRM; ');
   dbms_output.put_line ('    ReturnValue := 0; ');
   dbms_output.put_line ('END UPDATE' || table_name_arg || ';');
   dbms_output.put_line ('/');
END;
/


CREATE OR REPLACE PROCEDURE mk_delete_proc (
   ownername_arg      IN   VARCHAR2,
   package_name_arg   IN   VARCHAR2,
   table_name_arg     IN   VARCHAR2
)
/*
||   Name       : mk_delete_proc
||   Comments   : Packaged Delete Procedure Generator - since FORTE does not recognize the
||   package.object notation, we encapsulate it in a standalone procedure
*/

AS
   CURSOR pkcol_cur
   IS
      SELECT col.column_name
        FROM all_tab_columns col,
             all_cons_columns conscol,
             all_constraints cons
       WHERE col.table_name = table_name_arg
         AND col.owner = ownername_arg
         AND col.owner = conscol.owner
         AND conscol.owner = cons.owner
         AND col.table_name = conscol.table_name
         AND col.column_name = conscol.column_name
         AND conscol.table_name = cons.table_name
         AND cons.constraint_name = conscol.constraint_name
         AND cons.constraint_type = 'P'
       ORDER BY col.column_id;
BEGIN
   dbms_output.put_line ('CREATE OR REPLACE  PROCEDURE DELETE' || table_name_arg || '( '
   );

   FOR rowrec IN pkcol_cur
   LOOP
      dbms_output.put_line (rowrec.column_name || '_arg IN  ' || table_name_arg || '.' ||
              rowrec.column_name ||
              '%TYPE,'
      );
   END LOOP;

   dbms_output.put_line (' ReturnValue OUT INTEGER ');
   dbms_output.put_line (') ');
   dbms_output.put_line (' /* ');
   dbms_output.put_line (' ||   Name       : DELETE' || table_name_arg);
   dbms_output.put_line (' ||   Created on ' || SYSDATE);
   dbms_output.put_line (' ||   Comments   : Packaged procedure automatically generated using the '
   );
   dbms_output.put_line (' ||                PL/Vision building blocks ');
   dbms_output.put_line (' */ ');
   dbms_output.put_line ('AS  ');
   dbms_output.put_line (' SQLMessage VARCHAR2(80) ; ');
   dbms_output.put_line (' SQLStatus  INTEGER      ; ');
   dbms_output.put_line ('BEGIN');
   dbms_output.put_line (package_name_arg || '.DELETE' || table_name_arg || '(');

   FOR rowrec IN pkcol_cur
   LOOP
      dbms_output.put_line (rowrec.column_name || '_arg ,');
   END LOOP;

   dbms_output.put_line (' ReturnValue, SQLMessage , SQLStatus ');
   dbms_output.put_line (') ; ');
   dbms_output.put_line ('EXCEPTION -- Exception ');
   dbms_output.put_line ('WHEN OTHERS THEN');
   dbms_output.put_line ('    SQLStatus  := SQLCODE; ');
   dbms_output.put_line ('    SQLMessage := SQLERRM; ');
   dbms_output.put_line ('    ReturnValue := 0; ');
   dbms_output.put_line ('END DELETE' || table_name_arg || ';');
   dbms_output.put_line ('/');
END;
/

-- ==Package header generator

CREATE OR REPLACE PROCEDURE mk_one_package_header (
   ownername_arg      IN   VARCHAR2,
   package_name_arg   IN   VARCHAR2
)
AS
   CURSOR tablename_cur
   IS
      SELECT table_name
        FROM package_table_map
       WHERE owner = ownername_arg
         AND package_name = package_name_arg
       ORDER BY table_name;
BEGIN
   dbms_output.put_line (' CREATE OR REPLACE PACKAGE ' || package_name_arg);
   dbms_output.put_line (' /* ');
   dbms_output.put_line (' ||   Name       : ' || package_name_arg);
   dbms_output.put_line (' ||   Comments   : Package Body automatically generated using the PL/Vision building blocks '
   );
   dbms_output.put_line (' */ ');
   dbms_output.put_line (' IS ');
   dbms_output.put_line (' /*HELP ');
   dbms_output.put_line ('   Overview of ' || package_name_arg);
   dbms_output.put_line ('   HELP*/ ');
   dbms_output.put_line (' ');
   dbms_output.put_line (' /*EXAMPLES ');
   dbms_output.put_line ('   Examples of test ');
   dbms_output.put_line ('   EXAMPLES*/ ');
   dbms_output.put_line (' ');
   dbms_output.put_line (' /* Constants */ ');
   dbms_output.put_line (' ');
   dbms_output.put_line ('  /* Exceptions */ ');
   dbms_output.put_line (' ');
   dbms_output.put_line (' /* Variables */ ');
   dbms_output.put_line (' ');
   dbms_output.put_line (' /* Toggles */ ');
   dbms_output.put_line (' ');
   dbms_output.put_line ('/* Windows */ ');
   dbms_output.put_line (' ');
   dbms_output.put_line ('/* Programs */ ');
   dbms_output.put_line (' ');
   dbms_output.put_line (' PROCEDURE help (context_in IN VARCHAR2 := NULL); ');

   FOR maprec IN tablename_cur
   LOOP
      mk_insert_packhead (ownername_arg, maprec.table_name);
      dbms_output.put_line ('--');
      mk_update_packhead (ownername_arg, maprec.table_name);
      dbms_output.put_line ('--');
      mk_delete_packhead (ownername_arg, maprec.table_name);
      dbms_output.put_line ('--');
   END LOOP;

   dbms_output.put_line ('END ' || package_name_arg);
   dbms_output.put_line ('/');
END;
/




-- =======Package body generator

CREATE OR REPLACE PROCEDURE mk_one_package_body (
   ownername_arg      IN   VARCHAR2,
   package_name_arg   IN   VARCHAR2
)
AS
   CURSOR tablename_cur
   IS
      SELECT table_name
        FROM package_table_map
       WHERE owner = ownername_arg
         AND package_name = package_name_arg
       ORDER BY table_name;
BEGIN
   dbms_output.put_line (' CREATE OR REPLACE PACKAGE BODY ' || package_name_arg);
   dbms_output.put_line (' /* ');
   dbms_output.put_line (' ||   Name       : ' || package_name_arg);
   dbms_output.put_line (' ||   Comments   : Package Body automatically generated using the PL/Vision building blocks '
   );
   dbms_output.put_line (' */ ');
   dbms_output.put_line (' IS ');

   FOR maprec IN tablename_cur
   LOOP
      mk_insert_packproc (ownername_arg, maprec.table_name);
      dbms_output.put_line (' ');
      mk_update_packproc (ownername_arg, maprec.table_name);
      dbms_output.put_line (' ');
      mk_delete_packproc (ownername_arg, maprec.table_name);
   END LOOP;

   dbms_output.put_line ('END ' || package_name_arg);
   dbms_output.put_line ('/');
END;
/