How to create and use temporary table in oracle stored procedure?
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;
Create or replace procedure myprocedure
is
stmt varchar2(1000);
stmt2 varchar2(1000);
begin
stmt := 'create global temporary table temp(id number(10))';
execute immediate stmt;
stmt2 := 'insert into temp(id) values (10)';
execute immediate stmt2;
end;