how to convert global temp table to collection/type in oracle procedure

Question

I have a procedure that is using a temp table. I want to get rid of the temp table and use a collection to remove I/O. it has about 5000 records.

I want to insert data into this collection then I want to access the collection like:

select * from table(my_type_for_gtt)

I could not find an example of this. I'm confused, do I have to first create a record type and then create as table of?

can someone please show a quick small example?

Answer 1

You are heading in the right direction - first create your types

CREATE TYPE myEntry
AS
OBJECT
  (attr1 NUMBER,
   attr2 VARCHAR2(20)
  );

CREATE TYPE myCollection AS TABLE OF myEntry;

Next some example functions returning 'rows' of your collection

CREATE OR REPLACE FUNCTION ExampleMyCollection1
RETURN myCollection  
IS
   lCol myCollection := myCollection(); /* Must initialise empty collection */
BEGIN
    lCol.EXTEND(1000);
    /* Populate the collection entries with objects */
    FOR i IN 1..1000 LOOP
        lCol(i) := myEntry(i,'An entry for '||i);
    END LOOP;
    RETURN lCol;
END ExampleMyCollection1;

SELECT * FROM TABLE(ExampleMyCollection1);

Variation - this time we use pipelining, so that the results are returned to the query as they are created. Note that despite being a function, there is no end RETURN for a PIPELINED function.

CREATE OR REPLACE FUNCTION ExampleMyCollection2
RETURN myCollection PIPELINED
IS
BEGIN
    FOR i IN 1..1000 LOOP
        PIPE ROW(myEntry(i,'An entry for '||i));
    END LOOP;
END ExampleMyCollection2;

SELECT * FROM TABLE(ExampleMyCollection2);

To replace your temp table with purely in-memory data, you will need something to store your collection in - i.e. a package with state.

CREATE OR REPLACE PACKAGE pMyCollection
AS
   PROCEDURE AddEntry(entry IN myEntry);

   FUNCTION fCurrentCollection RETURN myCollection;

   PROCEDURE ClearEntries;

END pMyCollection;

CREATE OR REPLACE PACKAGE BODY pMyCollection
AS
   /* Stateful variable to hold the collection */
   pCollection myCollection := myCollection();

   PROCEDURE AddEntry(entry IN myEntry)
   IS
   BEGIN
      pCollection.EXTEND;
      pCollection(pCollection.LAST) := entry;
   END;

   PROCEDURE ClearEntries 
   IS
   BEGIN
      pCollection.DELETE;
   END ClearEntries;

   FUNCTION fCurrentCollection
   RETURN myCollection
   IS
   BEGIN
      /* Return whole collection - we could use pipelining and parameters to return partial elements */
      RETURN pCollection;
   END fCurrentCollection;

END pMyCollection;