One way to do this would be Object Types, in this case the type would be analagous to your #t1. So it would need to be defined somewhere but it would not need to be global, it could be per-schema or per-procedure even. First, we can create a type:
SQL> create or replace type t1_type as object (x int, y int, z int)
2 /
Type created.
SQL> create or replace type t1 as table of t1_type
2 /
Type created.
Now set up some sample data:
SQL> create table xy (x int, y int)
2 /
Table created.
SQL> insert into xy values (1, 2)
2 /
1 row created.
SQL> insert into xy values (3, 4)
2 /
1 row created.
SQL> commit
2 /
Commit complete.
And create a function over this data returning our "temporary" type:
SQL> create or replace function fn_t1 return t1 as
2 v_t1 t1 := t1(); -- empty temporary table (really an array)
3 v_ix number default 0; -- array index
4 begin
5 for r in (select * from xy) loop
6 v_ix := v_ix + 1;
7 v_t1.extend;
8 v_t1(v_ix) := t1_type(r.x, r.y, (r.x + r.y));
9 end loop;
10 return v_t1;
11 end;
12 /
Function created.
And finally:
SQL> select * from the (select cast (fn_t1 as t1) from dual)
2 /
X Y Z
---------- ---------- ----------
1 2 3
3 4 7
As you can see this is pretty clunky (and uses collection pseudo-functions, which is an obscure feature at the best of times!), as I always say, porting from DB to DB is not merely about syntax and keywords in their SQL dialects, the real difficulty comes in different underlying assumptions (in the case of SQL Server, that cursors are expensive and their use avoided/worked around at all costs).
Tables and indexes are created in users tablespace, which is USERS by default. A temporary tablespace, TEMP by default, is used by oracle for sorting large amount of data on disk. Temporary tablespace is typically used for processing joins, order by or creating indexes.
Since the default tablespace TEMP is not large enough for creating a large index, your example creates a large temporary tablespace ts_tmp for the index creation purpose only and drops it afterwards.
When creating the index with CREATE UNIQUE INDEX ... TABLESPACE ... command, don't use ts_tmp in the TABLESPACE clause. This will give you Attempt to create PERMANENT object in a TEMPORARY tablespace error.
Either don't use the TABLESPACE clause, then the defualt users tablespace will be used. Or give a tablespace name that was created with a CREATE TABLESPACE command.
Best Answer
I believe your DBA told you to use temporary tablespace for temporary data? If so using temporary tables have the following advantages:
Of course I forgot something. :) People will add more advantages in comments.
While listing advantages I am not considering scenario when you have to preserve data between restarts.
Database Concepts about Temporary Tables