CREATE OR REPLACE PACKAGE apkgenr
AS
/*
Created by Edward Stoever for Fuller Theological Seminary on April 8, 2004
to support the Development Department
*/

--
--The flatten function returns results from any query in a long text string,
--deliminated with the text that the user defines
--
   FUNCTION flatten (sql_text IN VARCHAR2, delim IN VARCHAR2 default ',')
      RETURN VARCHAR2;

END;
/


CREATE OR REPLACE PACKAGE BODY apkgenr
AS
   TYPE varchar2_table IS TABLE OF VARCHAR2 (4000)
      INDEX BY BINARY_INTEGER;

   g_number_of_columns   DBMS_SQL.number_table;

   PROCEDURE define_all (p_cursor IN INTEGER)
   AS
      l_columnvalue   VARCHAR2 (4000);
      l_desctbl       DBMS_SQL.desc_tab;
      l_colcnt        NUMBER;
   BEGIN
      DBMS_SQL.describe_columns (p_cursor, l_colcnt, l_desctbl);

      FOR i IN 1 .. l_colcnt
      LOOP
         DBMS_SQL.define_column (p_cursor, i, l_columnvalue, 2000);
      END LOOP;

      g_number_of_columns (p_cursor) := l_colcnt;
   END;

   FUNCTION fetch_row (p_cursor IN INTEGER)
      RETURN varchar2_table
   IS
      l_return   varchar2_table;
   BEGIN
      FOR i IN 1 .. g_number_of_columns (p_cursor)
      LOOP
         l_return (i) := NULL;
         DBMS_SQL.column_value (p_cursor, i, l_return (i));
      END LOOP;

      RETURN l_return;
   END;

   FUNCTION flatten (sql_text IN VARCHAR2, delim IN VARCHAR2)
      RETURN VARCHAR2
   IS
      l_thecursor       INTEGER         DEFAULT DBMS_SQL.open_cursor;
      l_status          INTEGER;
      l_data            varchar2_table;
      result_string     VARCHAR2 (5500) := NULL;
      result_too_long   EXCEPTION;
   BEGIN
      DBMS_SQL.parse (l_thecursor,
                      REPLACE (sql_text, '"', ''''),
                      DBMS_SQL.native
                     );
      define_all (l_thecursor);
      l_status := DBMS_SQL.EXECUTE (l_thecursor);

      WHILE (DBMS_SQL.fetch_rows (l_thecursor) > 0)
      LOOP
         l_data := fetch_row (l_thecursor);

         FOR i IN 1 .. l_data.COUNT
         LOOP
            -- dbms_output.put_line( l_data(i) );
            result_string := result_string || l_data (i) || delim;
         END LOOP;

         IF LENGTH (result_string) > 5000
         THEN
            RAISE result_too_long;
         END IF;
      END LOOP;

      result_string :=
             SUBSTR (result_string, 1, LENGTH (result_string) - LENGTH (delim));
      --drops last deliminator
      DBMS_SQL.close_cursor (l_thecursor);
      RETURN result_string;
   EXCEPTION
      WHEN result_too_long
      THEN
	        DBMS_SQL.close_cursor (l_thecursor);
         result_string := 'OUTPUT EXCEEDS LIMIT OF 5000 CHARACTERS!';
         RETURN result_string;
      WHEN OTHERS
      THEN
	        DBMS_SQL.close_cursor (l_thecursor);
         RAISE;
   END;
END apkgenr;
/