gentecode.sql

Oracle PL/SQL procedure generator

   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 ('); ');
   dbms_output.put_line ('--');
END;
/


CREATE OR REPLACE PROCEDURE mk_delete_packhead (
   ownername_arg    IN   VARCHAR2,
   table_name_arg   IN   VARCHAR2
)
/*
||   Name       : mk_delete_packhead
||   Created on : June 8th 1997
||   Comments   : Delete Procedure Header Generator ( for use with the package generator )
*/

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 (' 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, SQLMessage OUT VARCHAR2, SQLStatus OUT INTEGER '
   );
   dbms_output.put_line ('); ');
   dbms_output.put_line ('--');
END;
/



CREATE OR REPLACE PROCEDURE mk_update_packhead (
   ownername_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 ('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, SQLMessage OUT VARCHAR2, SQLStatus OUT INTEGER '
   );
   dbms_output.put_line ('--');
   dbms_output.put_line ('); ');
   dbms_output.put_line (' ');
END;
/
-- ============Packaged procedure body 


CREATE OR REPLACE PROCEDURE mk_insert_packproc (
   ownername_arg    IN   VARCHAR2,
   table_name_arg   IN   VARCHAR2
)
/*
||   Name       : mk_insert_packproc
||   Created on : June 8th 1997
||   Comments   : Insert Packaged Procedure Body 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 ('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, SQLMessage OUT VARCHAR2, SQLStatus 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 (' SQLStatus := SQLCODE ; ');
   dbms_output.put_line (' SQLMesage := 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 || ';');
END;
/



CREATE OR REPLACE PROCEDURE mk_update_packproc (
   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 ('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, 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 (' 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 UPDATE' || table_name_arg || ';');
END;
/



CREATE OR REPLACE PROCEDURE mk_delete_packproc (
   ownername_arg    IN   VARCHAR2,
   table_name_arg   IN   VARCHAR2
)
AS
   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 (' 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, SQLMessage OUT VARCHAR2, SQLStatus OUT INTEGER '
   );
   dbms_output.put_line (') ');