-----------------------------------------------------------
--        name  pivottable
--     version  0.0
-- description  This package gives an example of how to 
--              pivot a result set in PL/SQL.
--    synopsis  The package contains a function that 
--              selects rows from a table, sticks them
--              together end to end, and returns the 
--              result as a single row. The code also
--              contains the 'select' statement with the
--              'table' function that converts the output
--              from the package function into a result
--              set. 
--       usage  The last line of the code below shows
--              how to call the package.
--    comments  Based on a package I wrote that pivots 
--              rows from an XML document. That is why
--              the delimiters are XML tags. The cursor
--              assumes that there is a table containing
--              XML.
--   copyright  Copyright David Clement 2006. 
--              All rights reserved.
--    creation  25 May 2006
--      author  David Clement
--     changes
----------------------------------------------------------

----------------------------------------------------------
-- public specifications for package
----------------------------------------------------------
CREATE OR REPLACE PACKAGE pkg_pivottable
AS

  TYPE xml_record_type 
  IS RECORD (xml_id VARCHAR2 (64),
    xml_content VARCHAR2 (32767)
  );

  TYPE xml_table_type 
  IS TABLE OF xml_record_type;

  FUNCTION xml_pivot  
    (p_xml_id IN VARCHAR2)
  RETURN xml_table_type
  PIPELINED;
  
END pkg_pivottable;
/

-----------------------------------------------------------
-- private elements of package
-----------------------------------------------------------
CREATE OR REPLACE PACKAGE BODY pkg_pivottable
AS

  FUNCTION xml_pivot 
    (p_xml_id IN VARCHAR2)
  RETURN xml_table_type
  PIPELINED
  IS
    ret_row pkg_pivottable.xml_record_type;

    CURSOR cur (p_xid IN VARCHAR2)
    IS SELECT xml_content
      FROM xml_contents_table -- Table is assumed to exist
      WHERE xml_id = p_xid;
  BEGIN
    ret_row.xml_id := p_xml_id;
    FOR cur_row IN cur (p_xml_id)
    LOOP 
      IF ret_row.xml_content IS NULL THEN
        ret_row.xml_content := '<xml_content>' 
          || CHR (10);
      END IF;
      ret_row.xml_content := ret_row.xml_content 
        || cur_row.xml_content;
    END LOOP;
    IF ret_row.xml_content IS NOT NULL THEN
      ret_row.xml_content := ret_row.xml_content
        || '</xml_content>';
    END IF;
    PIPE ROW (ret_row);

  END xml_pivot;

END pkg_pivottable;
/

-- After creating the package as shown above, you could
-- call it as shown below.
select xml_content 
from table (pkg_pivottable.xml_pivot ('&testxmlid'));