Alternative for a MySQL temporary table in Oracle

Question

I noticed that the concept of temporary tables in these two systems is different, and I have a musing.. I have the following scenario in MySQL:

1. Drop temporary table 'a' if exists
2. Create temporary table 'a'
3. Populate it with data through a stored procedure
4. Use the data in another stored procedure

How can I implement the same scenario in Oracle? Can I (in one procedure preferable) create a temporary table, populate it, and insert data in another (non-temporary) table?

I think that I can use a (global) temporary table which truncates on commit, and avoid steps 1&2, but I need someone else's opinion too.

Answer 1

In Oracle, you very rarely need a temporary table in the first place. You commonly need temporary tables in other databases because those databases do not implement multi-version read consistency and there is the potential that someone reading data from the table would be blocked while your procedure runs or that your procedure would do a dirty read if it didn't save off the data to a separate structure. You don't need global temporary tables in Oracle for either of these reasons because readers don't block writers and dirty reads are not possible.

If you just need a temporary place to store data while you perform PL/SQL computations, PL/SQL collections are more commonly used than temporary tables in Oracle. This way, you're not pushing data back and forth from the PL/SQL engine to the SQL engine and back to the PL/SQL engine.

CREATE PROCEDURE do_some_processing
AS
  TYPE emp_collection_typ IS TABLE OF emp%rowtype;
  l_emps emp_collection_type;

  CURSOR emp_cur
      IS SELECT *
           FROM emp;
BEGIN
  OPEN emp_cur;
  LOOP
    FETCH emp_cur 
     BULK COLLECT INTO l_emps
    LIMIT 100;

    EXIT WHEN l_emps.count = 0;

    FOR i IN 1 .. l_emps.count
    LOOP
      <<do some complicated processing>>
    END LOOP;
  END LOOP;
END;

You can create a global temporary table (outside of the procedure) and use the global temporary table inside your procedure just as you would use any other table. So you can continue to use temporary tables if you so desire. But I can count on one hand the number of times I really needed a temporary table in Oracle.