Search code examples
oraclestored-proceduresplsql

how to save a query result in a temporary table within a procedure

I'm quite new at Oracle so I apologize in advance for the simple question. So I have this procedure, in which I run a query, I want to save the query result for further use, specifically I want run a for loop which will take row by row my selection and copy some of the values in another table. The purpose is to populate a child table (a weak entity) starting from a parent table. For the purpose let's imagine I have a query:

select *
from tab
where ...

now I want to save the selection with a local scope and therefore with a lifespan confined to the procedure itself (like a local variable in a C function basically). How can I achieve such a result?

Basically I have a class schedule table composed like this:

Schedule
--------------------------------------------------------
subject_code | subject_name | class_starting_date | starting hour | ending hour | day_of_week

so I made a query to get all the subjects scheduled for the current academic year, and I need to use the function next_day on each row of the result-set to populate a table of the actual classes scheduled for the next week.

My thought was: I get the classes that need to be scheduled for the next week with a query, save the result somewhere and then through a for loop using next_day (because I need the actual date in which the class take place) populate the "class_occurence" table. I'm not sure that this is the correct way of thinking, there could be something to perform this job without saving the result first, maybe a cursor, who knows...

Solution

  • Global Temporary tables are a nice solution.. As long as you know the structure of the data to be inserted (how many columns and what datatype) you can insert into the global temp table. Data can only be seen by the session that does the inserts. Data can be dropped or committed by using some of the options.

    CREATE GLOBAL TEMPORARY TABLE my_temp_table (
  column1  NUMBER,
  column2  NUMBER
) ON COMMIT DELETE ROWS;

    This has worked great for me where I need to have data aggregated but only for a short period of time.

    Edit: the data is local and temporary, the temp table is always there. If you want to have the table in memory in the procedure that is another solution but somewhat more sophisticated.