Temporary table in stored procedures!!!
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Temporary table in stored procedures!!!

  1. #1
    Join Date
    Nov 2002
    Location
    London, UK
    Posts
    13

    Temporary table in stored procedures!!!

    Hi,

    I have recently started programming in ORACLE 8. My problem is using temporary tables within procedures. I can create GTT & return data to my report. Once the procedure is finished the table still remain in the database. How can I get rid of the GTT within my stored proc. Any help would be appreciated. Here is my sampe code:

    CREATE OR REPLACE PROCEDURE testsp(O_RESULT_SET IN OUT TEST_PACK.CURSOR_TYPE)
    AS

    BEGIN

    execute immediate 'Create global temporary table man_temp on commit preserve rows as SELECT * from curr' ;

    open O_RESULT_SET for 'select * from man_temp';

    --Once the data is returned to my report, I want to drop the temp table.

    END;

  2. #2
    Join Date
    Nov 2002
    Location
    London, UK
    Posts
    13

    Question



    Can anyone please help?

  3. #3
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    So why do you need a table at all?

    I'm confused.

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  4. #4
    Join Date
    Nov 2002
    Posts
    2
    Temporary tables in Oracle are not like temporary tables in other DBMSs. I too am frustrated with the way Oracle has implemented temporary tables, I think the Informix style is WAY better. Anyways, in Oracle, temporary tables are permanent. That may sound like an oxymoron, but it's true. When you "create global temporary table..." in Oracle, you are creating a permanent table for which the DATA is temporary. The only way to get rid of the table is to drop it. I suggest you just create it as part of your schema and then you can use it directly in your code. You can set the table so that data disappears after a transaction ends or after a session ends.

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    there was a discussion about temp tables in oracle in usernet, I think the way you use temp tables in informix you can do them with inline views in oracle

    that's what I think but I may be wrong because I have not worked with informix

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