genaa.sql

Oracle PL/SQL procedure generator

      pkg_in        IN   VARCHAR2,
      pky_info_in   IN   pky_rec,
      ucols_in      IN   inds_tt
   )
   IS 
      v_fulltab   identifier_t := v_sch || '.' || v_tab;

      PROCEDURE gen_declarations
      IS 
         indx           identifier_t := ucols_in.FIRST;
         firstcolindx   PLS_INTEGER;
      BEGIN
         -- Global package variables
         IF do_reload_in
         THEN
            pl ('g_last_load DATE;');
            pl ('g_reload_interval INTERVAL DAY TO SECOND');
            pl (':= NULL; -- Auto reload turned off');
         END IF;

         -- Associative Array types and actual arrays
         -- First cached data based on primary key.
         pl (
               'TYPE '
            || tab_in
            || '_aat IS TABLE OF '
            || v_fulltab
            || '%ROWTYPE INDEX BY PLS_INTEGER;'
         );
         pl (pky_info_in.array_name || ' ' || tab_in || '_aat;');

         -- Now types and arrays for unique indexes.
         WHILE indx <= ucols_in.LAST
         LOOP
            IF ucols_in (indx).numcols = 1
            THEN
               firstcolindx := ucols_in (indx).cols.FIRST;
               pl (
                     'TYPE '
                  || ucols_in (indx).NAME
                  || '_aat IS TABLE OF '
                  || v_fulltab
                  || '.'
                  || pky_info_in.column_name
                  || '%TYPE INDEX BY '
                  || ucols_in (indx).cols (firstcolindx).aa_index_type
                  || ';'
               );
            ELSE
               pl (
                     'TYPE '
                  || ucols_in (indx).NAME
                  || '_aat IS TABLE OF '
                  || v_fulltab
                  || '.'
                  || pky_info_in.column_name
                  || '%TYPE INDEX BY '
                  || ucols_in (indx).subtype_name
                  || ';'
               );
            END IF;

            pl (
               ucols_in (indx).array_name || ' ' || ucols_in (indx).NAME || '_aat;'
            );
            indx := ucols_in.NEXT (indx);
         END LOOP;
      END;

      PROCEDURE gen_concat_functions
      IS 
         indx   identifier_t := ucols_in.FIRST;
      BEGIN
         -- Functions that return the concatenated value.
         WHILE indx <= ucols_in.LAST
         LOOP
            IF ucols_in (indx).numcols > 1
            THEN
               -- Generic VARCHAR2-indexed tables...
               pl ('function ' || ucols_in (indx).val_for_name || ' ( ');

               FOR cindx IN
                  ucols_in (indx).cols.FIRST .. ucols_in (indx).cols.LAST
               LOOP
                  pl (
                        ucols_in (indx).cols (cindx).NAME
                     || '_in IN '
                     || v_fulltab
                     || '.'
                     || ucols_in (indx).cols (cindx).NAME
                     || '%TYPE'
                  );

                  IF cindx < ucols_in (indx).cols.LAST
                  THEN
                     pl (',');
                  END IF;
               END LOOP;

               pl (') return ' || ucols_in (indx).subtype_name);
               pl ('is begin return (');

               FOR cindx IN ucols_in (indx).cols.FIRST .. ucols_in (indx).cols.LAST
               LOOP
                  pl (ucols_in (indx).cols (cindx).NAME || '_in');

                  IF cindx < ucols_in (indx).cols.LAST
                  THEN
                     pl (' || ');
                  END IF;
               END LOOP;

               pl ('); end ' || ucols_in (indx).val_for_name || ';');
            END IF;

            indx := ucols_in.NEXT (indx);
         END LOOP;
      END;

      FUNCTION rowval (
         ind_in      IN   inds_rec,
         prefix_in   IN   VARCHAR2,
         suffix_in   IN   VARCHAR2
      )
         RETURN VARCHAR2
      IS 
         retval   VARCHAR2 (32767);
      BEGIN
         -- If single column return that column name.
         -- If > 1 column, return call to function val_for
         --   for each of the input values.
         IF ind_in.numcols = 1
         THEN
            retval :=    prefix_in
                      || ind_in.cols (ind_in.cols.FIRST).NAME
                      || suffix_in;
         ELSE
            retval := ind_in.val_for_name || '(';

            FOR cindx IN 1 .. ind_in.cols.COUNT
            LOOP
               retval :=    retval
                         || prefix_in
                         || ind_in.cols (cindx).NAME
                         || suffix_in;

               IF cindx < ind_in.cols.LAST
               THEN
                  retval := retval || ',';
               END IF;
            END LOOP;

            retval := retval || ')';
         END IF;

         RETURN retval;
      END;

      PROCEDURE gen_retrieval_functions
      IS 
         indx   identifier_t := ucols_in.FIRST;
      BEGIN
          -- For the primary key, a function to return one row
         -- from the cached data array.
         NULL;
         -- For each unique index, a function to return one row
         -- using a separate associative array to locate the
         -- data in the primary key array.
         NULL;
         -- Declare a function for each index and pkey
         pl (
               'function onerow ( '
            || pky_info_in.column_name
            || '_in IN '
            || v_fulltab
            || '.'
            || pky_info_in.column_name
            || '%TYPE) return '
            || v_fulltab
            || '%ROWTYPE is begin return '
            || pky_info_in.array_name
            || ' ('
            || pky_info_in.column_name
            || '_in); end;'
         );

         WHILE indx <= ucols_in.LAST
         LOOP
            -- Generic VARCHAR2-indexed tables...
            pl ('function onerow_by_' || ucols_in (indx).NAME || ' ( ');

            FOR cindx IN ucols_in (indx).cols.FIRST .. ucols_in (indx).cols.LAST
            LOOP
               pl (
                     ucols_in (indx).cols (cindx).NAME
                  || '_in IN '
                  || v_fulltab
                  || '.'
                  || ucols_in (indx).cols (cindx).NAME
                  || '%TYPE'
               );

               IF cindx < ucols_in (indx).cols.LAST
               THEN
                  pl (',');
               END IF;
            END LOOP;

            pl (
                  ') return '
               || v_fulltab
               || '%ROWTYPE is begin return '
               || pky_info_in.array_name
               || ' ('
               || ucols_in (indx).NAME
               || '_aa ('
            );

            IF ucols_in (indx).numcols = 1
            THEN
               pl (
                     ucols_in (indx).cols (ucols_in (indx).cols.FIRST).NAME
                  || '_in'
               );
            ELSE
               pl (rowval (ucols_in (indx), NULL, '_in'));
            END IF;

            pl (')); end;');
            indx := ucols_in.NEXT (indx);
         END LOOP;
      END;

      PROCEDURE gen_reload_logic
      IS 
         indx   identifier_t := ucols_in.FIRST;
      BEGIN
         -- Procedure to load the arrays.
         pl ('procedure load_arrays is begin');
         pl ('FOR rec IN (SELECT * FROM ' || v_fulltab || ')');
         pl ('LOOP');
         pl (
               pky_info_in.array_name
            || '(rec.'
            || pky_info_in.column_name
            || ') := rec;'
         );

         WHILE indx <= ucols_in.LAST
         LOOP
            pl (
                  ucols_in (indx).array_name
               || '('
               || rowval (ucols_in (indx), 'rec.', NULL)
               || ') := rec.'
               || pky_info.column_name
               || ';'
            );
            indx := ucols_in.NEXT (indx);
         END LOOP;

         pl ('end loop;');

         IF do_reload_in
         THEN
            pl ('g_last_load := SYSDATE;');
         END IF;

         pl ('END load_arrays;');

         IF do_reload_in
         THEN
            -- Add logic to reload arrays after specified interval.
            NULL;
         END IF;
      END;

      PROCEDURE gen_test_program
      IS 
         indx   identifier_t := ucols_in.FIRST;
      BEGIN
         pl ('procedure test is');
         pl ('pky_rec ' || v_fulltab || '%ROWTYPE;');

         WHILE indx <= ucols_in.LAST
         LOOP
            pl (
               ucols_in (indx).array_name || '_rec ' || v_fulltab || '%ROWTYPE;'
            );
            indx := ucols_in.NEXT (indx);
         END LOOP;

         pl ('begin');
         pl ('for rec in (select * from ' || v_fulltab || ') loop');
         pl ('pky_rec := onerow (rec.' || pky_info_in.column_name || ');');
         indx := ucols_in.FIRST;

         WHILE indx <= ucols_in.LAST
         LOOP
            pl (
                  ucols_in (indx).array_name
               || '_rec := '
               || 'onerow_by_'
               || ucols_in (indx).NAME
               || ' ( '
            );

            FOR cindx IN ucols_in (indx).cols.FIRST .. ucols_in (indx).cols.LAST
            LOOP
               pl ('rec.' || ucols_in (indx).cols (cindx).NAME);

               IF cindx < ucols_in (indx).cols.LAST
               THEN
                  pl (',');
               END IF;
            END LOOP;

            pl (');');
            pl (
                  'if rec.'
               || pky_info_in.column_name
               || ' = '
               || ucols_in (indx).array_name
               || '_rec.'
               || pky_info_in.column_name
               || ' then'
            );
            pl (
                  'dbms_output.put_line ('''
               || ucols_in (indx).NAME
               || '  lookup OK'');'
            );
            pl ('else');
            pl (
                  'dbms_output.put_line ('''
               || ucols_in (indx).NAME
               || '  lookup NOT OK'');'
            );
            pl ('end if;');
            indx := ucols_in.NEXT (indx);
         END LOOP;

         pl ('end loop;');
         pl ('end test;');
      END;

      PROCEDURE gen_init_section
      IS
      BEGIN
         pl ('BEGIN load_arrays;');
      END;
   BEGIN
      pl ('create or replace package body ' || pkg_in || ' is ');
      gen_declarations;
      gen_concat_functions;
      gen_retrieval_functions;
      gen_reload_logic;
      gen_test_program;
      gen_init_section;
      pl ('end ' || pkg_in || ';');
      pl ('/');
      dump_output;
   END;
BEGIN
   validate_and_initialize (pky_info, ucols);
   gen_package_spec (v_tab, v_sch, v_pkg_name, pky_info, ucols);
   gen_package_body (v_tab, v_sch, v_pkg_name, pky_info, ucols);
END;