Oracle Database Discussions

Does Oracle have anything equivalent to SQL Server table variables, that can be used in the JOIN clause of a select statement ?

What I want to do is execute a query to retrieve a two-column result, into some form of temporary storage (a collection ?), and then re-use that common data in many other queries inside a PL/SQL block. I could use temporary tables, but I'd like to avoid having to create new tables in the database, if possible. If I was doing this in SQL Server, I could use a table variable to do this, but is there anything similar in Oracle ? SQL Server example:

use Northwind
DECLARE @myVar TABLE(CustomerID nchar(5), CompanyName nvarchar(40))
INSERT INTO @myVar(CustomerID, CompanyName)
select CustomerID, CompanyName
from Customers

--Join the variable onto a table in the database
SELECT *
FROM @myVar mv join Customers
on mv.CompanyName = Customers.CompanyName


The closest I've found in Oracle is to use CREATE TYPE to create new types in the database, and use TABLE and CAST to convert the collection to a table, as shown below. I can't see anyway without creating new types in the database.



CREATE TYPE IDMap_obj AS Object(OldID number(15), NewID number(15));
CREATE TYPE IDMap_TAB IS TABLE OF IDMap_obj;

DECLARE
v_Count Number(10) := 0;
--Initialize empty collection
SourceIDMap IDMap_TAB := IDMap_TAB();
BEGIN
--Populate our SourceIDMap variable (dummy select statement for now).
FOR cur_row IN (select ID As OldID, ID + 10000000 As NewID From SomeTable) LOOP
SourceIDMap.extend;
SourceIDMap(SourceIDMap.Last) := IDMap_obj(cur_row.OldId, cur_row.NewId);
END LOOP;

--Print out contents of collection
FOR cur_row IN 1 .. SourceIDMap.Count LOOP
DBMS_OUTPUT.put_line(SourceIDMap(cur_row).OldId || ' ' || SourceIDMap(cur_row).NewId);
END LOOP;

--OK, can we now use our collection in a JOIN statement ?
SELECT COUNT(SM.NewID)
INTO v_Count
FROM SomeTable ST JOIN
TABLE(CAST(SourceIDMap As IDMap_TAB)) SM
ON ST.ID = SM.OldID;

DBMS_OUTPUT.put_line(' ' );
DBMS_OUTPUT.put_line('v_Count is ' || v_Count);
END;