Temp Tables within Stored Procedure
I'm pretty new to Oracle, but I have been developing in MS SQL for about 15. So I'm still getting use to the syntax and features within Oracle.
I'm trying to create a stored procedure that has two temporary tables within it, and then queries both them tables and inserts the results into a table.
I created the script but when they try to run in on the server it wont run. Can you guys tell me what I'm doing wrong?
Code:
CREATE OR REPLACE PROCEDURE UpdateFIDB_SP
IS
BEGIN
CREATE GLOBAL TEMPORARY TABLE myAAAA
AS
(SELECT AAAA.1111, AAAA.2222, BBBB.3333_EXT, CCCC.4444, DDDD.5555, DDDD.6666, DDDD.7777,
DDDD.8888, AAAA.9999, EEEE.1010, EEEE.1A1A, EEEE.1B1B, FFFF.3333_LO, FFFF.1C1C,
AAAA.1D1D
FROM mySchema.FFFF_07 FFFF
RIGHT OUTER JOIN mySchema.EEEE EEEE ON FFFF.9999 = EEEE.1B1B
RIGHT OUTER JOIN (
mySchema.DDDD DDDD
RIGHT OUTER JOIN mySchema.AAAA AAAA ON DDDD.1D1D = AAAA.1D1D
) ON EEEE.PSPNR = AAAA.9999
LEFT OUTER JOIN mySchema.CCCC CCCC ON AAAA.3333 = CCCC.3333
LEFT OUTER JOIN mySchema.BBBB BBBB ON AAAA.3333 = BBBB.3333_INT
GROUP BY AAAA.1D1D, AAAA.1111, AAAA.2222, BBBB.3333_EXT, CCCC.4444, DDDD.5555, DDDD.6666,
DDDD.7777, DDDD.8888, AAAA.9999, EEEE.1010, EEEE.1A1A, EEEE.1B1B, FFFF.3333_LO,
FFFF.1C1C
ON COMMIT DELETE ROWS);
CREATE GLOBAL TEMPORARY TABLE myGGGG
AS
(SELECT GGGG.1E1E, GGGG.1F1F, GGGG.1G1G, GGGG.1H1H, GGGG.1I1I, GGGG.1J1J,
GGGG.1K1K, GGGG.R1D1D, GGGG.1L1L, GGGG.1M1M, GGGG.1N1N, GGGG.1O1O, GGGG.1P1P,
GGGG.1Q1Q, HHHH.1R1R, IIII.1S1S, IIII.1T1T, IIII.1U1U, IIII.1V1V
FROM mySchema.IIII IIII
INNER JOIN mySchema.GGGG GGGG ON IIII.1K1K = GGGG.1K1K
LEFT OUTER JOIN mySchema.HHHH HHHH ON GGGG.1L1L = HHHH.1W1W
WHERE ( GGGG.1M1M IN ('20', '30') )
AND ( TO_DATE(IIII.1V1V, 'dd-mon-yyyy') = TO_DATE('31-DEC-9999','dd-mon-yyyy') )
AND ( TO_DATE(GGGG.1N1N, 'dd-mon-yyyy') >= TO_DATE('01-Jan-2011','dd-mon-yyyy') )
ON COMMIT DELETE ROWS);
TRUNCATE TABLE FIDB;
INSERT INTO FIDB (1111, 2222, 3333_EXT, 4444, 5555, 6666, 7777, 8888, 9999,
1010, 1A1A, 1B1B,3333_LO, 1C1C, 1D1D, 1E1E, 1F1F, 1G1G,
1H1H, 1I1I, 1J1J, 1K1K, R1D1D, 1L1L, 1M1M, 1N1N,
1O1O, 1P1P, 1Q1Q, 1R1R, 1S1S, 1T1T, 1U1U, 1V1V)
SELECT myAAAA.1111, myAAAA.2222, myAAAA.3333_EXT, myAAAA.4444, myAAAA.5555, myAAAA.6666,
myAAAA.7777, myAAAA.8888, myAAAA.9999, myAAAA.1010, myAAAA.1A1A, myAAAA.1B1B,
myAAAA.3333_LO, myAAAA.1C1C, myAAAA.1D1D, myGGGG.1E1E, myGGGG.1F1F, myGGGG.1G1G,
myGGGG.1H1H, myGGGG.1I1I, myGGGG.1J1J, myGGGG.1K1K, myGGGG.R1D1D,
myGGGG.1L1L, myGGGG.1M1M, myGGGG.1N1N, myGGGG.1O1O, myGGGG.1P1P,
myGGGG.1Q1Q, myGGGG.1R1R, myGGGG.1S1S, myGGGG.1T1T, myGGGG.1U1U, myGGGG.1V1V
FROM myGGGG INNER JOIN myAAAA ON myGGGG.R1D1D = myAAAA.1D1D
ORDER BY myGGGG.R1D1D;
COMMIT;
END;
Thanks
