Does Oracle have an equivalent of SQL Server's table variables?

Question

In SQL Server, you can declare a table variable (DECLARE @table TABLE), which is produced while the script is run and then removed from memory.

Does Oracle have a similar function? Or am I stuck with CREATE/DROPs that segment my hard drive?

Answer 1

Yes.

Declare TABLE TYPE variables in a PL/SQL declare block. Table variables are also known as index-by table or array. The table variable contains one column which must be a scalar or record datatype plus a primary key of type BINARY_INTEGER. Syntax:

DECLARE TYPE type_name IS TABLE OF (column_type | variable%TYPE | table.column%TYPE [NOT NULL] INDEX BY BINARY INTEGER;

-- Then to declare a TABLE variable of this type: variable_name type_name;

-- Assigning values to a TABLE variable: variable_name(n).field_name := 'some text'; -- Where 'n' is the index value

Ref: http://www.iselfschooling.com/syntax/OraclePLSQLSyntax.htm

You might want to also take a look at Global Temporary Tables

Answer 2

Oracle is not my forte, but table variables appear to be a subset of the Oracle Collection Variable

Answer 3

Yes it does have a type that can hold the result set of a query (if I can guess what TABLE does). From ask Tom: your procedure may look like this:

procedure p( p_state in varchar2, p_cursor in out ref_cursor_type )
is
begin
    open p_cursor for select * from table where state = P_STATE;
end;

where p_cursor is like a table type. As has been already answered there are plenty of options for storing result sets in Oracle. Generally Oracle PL/SQL is far more powerful than sqlserver scripts.

Answer 4

the table in variable in oracle not the same as table variables in MS SQLServer. in oracle it's like regular array in java or c#. but in MS SQLserver it is the same as any table, you can call it logical table. but if you want something in oracle that does exactly the same as table variable of SQLserver you can use cursor.

regards