CODEWITHSUNDEEP
oracle11g
meena
meena

Reputation: 25

Can I use "CREATE LOCAL TEMPORARY TABLE table_name();" [or similar] (oracle stored procedure)

Can I use 

CREATE LOCAL TEMPORARY TABLE table_name();

[or similar]....., instead of 

CREATE GLOBAL TEMPORARY TABLE table_name();

While creating an oracle stored procedure. to save the space that global temp table creates, or suggest any other way possible.

Upvotes: 1

Views: 1147

Answers (1)

Littlefoot
Littlefoot

Reputation: 142720

You didn't mention database version you use. If it is 18c, then you can create a private temporary table which is memory-based and is dropped at the end of the transaction or the session (depending on the way you create it): ON COMMIT PRESERVE DEFINITION or ON COMMIT DROP DEFINITION.

If it is a lower version, then a global temporary table is the only option if you really want a temporary table.

Another option is to create a "normal" table, use it, truncate after you're done with what you do.

However, perhaps the best question is: do you really need a temporary table? If your background is MS SQL Server which uses them much, then try to switch to Oracle-ish way of living, most of time without temporary tables. It, actually, depends on what you want to do. If you could explain that, someone might suggest a good approach.

Upvotes: 1

Related Questions