gentecode.sql

Oracle PL/SQL procedure generator

/*
Package Generator

Note:

They have the following structure
mk_<operation name>_dbproc   : Generates a standalone non packaged procedure
mk_<operation>_packhead      : Generates a procedure header for inclusion in a package header
mk_<operation>_proc          : Generates a standalone packaged procedure -- We had to do this to get around the
                               fact that Forte does not recognize the package.proc notation ( we use Forte as a
                               front-end )
mk_<operation>_packproc      : Generates a procedure body for inclusion in a package body
mk_one_package_header        : Generates a package header by using the information in package_table_map ( a separate
                               table that needs to be created ( owner, package_name, table_name )
mk_one_package_body          : Generates the corresponding package body
*/

DROP table package_table_map;

CREATE TABLE package_table_map
( owner varchar2(60), package_name varchar2(60), table_name  varchar2(60));

INSERT INTO package_table_map
     VALUES (USER, 'PERS', 'EMP');
INSERT INTO package_table_map
     VALUES (USER, 'PERS', 'DEPT');

CREATE OR REPLACE PROCEDURE mk_insert_dbproc (
   ownername_arg    IN   VARCHAR2,
   table_name_arg   IN   VARCHAR2
)
/*
||   Created on : June 8th 1997
||   Comments   : Standalone Insert Procedure Generator
*/
AS
   column_index   INTEGER;

   CURSOR colname_cur
   IS
      SELECT column_id, column_name
        FROM all_tab_columns
       WHERE owner = ownername_arg
         AND table_name = table_name_arg
       ORDER BY column_id;
BEGIN
   SELECT MAX (column_id)
     INTO column_index
     FROM all_tab_columns
    WHERE owner = ownername_arg
      AND table_name = table_name_arg;
   dbms_output.put_line (' CREATE OR REPLACE PROCEDURE INSERT' || table_name_arg || '(');
   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   : Standalone Insert Procedure ');
   dbms_output.put_line (' ||   automatically generated using the PL/Vision building blocks '
   );
   dbms_output.put_line (' */ ');

   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 ('IS  ');
   dbms_output.put_line ('BEGIN');
   dbms_output.put_line ('INSERT INTO ' || table_name_arg || '(');

   FOR rowrec IN colname_cur
   LOOP
      IF rowrec.column_id < column_index
      THEN
         dbms_output.put_line (rowrec.column_name || ',');
      ELSE
         dbms_output.put_line (rowrec.column_name);
      END IF;
   END LOOP;

   dbms_output.put_line (')');
   dbms_output.put_line ('VALUES ');
   dbms_output.put_line ('(');

   FOR rowrec IN colname_cur
   LOOP
      IF rowrec.column_id < column_index
      THEN
         dbms_output.put_line (rowrec.column_name || '_arg,');
      ELSE
         dbms_output.put_line (rowrec.column_name || '_arg');
      END IF;
   END LOOP;

   dbms_output.put_line (');');
   dbms_output.put_line (' ReturnValue := 1 ;');
   dbms_output.put_line ('EXCEPTION -- Exception ');
   dbms_output.put_line ('WHEN OTHERS THEN');
   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_delete_dbproc (
   ownername_arg    IN   VARCHAR2,
   table_name_arg   IN   VARCHAR2
)
/*
||   Name       : mk_delete_dbproc
||   Created on : June 8th 1997
||   Comments   : Standalone Delete Procedure Generator
*/
AS
   arglist         VARCHAR2 (2000);
   colist          VARCHAR2 (2000);
   updatearglist   VARCHAR2 (2000);
   textline        VARCHAR2 (2000);
   pktextline      VARCHAR2 (2000);

   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
   FOR rowrec IN pkcol_cur
   LOOP
      pktextline :=
         pktextline || rowrec.column_name || ' = ' ||
            rowrec.column_name ||
            '_arg AND ';
   END LOOP;

/* Now take of the dangling AND at the end of the column list */
   pktextline := SUBSTR (pktextline, 1, (LENGTH (pktextline) - 4));
   dbms_output.put_line (' CREATE OR REPLACE PROCEDURE DELETE' || table_name_arg || '(');
   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   : Standalone Delete Procedure ');
   dbms_output.put_line (' ||   automatically generated using the PL/Vision building blocks '
   );
   dbms_output.put_line (' */ ');

   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 ('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 (' CLOSE table_cur ;');
   dbms_output.put_line ('EXCEPTION -- Exception ');
   dbms_output.put_line ('WHEN OTHERS THEN');
   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;
/



CREATE OR REPLACE PROCEDURE mk_update_dbproc (
   ownername_arg    IN   VARCHAR2,
   table_name_arg   IN   VARCHAR2
)
AS
   column_index    INTEGER;
   updatearglist   VARCHAR2 (2000);
   textline        VARCHAR2 (2000);
   pktextline      VARCHAR2 (2000);

   CURSOR colname_cur
   IS
      SELECT column_id, column_name
        FROM all_tab_columns
       WHERE owner = ownername_arg
         AND table_name = table_name_arg
       ORDER BY column_id;

   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;

   CURSOR nopk_cur
   IS
      SELECT col.column_name
        FROM all_tab_columns col
       WHERE col.table_name = table_name_arg
         AND col.owner = ownername_arg
         AND col.column_name NOT IN (SELECT conscol.column_name
                                       FROM all_cons_columns conscol,
                                            all_constraints cons
                                      WHERE col.owner = conscol.owner
                                        AND conscol.owner = cons.owner
                                        AND col.table_name =
                                                   conscol.table_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
   FOR rowrec IN nopk_cur
   LOOP
      updatearglist :=
         updatearglist || rowrec.column_name || ' = ' ||
            rowrec.column_name ||
            '_arg,';
   END LOOP;

   FOR rowrec IN pkcol_cur
   LOOP
      pktextline :=
         pktextline || rowrec.column_name || ' = ' ||
            rowrec.column_name ||
            '_arg AND ';
   END LOOP;

/* Now we have a dangling comma at the end of the colist and updatearg list ...so time to remove it */
   updatearglist :=
                SUBSTR (updatearglist, 1, (LENGTH (updatearglist) - 1));
/* Now take of the dangling AND at the end of the column list */
   pktextline := SUBSTR (pktextline, 1, (LENGTH (pktextline) - 4));
   dbms_output.put_line (' CREATE OR REPLACE PROCEDURE UPDATE' || table_name_arg || '(');
   dbms_output.put_line (' /* ');
   dbms_output.put_line (' ||   Name       : Update' || table_name_arg);
   dbms_output.put_line (' ||   Created on'|| '-' || SYSDATE);
   dbms_output.put_line (' ||   Comments   : Standalone Update Procedure ');
   dbms_output.put_line (' ||   automatically generated using the PL/Vision building blocks '
   );
   dbms_output.put_line (' */ ');

   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, SQLMessage OUT VARCHAR2, SQLStatus OUT INTEGER '
   );
   dbms_output.put_line (') ');
   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.NODATAUPDATED ;');
   dbms_output.put_line ('END IF;');
   dbms_output.put_line ('UPDATE ' || table_name_arg || ' SET ');
   dbms_output.put_line (updatearglist);
   dbms_output.put_line ('WHERE CURRENT OF table_cur ;');
   dbms_output.put_line (' ReturnValue := 1 ;');
   dbms_output.put_line (' CLOSE table_cur ;');
   dbms_output.put_line ('EXCEPTION -- Exception ');
   dbms_output.put_line ('WHEN OTHERS THEN');
   dbms_output.put_line ('    ReturnValue := 0; ');
   dbms_output.put_line ('    CLOSE table_cur ;');
   dbms_output.put_line ('END UPDATE' || table_name_arg || ';');
   dbms_output.put_line ('/');
END;
/



--=====Package header operations

CREATE OR REPLACE PROCEDURE mk_insert_packhead (
   ownername_arg    IN   VARCHAR2,
   table_name_arg   IN   VARCHAR2
)
/*
||   Name       : mk_insert_packhead
||   Created on : June 8th 1997
||   Comments   : Insert Procedure Header Generator ( for use with the package 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 (' PROCEDURE INSERT' || table_name_arg || '( ');