title: Temporary tables in stored procedure for Oracle
body: <p>I have a stored procedure that runs fine on MS SQL and DB2. But cannot get it to run on Oracle as it uses temporary tables created within the procedure. I would like to know any suggestions on my stored procedure if possible. Please do not suggest execute immediate as i do not have the permission for that. This is a huge procedure so please guide me how i can do this without a temporary table. Please note that creating a temporary table before creating the procedure is also not an option.</p>
  
  <p>Thanks in advance!</p>
  
  <pre><code>`CREATE OR REPLACE PROCEDURE LEAD_PURGE(closed IN DATE,
  oprtr IN INTEGER,
  leadscount OUT INTEGER)
  is
  BEGIN
  
  CREATE TABLE LEADS_DELETED
  (
      ID NUMBER(19),
      PRIMARY KEY (ID)
  );
  
  CREATE TABLE ASSIGNMENTS_DELETED
  (
      ID NUMBER(19),
      PRIMARY KEY (ID)
  );
  
  CREATE TABLE MAPRESULTS_DELETED
  (
      ID NUMBER(19),
      PRIMARY KEY (ID)
  );
  
  CREATE TABLE COMMAND_DELETED
  (
      ID NUMBER(19),
      PRIMARY KEY (ID)
  );
  
  CREATE TABLE PROGRESS_STATUS_DELETED
  (
      ID NUMBER(19),
      PRIMARY KEY (ID)
  );
  
  CREATE TABLE DETAILS_DELETED
  (
      ID NUMBER(19),
      PRIMARY KEY (ID)
  );
  
  CREATE TABLE NEEDS_DELETED
  (
      ID NUMBER(19),
      PRIMARY KEY (ID)
  );
  if oprtr = 0 then
  INSERT INTO ASSIGNMENTS_DELETED
  SELECT SEQID FROM ASSIGNMENT WHERE ROLE = 6 AND STATUS = 8 AND CLOSINGTIME &lt; closed;
  
  
  elsif oprtr = 1 then
  INSERT INTO ASSIGNMENTS_DELETED
  SELECT SEQID FROM ASSIGNMENT WHERE ROLE = 6 AND STATUS = 8 AND EXTRACT(YEAR FROM CLOSINGTIME) = EXTRACT(YEAR FROM closed)
                                                              AND EXTRACT(MONTH FROM CLOSINGTIME) = EXTRACT(MONTH FROM closed)
                                                              AND EXTRACT(DAY FROM CLOSINGTIME) = EXTRACT(DAY FROM closed);
  
  elsif oprtr = 2 then
  INSERT INTO ASSIGNMENTS_DELETED
  SELECT SEQID FROM ASSIGNMENT WHERE ROLE = 6 AND STATUS = 8 AND CLOSINGTIME &gt; closed;
  end if;
  
  INSERT INTO LEADS_DELETED
  SELECT DISTINCT LEADSEQ FROM ASSIGNMENT WHERE SEQID IN (SELECT ID FROM ASSIGNMENTS_DELETED);
  
  if oprtr = 0 then
  insert into ASSIGNMENTS_DELETED
  select SEQID FROM ASSIGNMENT WHERE LEADSEQ IN (SELECT ID FROM LEADS_DELETED) AND 
  SEQID NOT IN (SELECT SEQID FROM ASSIGNMENT WHERE ROLE = 6 AND STATUS = 8 AND CLOSINGTIME &lt; closed);
  
  elsif oprtr = 1 then
  insert into ASSIGNMENTS_DELETED
  select SEQID FROM ASSIGNMENT WHERE LEADSEQ IN (SELECT ID FROM LEADS_DELETED) AND 
  SEQID NOT IN (SELECT SEQID FROM ASSIGNMENT WHERE ROLE = 6 AND STATUS = 8 AND EXTRACT(YEAR FROM CLOSINGTIME) = EXTRACT(YEAR FROM closed)
                                                                           AND EXTRACT(MONTH FROM CLOSINGTIME) = EXTRACT(MONTH FROM closed)
                                                                           AND EXTRACT(DAY FROM CLOSINGTIME) = EXTRACT(DAY FROM closed));
  
  
  elsif oprtr = 2 then
  insert into ASSIGNMENTS_DELETED
  select SEQID FROM ASSIGNMENT WHERE LEADSEQ IN (SELECT ID FROM LEADS_DELETED) AND 
  SEQID NOT IN (SELECT SEQID FROM ASSIGNMENT WHERE ROLE = 6 AND STATUS = 8 AND CLOSINGTIME &gt; closed);
  end if;
  
  SET leadscount = (SELECT COUNT(*) FROM LEADS_DELETED);
  
  INSERT INTO MAPRESULTS_DELETED
  SELECT ID FROM MAPRESULT WHERE ASSIGNMENTSEQ IN (SELECT ID FROM ASSIGNMENTS_DELETED);
  
  INSERT INTO COMMAND_DELETED
  SELECT ID FROM EXECUTERULECOMMAND WHERE MAPRESULTID IN (SELECT ID FROM MAPRESULTS_DELETED);
  
  INSERT INTO PROGRESS_STATUS_DELETED
  SELECT PROGRESS_STATUS_ID FROM COMMAND WHERE ID IN (SELECT ID FROM COMMAND_DELETED);
  
  INSERT INTO DETAILS_DELETED
  SELECT DETAILID FROM LEAD WHERE SEQID IN (SELECT ID FROM LEADS_DELETED);
  
  INSERT INTO NEEDS_DELETED
  SELECT NEEDSID FROM LEAD WHERE SEQID IN (SELECT ID FROM LEADS_DELETED);
  
  
  
  DELETE FROM PROGRESS_STATUS WHERE ID IN (SELECT ID FROM PROGRESS_STATUS_DELETED);
  
  DELETE FROM EXECUTERULECOMMAND WHERE ID IN (SELECT ID FROM COMMAND_DELETED);
  
  DELETE FROM COMMAND WHERE ID IN (SELECT ID FROM COMMAND_DELETED);
  
  DELETE FROM SIMPLECONDITIONAL WHERE RESULT IN (SELECT ID FROM MAPRESULTS_DELETED);
  
  DELETE FROM MAPPREDICATE WHERE ROWBP IN (SELECT ID FROM MAPROW WHERE RESULT IN (SELECT ID FROM MAPRESULTS_DELETED));
  
  DELETE FROM MAPROW WHERE RESULT IN (SELECT ID FROM MAPRESULTS_DELETED);
  
  DELETE FROM MAPRESULT WHERE ID IN (SELECT ID FROM MAPRESULTS_DELETED);
  
  DELETE FROM ASSIGNMENTATTACHMENTS WHERE ASSIGNMENTSEQ IN (SELECT ID FROM ASSIGNMENTS_DELETED);
  
  DELETE FROM LEADOBSERVER WHERE ASSIGNSEQ IN (SELECT ID FROM ASSIGNMENTS_DELETED);
  
  DELETE FROM MAPDESTINATIONS WHERE SUGGESTEDASSIGNID IN 
      (SELECT ID FROM SUGGESTEDASSIGNMENT WHERE ASSIGNMENT_SEQID IN (SELECT ID FROM ASSIGNMENTS_DELETED));
  
  DELETE FROM SUGGESTEDASSIGNMENT WHERE ASSIGNMENT_SEQID IN (SELECT ID FROM ASSIGNMENTS_DELETED);
  
  DELETE FROM PRODUCTINTEREST WHERE LEADSEQ IN (SELECT ID FROM LEADS_DELETED);
  
  
  
  CREATE TABLE SALE_DELETED_EX
  (
      ID NUMBER(19),
      PRIMARY KEY (ID)
  );
  
  INSERT into SALE_DELETED_EX SELECT SALEEXSEQ FROM SALE WHERE SEQID IN (SELECT SALEID FROM LEADSALES WHERE LEADID IN (SELECT ID FROM LEADS_DELETED));
  
  
  DELETE FROM SALE WHERE SEQID IN (SELECT SALEID FROM LEADSALES WHERE LEADID IN (SELECT ID FROM LEADS_DELETED));
  
  
  DELETE FROM SALEEXTENSIONS WHERE 
      SEQID IN (SELECT ID FROM SALE_DELETED_EX);
  
  DELETE FROM LEADSALES WHERE LEADID IN (SELECT ID FROM LEADS_DELETED);
  
  
  DELETE FROM NOTES WHERE OBJECTID IN (SELECT ID FROM NEEDS_DELETED) OR OBJECTID IN (SELECT ID FROM DETAILS_DELETED);
  
  DELETE FROM HISTORYRECORD WHERE OBJECTID IN (SELECT ID FROM DETAILS_DELETED);
  
  DELETE FROM DETAIL WHERE SEQID IN (SELECT ID FROM NEEDS_DELETED UNION SELECT ID FROM DETAILS_DELETED);
  
  DELETE FROM MESSAGES WHERE PROVIDERID IN (SELECT ID FROM LEADS_DELETED);
  
  DELETE FROM ASSIGNMENT WHERE LEADSEQ IN (SELECT ID FROM LEADS_DELETED);
  
  DELETE FROM LEAD WHERE SEQID IN (SELECT ID FROM LEADS_DELETED);
  
  CREATE TABLE LEADS_DELETED_E
  (
      ID NUMBER(19),
      PRIMARY KEY (ID)
  );
  
  InSERT into LEADS_DELETED_E Select SEQID FROM LEADEXTENSIONS WHERE 
      SEQID NOT IN (SELECT LEADEXSEQ FROM LEAD);
  
  DELETE FROM LEADEXTENSIONS WHERE 
      SEQID  IN (SELECT ID FROM LEADS_DELETED_E);
  
  
  DROP TABLE LEADS_DELETED;
  
  DROP TABLE ASSIGNMENTS_DELETED;
  
  DROP TABLE MAPRESULTS_DELETED;
  
  DROP TABLE COMMAND_DELETED;
  
  DROP TABLE PROGRESS_STATUS_DELETED;
  
  DROP TABLE DETAILS_DELETED;
  
  DROP TABLE NEEDS_DELETED;
  
  DROP TABLE LEADS_DELETED_E;
  
  DROP TABLE SALE_DELETED_EX;
  
  COMMIT;
  END LEAD_PURGE;
  </code></pre>
  
  <p>`</p>