plggen.tst

Oracle PL/SQL procedure generator (second generator type)

      EXCEPTION
         WHEN OTHERS
         THEN
            PLGerr.raise (
               PLGerr.hash_value_notfound,
               'Error finding loop/label ' || str || ' in hash.');
      END;
   END;

   FUNCTION loopstart (loopstate_in IN state_stk_type) RETURN INTEGER
   IS
      v_outer PLGadmin.identifier := outer_loop (loopstate_in.statevar);
      v_pkgname PLGadmin.identifier;
      v_row PLS_INTEGER := loop_stack.LAST;
      retval PLS_INTEGER;
   BEGIN
      /* "Manual override" */
      IF loopstate_in.startend_override
      THEN
         retval := loopstate_in.startrow;

      ELSIF offset_loop (loopstate_in.statevar)
      THEN
         retval := strbyname (
            PLGgenv.genrec.driver,
            loopstate_in.statevar || '_START',
            sch => PLGgenv.genrec.schema,
            context_in => curlooprow);
      ELSE
         IF nested_loop (loopstate_in.statevar) AND v_row IS NOT NULL
         THEN
            LOOP
               IF debugging
               THEN
                  pl ('loopstart checking ' || loop_stack(v_row).statevar ||
                     ' against ' || v_outer || '-' || v_row);
               END IF;
               EXIT WHEN v_row IS NULL OR loop_stack(v_row).statevar = v_outer;
               v_row := loop_stack.PRIOR (v_row);
            END LOOP;

            IF v_row IS NOT NULL
            THEN
               v_pkgname := loop_stack(v_row).pkgname;
               IF debugging
               THEN
                  pl ('loopstart pkgname used from row ' ||
                     v_row || ' is ' || v_pkgname);
               END IF;

               IF same_as_outer_loop (loopstate_in.statevar, loop_stack(v_row).statevar)
               THEN
                  retval := v_row; -- Use row number from outer loop row.
               ELSE
                  v_row := loop_stack(v_row).currrow;

                  IF debugging
                  THEN
                     pl ('getting starting_row from loop_stack currrow' || '-' || v_row);
                  END IF;

                  PLGvar.assign (
                     v_pkgname || '.' || v_outer ||
                        '(' || TO_CHAR (v_row) || ').starting_row',
                     retval, dyncur);
               END IF;
            END IF;
         END IF;

         IF retval IS NULL
         THEN
            PLGvar.assign
               (loopstate_in.pkgname || '.' ||
                loopstate_in.statevar || '.FIRST', retval, dyncur);
         END IF;
      END IF;

      RETURN retval;
   END;

   FUNCTION loopnext (row_in IN INTEGER) RETURN INTEGER
   IS
      retval PLS_INTEGER;
   BEGIN
      IF alias_loop (loopstate.statevar)
      THEN
         retval := strbyname (
            PLGgenv.genrec.driver,
            loopstate.statevar || '_NEXT',
            sch => PLGgenv.genrec.schema,
            context_in => curlooprow);
      ELSE
         PLGvar.assign (
            loopstate.pkgname || '.' || loopstate.statevar || '.NEXT ('
            || TO_CHAR (row_in) || ')', retval, dyncur);
      END IF;

      RETURN retval;
   END;

   FUNCTION loopend (loopstate_in IN state_stk_type) RETURN INTEGER
   IS
      v_outer PLGadmin.identifier := outer_loop (loopstate_in.statevar);
      v_pkgname PLGadmin.identifier;
      v_row PLS_INTEGER := loop_stack.LAST;
      v_start PLS_INTEGER;
      v_count PLS_INTEGER;
      retval PLS_INTEGER;
   BEGIN
      /* "Manual override" */
      IF loopstate_in.startend_override
      THEN
         retval := loopstate_in.endrow;

      ELSIF offset_loop (loopstate_in.statevar)
      THEN
         retval := strbyname (
            PLGgenv.genrec.driver,
            loopstate_in.statevar || '_END',
            sch => PLGgenv.genrec.schema,
            context_in => curlooprow);
      ELSE
         IF nested_loop (loopstate_in.statevar) AND v_row IS NOT NULL
         THEN
            LOOP
               IF debugging
               THEN
                  pl ('loopend checking ' || loop_stack(v_row).statevar ||
                     ' against ' || v_outer || '-' || v_row);
               END IF;
               EXIT WHEN v_row IS NULL OR loop_stack(v_row).statevar = v_outer;
               v_row := loop_stack.PRIOR (v_row);
            END LOOP;

            IF v_row IS NOT NULL
            THEN
               v_pkgname := loop_stack(v_row).pkgname;
               IF debugging
               THEN
                  pl ('loopend pkgname used from row ' ||
                     v_row || ' is ' || v_pkgname);
               END IF;

               IF same_as_outer_loop (loopstate_in.statevar, loop_stack(v_row).statevar)
               THEN
                  v_start := v_row; -- Use row number from outer loop row.
               ELSE
                  -- 1/99 OLD v_row := loop_stack(v_row).nthrow;
                  v_row := loop_stack(v_row).currrow;

                  PLGvar.assign (
                     v_pkgname || '.' || v_outer ||
                     '(' || TO_CHAR (v_row) ||
                      ').starting_row',
                      v_start, dyncur);
               END IF;

               PLGvar.assign
                  (v_pkgname || '.' || v_outer ||
                  '(' || TO_CHAR (v_row) ||
                   ').column_count',
                   v_count, dyncur);

               retval := v_start + v_count - 1;
            END IF;
         END IF;

         IF retval IS NULL
         THEN
            PLGvar.assign (
               loopstate_in.pkgname || '.' ||
               loopstate_in.statevar || '.LAST', retval, dyncur);
         END IF;
      END IF;

      RETURN retval;
   END;

   /* Alias Cache for improved performance: DISABLED
      Aliases can be changed with SET command.

   PROCEDURE use_aliascache IS BEGIN g_aliascache.inuse := TRUE; END;
   PROCEDURE nouse_aliascache IS BEGIN g_aliascache.inuse := FALSE; END;
   FUNCTION using_aliascache RETURN BOOLEAN IS BEGIN RETURN g_aliascache.inuse; END;

   PROCEDURE aliascache_define (str IN VARCHAR2, delim IN VARCHAR2 := ',')
   IS
      v_row PLS_INTEGER;
   BEGIN
      g_aliascache.currinuse := g_aliascache.inuse AND str IS NOT NULL;
      g_aliascache.nmtab.DELETE;
      g_aliascache.sctab.DELETE;

      IF g_aliascache.currinuse
      THEN
         PLGprs.string (str, g_aliascache.nmtab, delim, PLGprs.c_word, usehash => TRUE);
      END IF;
   END;

   FUNCTION inaliascache (str IN VARCHAR2) RETURN BOOLEAN
   IS
      v_row PLS_INTEGER := PLGhash (str);
   BEGIN
      RETURN g_aliascache.nmtab.EXISTS (v_row);
   END;

   PROCEDURE aliascache_setalias (orig IN VARCHAR2, subst IN VARCHAR2)
   IS
      v_row PLS_INTEGER;
   BEGIN
      IF g_aliascache.currinuse
      THEN
         v_row := PLGhash (orig);

         IF g_aliascache.nmtab.EXISTS (v_row) AND
            NOT g_aliascache.sctab.EXISTS (v_row)
         THEN
            g_aliascache.sctab(v_row) := subst;
         END IF;
      END IF;
   END;
   */

   PROCEDURE initcur (cur_inout IN OUT INTEGER) IS
   BEGIN
      IF NOT DBMS_SQL.IS_OPEN (cur_inout)
      THEN
         cur_inout := DBMS_SQL.OPEN_CURSOR;
      END IF;
   EXCEPTION
      WHEN invalid_cursor
      THEN
         cur_inout := DBMS_SQL.OPEN_CURSOR;
   END;

   FUNCTION strbyname (
      drv IN VARCHAR2,
      name_in IN VARCHAR2,
      op IN VARCHAR2 := objtagopen,
      cl IN VARCHAR2 := objtagclose,
      sch IN VARCHAR2 := NULL,
      context_in IN VARCHAR2 := NULL,
      expandalias_only IN BOOLEAN := FALSE)
   RETURN VARCHAR2
   IS
      v_name PLGadmin.identifier := UPPER (LTRIM (RTRIM (name_in, cl), op));
      v_qualifier PLGadmin.identifier := NULL;
      v_coltype PLGadmin.identifier := NULL;
      v_srcrec PLGdoir.src_rectype;
      v_loc PLS_INTEGER;
      v_sch PLGadmin.identifier := NVL (sch, c_schema);
      need_alias BOOLEAN := TRUE;
      null_name EXCEPTION;
      retval PLGadmin.dbmaxvc2;
   BEGIN
   --tmr_start;
      initcur (dyncur);

      IF name_in IS NULL
      THEN
         retval := name_in;
         RAISE null_name;
      END IF;

      IF debugging
      THEN
         pl ('STRBYNAME: name ' || name_in || ' with op/cl = ' ||
            op ||  cl || ' context ' || context_in);
      END IF;

      IF op = aliasopen
      THEN
         retval :=
            PLGdoir.alias (
               drv,
               PLGgenv.genrec.objname,
               LOWER (v_name),
               sch => v_sch);

         /* Not in use...aliases are too dynamic
         IF g_aliascache.currinuse
         THEN
            v_loc := PLGhash (LOWER (v_name));
            IF g_aliascache.sctab.EXISTS (v_loc)
            THEN
               retval := g_aliascache.sctab(v_loc);
               need_alias := FALSE;
            END IF;
         END IF;

         IF need_alias
         THEN
            retval :=
               PLGdoir.alias (
                  drv,
                  PLGgenv.genrec.objname,
                  LOWER (v_name),
                  sch => v_sch);
         END IF;
         */
      ELSIF op = objtagopen
      THEN
         /* Support qualified notation like nonpkycol.colname
            and unique_index.index_name or nested loops. */

         IF escaping (v_name) -- slower: SUBSTR (v_name, 1, 1) = '!'
         THEN
            /* Leave intact and execute as is. */
            v_qualifier := NULL;
         ELSE
            v_loc := INSTR (v_name, '.');
            IF v_loc > 0
            THEN
               v_qualifier := SUBSTR (v_name, 1, v_loc-1);
               v_name := SUBSTR (v_name, v_loc+1);
            END IF;
         END IF;

         /* The name is used to find the source of data for
            this named element. */
         v_srcrec := PLGdoir.srcinfo (drv, v_name, raise_exc => FALSE);

         IF debugging
         THEN
            pl ('Srcrec string/type: ' || v_srcrec.srcstring);
         END IF;

         IF v_srcrec.srcstring IS NULL
         THEN
            DECLARE
               v_boolean BOOLEAN;
            BEGIN
               /* Are we supposed to avoid any processing? */
               IF escaping (v_name)
               THEN
                  /* Try a direct PL/SQL translation */
                  v_srcrec.plsql_command := SUBSTR (v_name, 2);
                  retval := valbyname (v_srcrec.plsql_command, dyncur);

               /* Try to grab the value from the qualified array. */
               ELSIF v_qualifier IS NOT NULL
               THEN
                  /* Validate the array and convert the array name. */

                  PLGdoir.validate_isarray (
                     PLGgenv.genrec.driver,
                     v_qualifier,
                     retval); /* dummy value, just temporary usage, overwritten below */

                  v_loc := PLGgen.genstate (
                     genrec_ptr (v_qualifier)).currrow;

                  IF debugging
                  THEN
                     pl ('Array validated as ' || v_qualifier ||
                        ' with name ' || v_name || ' and state pointer ' || v_loc);
                  END IF;

                  v_srcrec.plsql_command :=
                     curlooprow (v_qualifier, v_loc, retval) || v_name;

                  IF debugging
                  THEN
                     pl ('Get valbyname for array element from ' ||
                        v_srcrec.plsql_command);
                  END IF;

                  retval := valbyname (v_srcrec.plsql_command, dyncur);

               /* Use the current context array information instead. */
               ELSE         
                  v_srcrec.plsql_command := context_in || v_name;
                  retval := valbyname (v_srcrec.plsql_command, dyncur);
               END IF;
            EXCEPTION
               /* Might have failed cause it's a boolean and could not
                  be converted implicitly. See if it is a field in the
                  array and then treat it as a potential (and "unregistered")
                  PLGdoir boolean function call. */
               WHEN plsql_compile_error
               THEN   
                  BEGIN
                     PLGvar.assign (
                        v_srcrec.plsql_command,
                        v_boolean,
                        dyncur);
                     retval := PLGbool.stg (v_boolean, PLGdoir.c_truefalse);
                  EXCEPTION
                     WHEN OTHERS 
                     THEN  
                        BEGIN
                           PLGvar.assign (
                              PLGdoir.func_call (
                                 v_name,
                                 PLGgenv.genrec.driver,
                                 PLGgenv.genrec.objname,
                                 PLGgenv.genrec.schema),
                              v_boolean,
                              dyncur,
                              use_plgerr => FALSE);
                           retval := PLGbool.stg (v_boolean, PLGdoir.c_truefalse);
                        EXCEPTION
                        WHEN plsql_compile_error THEN
                           PLGerr.reset;
                           PLGerr.raise (
                              PLGerr.undefined_objtag,
                              'Unable to translate "' || v_name || '"'
                              );
                        END;
                  END;
            END;

         ELSIF PLGdoir.plsql_command_src (drv, v_srcrec)
         THEN
            /* If type = plsql command, then use the plsql_command. Check for [objname]
               and [schema] and do the appropriate binds before executing the dynamic
               PL/SQL. Just call this program recursively to do replacements...
            */
            DECLARE
               v_boolean BOOLEAN := TRUE;
               v_number NUMBER;
            BEGIN
               /* RECURSION */
               IF debugging
               THEN
                  pl ('Before substitution: ' || v_srcrec.plsql_command);