title: ORACLE: can we create global temp tables or any tables in stored proc?
body: <p>below is the stored proc I wrote:</p>
  
  <pre><code>create or replace procedure test005
  as
  begin
  
  CREATE GLOBAL TEMPORARY TABLE TEMP_TRAN 
  ( 
  COL1 NUMBER(9), 
  COL2 VARCHAR2(30), 
  COL3 DATE 
  ) ON COMMIT PRESERVE ROWS 
  
  /
  INSERT INTO TEMP_TRAN VALUES(1,'D',sysdate); 
  
  INSERT INTO TEMP_TRAN VALUES(2,'I',sysdate); 
  
  INSERT INTO TEMP_TRAN VALUES(3,'s',sysdate); 
  
  COMMIT;
  end;
  </code></pre>
  
  <p>when i executed it , i get an error message mentioning:</p>
  
  <pre><code>create or replace procedure test005
  as
  begin
  
  CREATE GLOBAL TEMPORARY TABLE TEMP_TRAN 
  ( 
  COL1 NUMBER(9), 
  COL2 VARCHAR2(30), 
  COL3 DATE 
  ) ON COMMIT PRESERVE ROWS 
  
  /
  INSERT INTO TEMP_TRAN VALUES(1,'D',sysdate); 
  
  INSERT INTO TEMP_TRAN VALUES(2,'I',sysdate); 
  
  INSERT INTO TEMP_TRAN VALUES(3,'s',sysdate); 
  
  COMMIT;
  end;
  Error at line 1
  ORA-00955: name is already used by an existing object
  
  Script Terminated on line 1.
  </code></pre>
  
  <p>I tried to drop the TEMP_TRAN and it says table doesn't exist. So there is no TEMP_TRAN table existed in system. why am I getting this error? I am using TOAD to create this stored proc.</p>
  
  <p>Any help would be highly appreciated.</p>