CODEWITHSUNDEEP
oracle-database
Rajaram Shelar
Rajaram Shelar

Reputation: 7877

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

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?

Upvotes: 14

Views: 179600

Answers (5)

Enrico Cairo
Enrico Cairo

Reputation: 1

CREATE OR REPLACE PROCEDURE myproc IS
BEGIN

    CREATE GLOBAL TEMPORARY TABLE temp (id NUMBER(10)) ON COMMIT DELETE ROWS AS
        SELECT 10 FROM dual;

END;
/

Upvotes: -2

LAV KUMAR
LAV KUMAR

Reputation: 39

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;

Upvotes: 2

swimswithbricks
swimswithbricks

Reputation: 9

I have edited this answer as it was wrong. I am a recent MSSQL convert and because of the way oracle implements global temp tables, if you really DO need to use temp tables, creating them once and leaving them there is the way to go. Unless you use dynamic sql in your procs exclusively (have fun debugging), you will not be able to successfully compile your package unless the tables referenced already exist. Oracle validates any objects referenced in methods that you attempt to compile, which is why you got the 942 error. I love the way Oracle manages scope with these global temp tables. That, alone, sold me on the idea.

Upvotes: 0

Swapni and Avinash
Swapni and Avinash

Reputation: 19

Use this

Create of 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;

Upvotes: -1

tbone
tbone

Reputation: 15473

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;

Upvotes: 18

Related Questions