str2list.pkg

CREATE OR REPLACE PACKAGE str2list
IS
   PROCEDURE parse (
      str IN VARCHAR2,
      delim IN VARCHAR2,
      pkg IN VARCHAR2,
      tab IN VARCHAR2,
      datatype IN VARCHAR2 := 'VARCHAR2(32767)',
      extend_needed BOOLEAN := FALSE
   );

   PROCEDURE parse (
      str IN VARCHAR2,
      delim IN VARCHAR2,
      pkg IN VARCHAR2,
      appendproc IN VARCHAR2, /* pkg.appendproc (oneval); */
      deleteproc IN VARCHAR2,  /* pkg.deleteproc (onerow); or pkg.deleteproc; */
      datatype IN VARCHAR2
      );

   PROCEDURE showlist (
      pkg IN VARCHAR2,
      tab IN VARCHAR2
   );

   PROCEDURE showlist (
      pkg IN VARCHAR2,
      firstrowproc IN VARCHAR2,
      nextrowproc IN VARCHAR2,
      getvalfunc IN VARCHAR2,
      showproc IN VARCHAR2 := 'DBMS_OUTPUT.PUT_LINE',
      datatype IN VARCHAR2 := 'VARCHAR2(32767)'
   );
END str2list;
/
CREATE OR REPLACE PACKAGE BODY str2list
IS
procedure disperr (str in varchar2) is
begin
   p.l ('Compilation/Execution Error:');
   p.l (SQLERRM);
   p.l ('In:');
   p.l (str);
end;

   PROCEDURE parse (
      str IN VARCHAR2,
      delim IN VARCHAR2,
      pkg IN VARCHAR2,
      tab IN VARCHAR2,
      datatype IN VARCHAR2 := 'VARCHAR2(32767)',
      extend_needed BOOLEAN := FALSE
   )
   IS
      tabname VARCHAR2 (100) := pkg || '.' || tab;
      dynblock VARCHAR2 (32767);
      extstring VARCHAR2 (100);
   BEGIN
      IF extend_needed
      THEN  
         extstring := tabname || '.EXTEND;'
      END IF;
      
      dynblock :=
         'DECLARE 
            v_loc PLS_INTEGER;
            v_startloc PLS_INTEGER := 1;
            v_item ' || datatype || ';
          BEGIN ' ||
             tabname || '.DELETE;
             IF :str IS NOT NULL 
             THEN 
                LOOP
                   v_loc := INSTR (:str, :delim, v_startloc);
                   IF v_loc = v_startloc
                   THEN
                      v_item := NULL;
                   ELSIF v_loc = 0
                   THEN
                      v_item := SUBSTR (:str, v_startloc);
                   ELSE
                      v_item := SUBSTR (:str, v_startloc, v_loc - v_startloc);
                   END IF;' ||
                  
                   extstring || tabname || '(' || tabname || '.LAST) 
                      := v_item;
                      
                   IF v_loc = 0
                   THEN
                      EXIT;
                   ELSE 
                      v_startloc := v_loc + 1;
                   END IF;
                END LOOP;
             END IF;
          END;';
          
      EXECUTE IMMEDIATE dynblock USING str, delim;
   EXCEPTION
      WHEN OTHERS 
      THEN 
         disperr (dynblock);   
   END;

   PROCEDURE parse (
      str IN VARCHAR2,
      delim IN VARCHAR2,
      pkg IN VARCHAR2,
      appendproc IN VARCHAR2,
      deleteproc IN VARCHAR2,
      datatype IN VARCHAR2
   )IS
      dynblock VARCHAR2 (32767);
   BEGIN
      dynblock :=
         'DECLARE 
            v_loc PLS_INTEGER;
            v_startloc PLS_INTEGER := 1;
            v_item ' || datatype || ';
          BEGIN 
             ' || pkg || '.' || deleteproc || ';
             IF :str IS NOT NULL 
             THEN 
                LOOP
                   v_loc := INSTR (:str, :delim, v_startloc);
                   IF v_loc = v_startloc
                   THEN
                      v_item := NULL;
                   ELSIF v_loc = 0
                   THEN
                      v_item := SUBSTR (:str, v_startloc);
                   ELSE
                      v_item := SUBSTR (:str, v_startloc, v_loc - v_startloc);
                   END IF;' ||
                  pkg ||
                  '.' ||
                  appendproc ||
                  '( v_item);
                   IF v_loc = 0
                   THEN
                      EXIT;
                   ELSE 
                      v_startloc := v_loc + 1;
                   END IF;
                END LOOP;
             END IF;
          END;';
      EXECUTE IMMEDIATE dynblock USING str, delim;
   EXCEPTioN
   when others then disperr (dynblock);
   END;

   PROCEDURE showlist (
      pkg IN VARCHAR2,
      tab IN VARCHAR2
   )
   IS
      tabname VARCHAR2 (100) := pkg || '.' || tab;
      dynblock VARCHAR2 (32767);
   BEGIN
      dynblock :=
         'DECLARE 
            indx PLS_INTEGER := ' || tabname ||
                  '.FIRST;
            v_startloc PLS_INTEGER := 1;
            v_item VARCHAR2(32767);
          BEGIN 
             LOOP
                EXIT WHEN indx IS NULL;
                DBMS_OUTPUT.PUT_LINE (' ||
                  tabname ||
                  '(indx));
                indx := ' ||
                  tabname ||
                  '.NEXT (indx);
             END LOOP;
          END;';
      EXECUTE IMMEDIATE dynblock;
   EXCEPTioN
   when others then disperr (dynblock);
   END;
   
   PROCEDURE showlist (
      pkg IN VARCHAR2,
      firstrowproc IN VARCHAR2,
      nextrowproc IN VARCHAR2,
      getvalfunc IN VARCHAR2,
      showproc IN VARCHAR2 := 'DBMS_OUTPUT.PUT_LINE',
      datatype IN VARCHAR2 := 'VARCHAR2(32767)'   )
   IS
      dynblock VARCHAR2 (32767);
   BEGIN
      dynblock :=
         'DECLARE 
            indx PLS_INTEGER := ' || pkg ||
                  '.' || firstrowproc || ';
            v_startloc PLS_INTEGER := 1;
            v_item ' || datatype || ';
          BEGIN 
             LOOP
                EXIT WHEN indx IS NULL;' ||
                showproc || ' (' ||
                  pkg || '.' || getvalfunc || '(indx));
                indx := ' ||
                  pkg ||
                  '.' || nextrowproc || '(indx);
             END LOOP;
          END;';
      EXECUTE IMMEDIATE dynblock;
   EXCEPTioN
   when others then disperr (dynblock);
   END;
   
END str2list;
/