Databases
StackOverflow2013
Postsdbo
POTemporary tables in stored procedure for Oracle
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POTemporary tables in stored procedure for Oracle
    primarykey
    data
    text
    <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>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USMozenRath
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POTemporary tables in stored procedure for Oracle
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POTemporary tables in stored procedure for Oracle
      1. USMozenRath
      1. VTFavorite
    3. VO
      singulars
      1. POTemporary tables in stored procedure for Oracle
      1. This table or related slice is empty.
      1. VTUpMod
    1. CODo you have permissions to create database level types such as a nested table type?
      singulars
      1. POTemporary tables in stored procedure for Oracle
      1. USOllie
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload