Home » SQL & PL/SQL » SQL & PL/SQL » Temp Table within an Stored Procedure (Oracle)
Temp Table within an Stored Procedure [message #590156] Mon, 15 July 2013 07:54 Go to next message
ModelosPR
Messages: 3
Registered: July 2013
Junior Member
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?

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!
Re: Temp Table within an Stored Procedure [message #590157 is a reply to message #590156] Mon, 15 July 2013 07:56 Go to previous messageGo to next message
Littlefoot
Messages: 19809
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
In Oracle, you don't create any kind of tables "on the fly" - you create them once (CREATE TABLE ...) and use many times. Once you're done with the process, you don't drop it (but you can delete data from it; as of global temporary tables, you don't even have to do that).

P.S. In your case, as I said - you'd create myaaaa and mygggg tables first. Then, in a stored procedure, you'd only INSERT INTO these tables, process data, and - finally - insert the result into the FIDB tables.

However, from what I can see, you don't even need a stored procedure, pure SQL is perfectly capable of doing that job, which means that all you need is an INSERT statement.

[Updated on: Mon, 15 July 2013 07:59]

Report message to a moderator

Re: Temp Table within an Stored Procedure [message #590158 is a reply to message #590157] Mon, 15 July 2013 08:01 Go to previous messageGo to next message
cookiemonster
Messages: 11201
Registered: September 2008
Location: Rainy Manchester
Senior Member
And with the above example there is no need or point in even using temporary tables, never mind creating them.
Just write the whole thing as a single insert/select statement. It'll be a lot more efficient that way.
Re: Temp Table within an Stored Procedure [message #590161 is a reply to message #590157] Mon, 15 July 2013 08:12 Go to previous messageGo to next message
ModelosPR
Messages: 3
Registered: July 2013
Junior Member
Hi,

Thanks for the reply.

I tought I saw in the oracle documentation that you could create temp tables.

The reason for the stored procedure is that an schedule job is going to call it once a day to keep the data final table updated. I use two temp tables because I didn't want to keep the data, just use it for the grouping. I can't just create one query since it will yield different results. Hence to queries and then merging the results from both.

So, I'm still a bit lost of what approach to take them if there are no temp tables in Oracle. Maybe MViews?
Re: Temp Table within an Stored Procedure [message #590162 is a reply to message #590161] Mon, 15 July 2013 08:25 Go to previous messageGo to next message
cookiemonster
Messages: 11201
Registered: September 2008
Location: Rainy Manchester
Senior Member
ModelosPR wrote on Mon, 15 July 2013 14:12

I tought I saw in the oracle documentation that you could create temp tables.

With oracle temporary table the word temporary refers to the data they contain, not the tables themselves. You create them once and use them as required.

ModelosPR wrote on Mon, 15 July 2013 14:12

The reason for the stored procedure is that an schedule job is going to call it once a day to keep the data final table updated. I use two temp tables because I didn't want to keep the data, just use it for the grouping. I can't just create one query since it will yield different results. Hence to queries and then merging the results from both.

Nothing in that explanation explains to me why you can't use a single insert/select. Why would the results be different?

ModelosPR wrote on Mon, 15 July 2013 14:12

So, I'm still a bit lost of what approach to take them if there are no temp tables in Oracle. Maybe MViews?

If you really need temp tables use them (remember to create them once outside of the procedure) but I still don't belive you need them.
Re: Temp Table within an Stored Procedure [message #590165 is a reply to message #590162] Mon, 15 July 2013 08:41 Go to previous messageGo to next message
ModelosPR
Messages: 3
Registered: July 2013
Junior Member
Hi,

Thanks for your help cookiemonster. It doesn't much sense without seing the data. But in the first query I group the data, like a select distinct, which are like definitions of items, but they can be duplicated. The second query I pull out the items data. On the third I pull the definitions from the first query and join it with the items.

So I create two scripts to create the two temp tables, and in the Stored Procedure Truncate them, fill them up with new data, join them and insert into the table. Correct?

Thanks for you time, I really appreciate it.
Re: Temp Table within an Stored Procedure [message #590166 is a reply to message #590165] Mon, 15 July 2013 08:42 Go to previous messageGo to next message
BlackSwan
Messages: 23055
Registered: January 2009
Senior Member
>Correct?
NO!

do as single SQL
Re: Temp Table within an Stored Procedure [message #590167 is a reply to message #590165] Mon, 15 July 2013 08:48 Go to previous message
cookiemonster
Messages: 11201
Registered: September 2008
Location: Rainy Manchester
Senior Member
ModelosPR wrote on Mon, 15 July 2013 14:41

Hi,

Thanks for your help cookiemonster. It doesn't much sense without seing the data. But in the first query I group the data, like a select distinct, which are like definitions of items, but they can be duplicated. The second query I pull out the items data. On the third I pull the definitions from the first query and join it with the items.

Nothing there suggests you can't use a single insert/select statement.
You will probably need inline views, or WITH clauses, but it ought to be pretty simple.
For a starter you could just replace the temporary tables in the current insert/select with the two select statements you use for the temp tables.
That should just work.


ModelosPR wrote on Mon, 15 July 2013 14:41

So I create two scripts to create the two temp tables, and in the Stored Procedure Truncate them, fill them up with new data, join them and insert into the table. Correct?

I'd probably just use delete rather than truncate - since truncate auto-commits - but other than that, yes.
Previous Topic: Transpose dynamic Rows to Columns
Next Topic: Global Temporary Table and Autonomous_Transaction
Goto Forum:
  


Current Time: Wed Nov 26 11:22:26 CST 2014

Total time taken to generate the page: 0.15344 seconds