Can Oracle Program Return Results from global Temporary Table

Here's a piece of Oracle code I'm trying to adapt. I've abbr开发者_C百科eviated all the details:

    declare  
    begin  
      loop  
      --do stuff to populate a global temporary table.  I'll call it 'TempTable'  
      end loop;  
    end; 
    /

   Select * from TempTable

Right now, this query runs fine provided I run it in two steps. First I run the program at the top, then I run the select * to get the results.

Is it possible to combine the two pieces so that I can populate the global temp table and retrieve the results all in one step?

Thanks in advance!

Well, for me it depends on how I would see the steps. You are doing a PL/SQL and SQL command. I would rather type in those into a file, and run them in one command (if that could called as a single step for you)...

Something like

file.sql

begin  
 loop  
 --do stuff to populate a global temporary table.  I'll call it 'TempTable'  
 end loop;  
end; 
/
Select * 
from TempTable
/

And run it as:

prompt> sqlplus /@db @file.sql

If you give us more details like how you populate the GTT, perhaps we might find a way to do it in a single step.

Yes, but it's not trivial.

create global temporary table my_gtt
 ( ...  )
on commit preserve rows;

create or replace type my_gtt_rowtype as object
  ( [columns definition] )
/

create or replace type my_gtt_tabtype as table of my_gtt_rowtype
/

create or replace function pipe_rows_from_gtt
  return my_gtt_tabtype 
  pipelined
is
  pragma autonomous_transaction;
  type rc_type is refcursor;
  my_rc rc_type;
  my_output_rec my_gtt_rectype := my_gtt_rectype ([nulls for each attribute]);
begin
  delete from my_gtt;
  insert into my_gtt ...
  commit;
  open my_rc for select * from my_gtt;

  loop
    fetch my_rc into my_output_rec.attribute1, my_output_rec.attribute1, etc;
    exit when my_rc%notfound;
    pipe_row (my_output_rec);
  end loop;
  close my_rc;
  return;
end;
/

I don't know it the autonomous transaction pragma is required - but I suspect it is, otherwise it'll throw errors about functions performing DML.

We use code like this to have reporting engines which can't perform procedural logic build the global temporary tables they use (and reuse) in various subreports.

In oracle, an extra table to store intermediate results is very seldom needed. It might help to make things easier to understand. When you are able to write SQL to fill the intermediate table, you can certainly query the rows in a single step without having to waste time by filling a GTT. If you are using pl/sql to populate the GTT, see if this can be corrected to be pure SQL. That will almost certainly give you a performance benefit.