0

In SQL Server one could do something like the following

declare @t as table(id int)

insert into @t values(1)
insert into @t values(2)

delete from @t where id=1

Is there an equivalence of this in Oracle without creating a physical table. Now, I used to create physical table to do this and delete later.

I have gone to this links How to create a temporary table in Oracle but that's 2010 and the reference link mentioned Oracle 8i. Is this still the situation with Oracle 10g and 11g? Another link I have visited is Constructing a temporary table in Oracle SQL

Thanks

Community
  • 1
  • 1
codingbiz
  • 26,179
  • 8
  • 59
  • 96
  • Could you not use a collection, perhaps an associative array? – Ollie Aug 16 '12 at 14:24
  • @Ollie I only used that once but not in a similar way. Being able to perform CRUD on it, is that possible? – codingbiz Aug 16 '12 at 14:34
  • possible duplicate of [Temporary table in Oracle?](http://stackoverflow.com/questions/11131057/temporary-table-in-oracle) – Ben Aug 16 '12 at 14:36
  • Although this is a duplicate I would recommend forgetting about temporary tables. Oracle is different from SQL Server. http://stackoverflow.com/questions/2918466/ways-to-avoid-global-temp-tables-in-oracle is highly relevant as is http://stackoverflow.com/questions/7598631/alternatives-for-temporary-tables-in-oracle. I would recommend reading both. – Ben Aug 16 '12 at 14:38
  • @Ben I have seen that link and I even posted several duplicate links too. I don't need this in Stored Proc. I need it my query window e.g. SQL Developer IDE to simply test before writing the script against the actual table – codingbiz Aug 16 '12 at 14:46
  • 1
    Maybe I'll go with CTE. Oracle CTE is not as flexible as SQL Server CTE though where you can reference any of the CTE from anywhere – codingbiz Aug 16 '12 at 14:52
  • 1
    @codingbiz - Why wouldn't you write the script in a development environment against the version of the table that is in the development environment? It doesn't make a lot of sense to write a script against table B when you really need to execute it against table A. Unless, perhaps, you're trying to develop the script in production rather than developing it in a lower environment and promoting it through the environments. – Justin Cave Aug 16 '12 at 14:52
  • Why? To make use of few columns that are needed to make the script work and to have fewer results than would be returned from the original table. – codingbiz Aug 16 '12 at 14:55
  • possible duplicate of [How to create a temporary table in Oracle](http://stackoverflow.com/questions/2671518/how-to-create-a-temporary-table-in-oracle) – Bob Jarvis - Слава Україні Aug 17 '12 at 02:00

3 Answers3

1

CREATE GLOBAL TEMPORARY TABLE admin_work_area (startdate DATE, enddate DATE, class CHAR(20)) ON COMMIT DELETE ROWS;

This statement creates a temporary table that is transaction specific. For details use below link:

http://docs.oracle.com/cd/B28359_01/server.111/b28310/tables003.htm#i1006400

rt.jar
  • 61
  • 2
1

In most cases you do not need it. In Oracle when you need temporary table then "your design is wrong". Do not try to rewrite MS SQL pattern into Oracle which exact wording. Where you use temporary table in MS SQL you use in Oracle CTE(nested subquery, query factoring) a CURSOR or some PL/SQL construct.

The temporary table is not what you need. It's just a tool you use to achieve some goal. In Oracle you should use other tools.

ibre5041
  • 4,903
  • 1
  • 20
  • 35
  • +1, but can you cite a reference for the preference for CTE's in oracle vs #tempdb work in Sql Server? – StuartLC Dec 20 '13 at 12:49
  • 1
    Look at Jonathan Lewis blog. There you will find several articles about CTEs(query factoring). Oracle performs various algebraic transformations before optimizing the query. In most cases this will result in NOT materializing the subquery - e.i. no disk I/O against TEMP tablespace. While temporary tables will always generate some I/O. – ibre5041 Dec 20 '13 at 13:12
0

Use an Associative Array :)

declare
type temp_rec is record(v integer);
type temp_table is table of temp_rev indexed by pls_integer;

my_temp_table temp_table;

begin
  -- Here you can do do your stuff :)
end
/
kleopatra
  • 51,061
  • 28
  • 99
  • 211
Harry McDale
  • 1