Select Into A Temporary Table In Oracle

I am trying to do something like the following, select * into temp from (select * from student); It gives me the following error, ERROR at line 1: ORA-00905: missing keyword In m

Solution 1:

Then perhaps you need to do something like this:

declare
   type t_temp_storage istableof student%rowtype;
   my_temp_storage t_temp_storage;
beginselect* bulk collectinto my_temp_storage from student;
   for i in1..my_temp_storage.count
    loop
    dbms_output.put_line('here I am '||my_temp_storage(i).stuid);
   end loop; 
 end;

Solution 2:

If the table temp does not exist, you have to create it.

CREATETABLE temp asSELECT*FROM student;

Solution 3:

You don't "select" into a temp table. If you want to insert into a temp table from the results of a select:

insertinto temp
select*from student;

Solution 4:

I found useful this:

CREATEGLOBAL TEMPORARY TABLE MY_TEMPORARY_TABLE ONCOMMIT PRESERVE ROWSAS (
    SELECT*FROM MY_TABLE WHERE MY_CONDITION
) 

The clause ON COMMIT PRESERVE ROWS overrides the default one (ON COMMIT DELETE ROWS). If you keep the default the records will be deleted at every commit, so if you work with an auto-commit client the records will be deleted immediately after creation. In both cases anyway the table exists only for the duration of the session.