Temporary table in oracle11g and select multiple rows

Question

In my oracle stored procedure,I need to create temporary table. I want to insert rows in temporary table based on some logic and return this rows from stored procedure.

My database is in oracle 11G. I am using oracle sql developer for creating stored procedure.

Getting below error while trying to fetch multiple rows.

Error(6,4): PLS-00428: an INTO clause is expected in this SELECT statement

CREATE OR REPLACE PROCEDURE TestStoredP AS 
 stmt varchar2(1000);

BEGIN  

  select emp_id,empname INTO AAA,BBB from EMPLOYEE;

   stmt := 'create global temporary table temp(id number(10))';

   EXECUTE IMMEDIATE stmt;  

   stmt := 'insert into temp values(1)';   
   EXECUTE IMMEDIATE stmt;  

   stmt := 'select * from temp';   
   EXECUTE IMMEDIATE stmt;  

   execute immediate ' drop table temp'; 

END TOPS;

Please suggest how to implement this.

The error has nothing to do with it being a temporary table; your dynamic select has no into clause, as the error suggests. You should not create a table (or usually do any DDL) in a procedure. You can use a PL/SQL collection if you really need temporary storage of interim results, or an SQL type, depending on what and how you want to 'return'. You have no out parameters, and you'd usually return data from a function not a procedure, so it isn't clear what you really need. — Alex Poole
This might be useful too, if you're coming from another DBMS where temproary tables were used; you might also find a pipelined function appropriate. But it depends what you're doing really. — Alex Poole

René Nyffenegger René Nyffenegger · Accepted Answer · 2015-01-14T08:11:57

Use a global temporary table:

create global temporary table temp_foo(id number(10));

The values you inserted into the temporary table are only visible within the session that inserted them.

Then the procedure becomes:

CREATE OR REPLACE PROCEDURE TestStoredP AS 

    val number(10);
    aaa emp.emp_id%type;
    bbb emp.empname%type;
BEGIN  

    select emp_id,empname INTO AAA,BBB from EMPLOYEE;

    insert into temp_foo values(1);

    select id into val from temp_foo;

END TOPS;

Now, the error you get (PLS-00428: an INTO clause is expected in this SELECT statement) has nothing to do with your temporary table. In a stored procedure, when you select a value, you need to tell into which variables you want to select it. This is done with the into clause.

Temporary table in oracle11g and select multiple rows

1 Answers