-- Create table

create table STOCKTABLE

(

  TICKER      VARCHAR2(10),

  TRADE_DATE  DATE,

  OPEN_PRICE  NUMBER,

  CLOSE_PRICE NUMBER

)

tablespace JTORDER

  pctfree 10

  initrans 1

  maxtrans 255

  storage

  (

    initial 64

    minextents 1

    maxextents unlimited

  );

-- Create table

create table TICKERTABLE

(

  TICKER    VARCHAR2(10),

  PRICEDATE DATE,

  PRICETYPE VARCHAR2(1),

  PRICE     NUMBER

)

tablespace JTORDER

  pctfree 10

  initrans 1

  maxtrans 255

  storage

  (

    initial 64

    minextents 1

    maxextents unlimited

  );

CREATE TYPE TickerType AS OBJECT(

     ticker VARCHAR2(10),

     pricedate DATE,

     pricetype VARCHAR2(1),

     price NUMBER);

CREATE TYPE TickerTypeSet AS TABLE OF TickerType;

CREATE PACKAGE refcur_pkg

IS

   TYPE refcur_t IS REF CURSOR RETURN StockTable%ROWTYPE;

END refcur_pkg;

CREATE FUNCTION stockpivot(dataset refcur_pkg.refcur_t)

         RETURN TickerTypeSet

IS

    l_row_as_object tickertype := tickertype(NULL,null,null,null);

    l_row_from_query dataset%ROWTYPE;

    retval tickertypeset := tickertypeset();

BEGIN

    LOOP

           FETCH dataset

                 INTO l_row_from_query;

           EXIT WHEN dataset%NOTFOUND;

           l_row_as_object.ticker := l_row_from_query.ticker;

           retval.EXTEND;

           retval(retval.LAST) := l_row_as_object;

    END LOOP;

    CLOSE dataset;

    RETURN retval;

END stockpivot;

BEGIN

    INSERT INTO tickertable

         select * from TABLE (stockpivot(CURSOR(SELECT * from stocktable)));

END;