2

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?

learn_plsql
  • 1,681
  • 10
  • 28
  • 34
  • possible duplicate of [ways to avoid global temp tables in oracle](http://stackoverflow.com/questions/2918466/ways-to-avoid-global-temp-tables-in-oracle) – APC Jul 14 '10 at 23:05
  • Check out my answer to this previous question: http://stackoverflow.com/questions/2918466/ways-to-avoid-global-temp-tables-in-oracle/2918935#2918935 – APC Jul 14 '10 at 23:23
  • I've dumped a quick collections example below, although it duplicates your more detailed answer. Hopefully OP can close this question. – JulesLt Jul 15 '10 at 08:26
  • I/O will only be an issue if you are short on memory. The blocks will be held in the buffer cache and only pushed to disk if you run out of free buffer cache. Also there is no redo generation for inserting in global temp tables. – PenFold Jul 15 '10 at 10:26

1 Answers1

2

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;
JulesLt
  • 1,765
  • 10
  • 9