Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm in a situation right now. I'm migrating several procedures from Mysql 5.0 to Oracle 11g. Mysql procedures allow me to:
1. Create temporary tables
2. Insert data into temporary tables / Query these tables / Do some processes
3. Drop temporary tables

I've been searching and found I can not create DDL Statements within procedures without EXECUTE IMMEDIATE statement. I tried to create the temporary table with that statement, and immediately insert some values on it but it does not work 'cause the table does not already exist. I tried to create a function which creates a temporary table, then call it from the procedure but the same happens.

I need to achieve the same steps I'm doing with MySQL. This situation happens very often (there are so many procedures with this kind of "issue") and they are quite large.

What options do I have, what do you recommend ?

share|improve this question
3  
You need to forget about creating tables inside your procedure (ddl commits in any case, which is often a showstopper). Look at global temporary tables. –  Mat Feb 7 '13 at 8:20
    
I am not creating normal tables inside my procedure. I'm talking about temporary tables. When you create a temporary table in an Oracle database, it is automatically global. –  Alejandro Bastidas Feb 7 '13 at 13:38
1  
The thing is you are creating normal tables. There are no temporary tables in Oracle except global temporary tables whose structure is permanent (but contents is temporary). Anything else are ordinary, permanent tables that are "expensive" to create and drop. –  Mat Feb 7 '13 at 14:15
    
Thank you very much for your answer. What would you recommend for me to achieve the steps above ? –  Alejandro Bastidas Feb 7 '13 at 14:23
add comment

1 Answer

up vote 4 down vote accepted

It depends on why you are creating the temporary tables in MySQL.

Frequently, people that are creating temporary tables in other databases are doing so in order to work around limitations that don't exist in Oracle where readers don't block writers and writers don't block readers. In other databases, you commonly copy data from a permanent table to a temporary table so that your process doesn't block some other process that needs the same data. Since Oracle provides multi-version read consistency, however, this isn't necessary (or beneficial) in Oracle-- your process can process the data sitting in the real tables without worrying that it is going to block someone else. If that's the situation you're in, the proper response is simply to remove the temporary tables and process the data from the permanent tables.

Assuming that you really need a temporary copy of the data, you can create global temporary tables. You would create these tables outside of your code just like a permanent table and use them just like a permanent table inside your code. The global temporary table ensures that each session can only see the data that session has inserted. The only difference is that you're not dropping and recreating the structure of the table inside your procedure.

Another alternative would be to pull the data into a PL/SQL collection that you work with rather than using a temporary table. PL/SQL collections are stored in the server's PGA (one of Oracle's memory structures) so you generally want to limit the size of the collection particularly if there is a lot of data to process or there could be many sessions processing data simultaneously. You can do something like

DECLARE
  TYPE emp_tbl IS TABLE OF emp%rowtype;
  l_emps emp_tbl;

  CURSOR emp_cur 
      IS SELECT *
           FROM emp;
BEGIN
  OPEN emp_cur;
  LOOP
    -- Fetch 10 rows at a time from the cursor into the collection.
    -- You'd realistically want a larger limit, something between 100 and 1000 generally
    FETCH emp_cur 
     BULK COLLECT INTO l_emps
    LIMIT 10;

    EXIT WHEN l_emps.COUNT = 0;

    -- An example of manipulating the collection in memory
    FOR i IN 1 .. l_emps.COUNT
    LOOP
      l_emps(i).sal := l_emps(i).sal * 2;
    END LOOP;

    -- And an example of using the collection to update a table
    FORALL i IN 1 .. l_emps.COUNT
      UPDATE emp
         SET sal = l_emps(i).sal
       WHERE empno = l_emps(i).empno;
  END LOOP;
END;

Of course, in the example above, it would be far simpler (and more efficient) to simply issue an UPDATE statement that would double every employee's salary.

share|improve this answer
    
I'm new in oracle, and actually I didn't know about "Oracle provides multi-version read consistency". I think there could be my solution. Even if that were not the case, I would create global temporary tables outside procedures. Thank you very much. –  Alejandro Bastidas Feb 7 '13 at 15:43
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.