Select to temporary table in Oracle

Question

I'm trying to do something like the following,

select * into temp from (select * from student); 

This gives me the following error:

 ERROR at line 1: ORA-00905: missing keyword 

In my real example, the subquery (select * from student) is more complex.

I want to use this in a stored procedure, so I don't want to create a table. I just want my code to become more readable using a temporary table.

Answer 1

Then maybe you need to do something like this:

 declare type t_temp_storage is table of student%rowtype; my_temp_storage t_temp_storage; begin select * bulk collect into my_temp_storage from student; for i in 1..my_temp_storage.count loop dbms_output.put_line('here I am '||my_temp_storage(i).stuid); end loop; end; 

Answer 2

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

  CREATE TABLE temp as SELECT * FROM student; 

Answer 3

You do not select a "temporary table". If you want to insert into the temporary table from the selection results:

 insert into temp select * from student;