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.
Thanks in advance!
`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 < 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 > 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 < 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 > 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;
`
You can try, as Ollie says, with Collections. I take only your LEADS_DELETED table.
CREATE OR REPLACE TYPE Collection_LEADS_DELETED;
CREATE OR REPLACE TYPE TypeObject_LEADS_DELETED
AS OBJECT (ID NUMBER(19));
FUNCTION your_function
RETURN Collection_LEADS_DELETED PIPELINED
IS
type t_array is table of number
index by binary_integer;
v_data t_array;
v_cont integer;
CURSOR cur_your_cursor
IS
SELECT DISTINCT LEADSEQ
FROM ASSIGNMENT
WHERE SEQID IN (SELECT ID FROM ASSIGNMENTS_DELETED);
BEGIN
v_cont := 0;
FOR i in cur_your_cursor
LOOP
v_cont := v_cont + 1;
v_data (v_cont) := i.LEADSEQ;
END LOOP;
For x in 1 .. v_data.count
loop
pipe row (TypeObject_LEADS_DELETED(v_data(x)));
end loop;
RETURN;
END your_function;
Then, you can call this function anywhere, for example:
select * from table(your_function);
If you can't create Object Types (you have no grants, for example), as your temporary tables seems only an array of integers, you could use only the first part, avoiding returning anything or pipelining or creating types outside your procedure. In v_data variable you had all your desired ID's. You can use it, for example, something like this:
For x in 1 .. v_data.count
LOOP
insert into ASSIGNMENTS_DELETED
select SEQID FROM ASSIGNMENT
WHERE LEADSEQ IN (v_data(x))
AND SEQID NOT IN
(SELECT SEQID FROM ASSIGNMENT
WHERE ROLE = 6 AND STATUS = 8
AND CLOSINGTIME < closed);
END LOOP;