Temp Tables within Stored Procedure
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Temp Tables within Stored Procedure

  1. #1
    Join Date
    Jul 2013
    Posts
    4

    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

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,002
    If you want real help, you should let people know what error message you are getting.
    If you want something, that may or may not solve your problem, then I suggest the Magic 8 ball...

    http://web.ics.purdue.edu/~ssanty/cgi-bin/eightball.cgi
    this space intentionally left blank

  3. #3
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,477

    Cool

    Oracle temporary tables are statically defined, you can learn all about them by Reading The Fine Manual: OracleŽ Database Administrator's Guide
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width