12 Replies Latest reply on Oct 14, 2009 6:40 PM by Campber

    PL/SQL to create a temp table that will be dropped after session ends

    tx103108
      Is it possible in PL/SQL to create a temp table that will be dropped after the session ends? Please provide example if possible. I can create a global temp table in PL/SQL but I am not sure how (if possible) to have it 'drop' once the session ends.

      DB: 10g
      OS: Wiindoze 2003 Server

      :-)
        • 1. Re: PL/SQL to create a temp table that will be dropped after session ends
          Walter Fernández
          Hi,

          Why don't you use a temporary table? Search for global temporary in [CREATE TABLE|http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_7002.htm#i2095331] documentation.

          Regards,
          1 person found this helpful
          • 2. Re: PL/SQL to create a temp table that will be dropped after session ends
            Etbin
            Creating a table to be dropped at the end of the session indicates that most probably you don't need it. Think about it.
            Anyway CTAS (Create Table As Select something from somewhere) seems to be the easiest way.

            Regards

            Etbin
            1 person found this helpful
            • 3. Re: PL/SQL to create a temp table that will be dropped after session ends
              Frank Kulash
              Hi,
              user601798 wrote:
              Is it possible in PL/SQL to create a temp table that will be dropped after the session ends? Please provide example if possible. I can create a global temp table in PL/SQL but I am not sure how (if possible) to have it 'drop' once the session ends.

              DB: 10g
              OS: Wiindoze 2003 Server

              :-)
              Typically, Global Temporary Tables are not regularly dropped; you create the table once and use it as many times as you need to. The data is automatically deleted when you end the session (assuming the table was created with "ON COMMIT PRESERVE ROWS"). Every time you start a new session, the table is already there, with 0 rows.

              What are you trying to do? Describe the problem in terms of your business requirements, and then we can discuss what Oracle features and techniques might be useful.
              1 person found this helpful
              • 4. Re: PL/SQL to create a temp table that will be dropped after session ends
                tx103108
                What I am trying to do is - for performance reasons - use temporary tables for a session only and not have to worry about cleaning (dropping) those tables afterwards.

                Other RDBM systems - SQL Server - have such tables. Example for SQL Server would be:

                CREATE TABLE #mytable (
                age int,
                mame char(30) )

                You'll notice I prefixed the table with a pound sign (#). This tells SQL Server that this table is a local temporary table. This table is only visible to this session of SQL Server. When I close this session, the table will be automatically dropped.

                Edited by: user601798 on Aug 4, 2009 2:30 PM
                • 5. Re: PL/SQL to create a temp table that will be dropped after session ends
                  SomeoneElse
                  Other RDBMS systems - SQL Server - have some tables.
                  Typically, people who ask about these "create-and-drop temp tables" come from a SQL Server background. That must be SOP on that database.

                  But that's not a good approach with Oracle.

                  Create the table once and be done with it. If you really want the data to be temporary within a session, then use Global Temp Tables as others have suggested. Have you tried this yet?
                  1 person found this helpful
                  • 6. Re: PL/SQL to create a temp table that will be dropped after session ends
                    William Robertson
                    Oracle has global temporary tables, but not session temporary tables.

                    You probably don't need to create tables and drop them again, as you can generally do everything you need in straight SQL. Just to repeat what Frank said,
                    What are you trying to do? Describe the problem in terms of your business requirements, and then we can discuss what Oracle features and techniques might be useful.
                    1 person found this helpful
                    • 7. Re: PL/SQL to create a temp table that will be dropped after session ends
                      tx103108
                      I have tried global temporary tables (GTT) but the table is not dropping after the session ends ... here is the syntaxes I have used. Is there something syntactically that I am missing?

                      (I am doing this in PL/SQL and I have used both commit options)

                      EXECUTE IMMEDIATE 'create global temporary table mytable(age number(10),
                      name varchar2(20))
                      on commit preserve rows';


                      EXECUTE IMMEDIATE 'create global temporary table mytable(age number(10),
                      name varchar2(20))
                      on commit delete rows';

                      I am guessing there is not quite the same thing in Oracle as in SQL Server. With GTT, you save the table and the contents get dropped (or not) depending on the commit option.

                      Edited by: user601798 on Aug 4, 2009 2:45 PM
                      • 9. Re: PL/SQL to create a temp table that will be dropped after session ends
                        345641
                        Hi,

                        have you considered using a pl/sql collection to temporarily store the data you are processing.

                        They are easy to create and manipulate and can be populated very efficiently with bulk collect. They reside in memory so you will need to be careful about how large they grow, but the bulk collect feature even allows you to collect in batches from the same cursor, keeping your memory requirements at an acceptable level.

                        There are plenty of examples around for their use.

                        Regards
                        Andre
                        • 10. Re: PL/SQL to create a temp table that will be dropped after session ends
                          BluShadow
                          As others have mentioned (but probably not clearly explained), Oracle treats temporary tables differently to SQL Server.

                          In SQL Server you create a temporary table and it gets dropped (automatically I assume, I dont do SQL Server) after the session finishes. This will obviously allow each session to "request" a temporary table to use, then use it, and not have to worry about cleaning up the database after the session has finished.

                          Oracle takes a different approach...

                          On the assumption that each session is likely to be creating a temporary table for the same purposes, with the same structure, Oracle let's you create a Global Temporary Table a.k.a. GTT (which you've already come across). You only have to create this table once and you leave it on the database. This then means that any code written to use that table doesn't have to be dynamic code and can be verified and checked at compile time, just like code written for any other table. The difference of a GTT from a regular table is that any data you put into that table can only be seen by that session and will not interfere with any data of other sessions and, when you either commit, or end the session (depending on the "on commit delete rows" or "on commit preserve rows" option used when creating the GTT), that data from your own session will automatically be removed and hence the table is cleaned up that way, whilst the actual table itself remains.

                          Some people from SQL Server backgrounds try and create and drop tables dynamically in their PL/SQL code, but this leads to problems...
                          SQL> ed
                          Wrote file afiedt.buf
                          
                            1  begin
                            2    execute immediate 'create table my_temp (x number)';
                            3    insert into my_temp values (1);
                            4    execute immediate 'drop table my_temp';
                            5* end;
                          SQL> /
                            insert into my_temp values (1);
                                        *
                          ERROR at line 3:
                          ORA-06550: line 3, column 15:
                          PL/SQL: ORA-00942: table or view does not exist
                          ORA-06550: line 3, column 3:
                          PL/SQL: SQL Statement ignored
                          i.e. the code will not compile for direct DML statements trying to use that table.

                          They then try and get around this issue by making their DML statements dynamic too...
                          SQL> ed
                          Wrote file afiedt.buf
                          
                            1  create or replace procedure my_proc is
                            2  begin
                            3    execute immediate 'create table my_temp (x number)';
                            4    execute immediate 'insert into my_temp values (''A'')';
                            5    execute immediate 'drop table my_temp';
                            6* end;
                          SQL> /
                          
                          Procedure created.
                          ... which looks great and it compiles ok... but... when they try and run it...
                          SQL> exec my_proc;
                          BEGIN my_proc; END;
                          
                          *
                          ERROR at line 1:
                          ORA-01722: invalid number
                          ORA-06512: at "SCOTT.MY_PROC", line 4
                          ORA-06512: at line 1
                          ... oops the code has a bug in it. Our DML statement was invalid.

                          This is really something that would have been caught at compile time, if the statement had been a direct DML statement rather than dynamic. And thus we see the problem with people trying to write all their code as dynamic SQL... it's more likely to contain bugs that won't be detected at compile time and only come to light at run time... sometimes only under certain conditions and sometimes once it's got into a production environment. Bad Idea!!!! ;)

                          Far better to never create tables (or most other database objects) at run time. Just create them once as part of the database design/implementation and use them as required, allowing you to catch the most common coding errors up front before they get anywhere near a test environment or worse still, a production environment.
                          • 11. Re: PL/SQL to create a temp table that will be dropped after session ends
                            Boneist
                            In addition to what BluShadow et al. have said, in my experience, people from a SQL Server / Sybase / etc background usually want to create temporary tables and drop them afterwards to hold a dataset, as it can be expensive to select all the data in one big query.

                            In Oracle, this is typically not the case. You will find that it will often be much more performant to just write one big statement in Oracle, than using the "create a temporary table to house the data for a bit, query from that temp table to get the final resultset and then drop the table when we're done with it" or "use the GTT to house the data for a bit and query that to get the final resultset".

                            If you use the subquery factoring ("With my_table as (select .... from .... where ....) select * from my_table") to write your query (from version 9i and above), you'll probably find that you can create one big SELECT statement very easily. The advantage to doing this is that if Oracle decides that a temporary table is the best way to go (for example, you refer to the same set of data in multiple places in your query), then it will create one behind the scenes, so you don't have to worry about it!

                            So, in short, doing one big SQL statement in Oracle is usually the best option.
                            • 12. Re: PL/SQL to create a temp table that will be dropped after session ends
                              Campber
                              Thanks for the good explanation of GTT.