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