#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    3
    Rep Power
    0

    Temp Table within an Stored Procedure


    Hi,

    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
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,777
    Rep Power
    348
    In contrast to SQL Server, temporary tables in Oracle are only created once, e.g. as part of your DDL script to setup your database.
    You don't create them "on-the-fly".

    Secondly, you usually don't need a temporary table in Oracle (at least not as much as you do in SQL Server). Just select the data you need as part of your insert statement.

    TRUNCATE is considered a DDL statement in Oracle and thus cannot be run directly inside a stored procedure. You will need to use execute immediate for that.

    Taking all that into account, you should rewrite your procedure to something like this:

    Code:
    CREATE OR REPLACE PROCEDURE UpdateFIDB_SP
    IS
    BEGIN
    
            execute immediate 'TRUNCATE TABLE FIDB';
          
            with 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
           ), 
           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') )
            )
            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;
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

IMN logo majestic logo threadwatch logo seochat tools logo