Temporary table in stored procedures!!!

[mjain]

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;

[mjain]

Can anyone please help?

[chrisrlong]

So why do you need a table at all?

I'm confused.

- Chris

[infidel]

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.

[pando]

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