How to create and use temporary table in oracle stored procedure?

ghz 1years ago ⋅ 8085 views

Question

I want to create temporary table in stored procedure and access it in the same but I got error that ORA-00942:Table or view does not exists. Following is the procedure that i tried,

Create procedure myproc
  IS
  stmt varchar2(1000);
  BEGIN
  stmt:='CREATE GLOBAL TEMPORARY table temp(list if columns) ON COMMIT DELETE ROWS';

  execute immediate stmt;

  insert into temp values('list of column values');

 END;

This is the way I used to create temporary table but I got error, is there any other way to perform this task?

Answer

Just create it first (once, outside of your procedure), and then use it in your procedure. You don't want to (try to) create it on every call of the procedure.

create global temporary table tmp(x clob)
on commit delete rows;

create or replace procedure...
-- use tmp here
end;
#oracle