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/DROP statements 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

The below solution is the closest from SQL Server I can do today.

Objects:

    CREATE OR REPLACE TYPE T_NUMBERS IS TABLE OF NUMBER;

    CREATE OR REPLACE FUNCTION ACCUMULATE (vNumbers T_NUMBERS)
    RETURN T_NUMBERS
    AS
       vRet T_NUMBERS;
    BEGIN
       SELECT SUM(COLUMN_VALUE)
       BULK COLLECT INTO vRet
       FROM TABLE(CAST(vNumbers AS T_NUMBERS));

       RETURN vRet;
    END;

Queries:

    --Query 1: Fixed number list.
    SELECT *
    FROM TABLE(ACCUMULATE(T_NUMBERS(1, 2, 3, 4, 5)));

    --Query 2: Number list from query.
    WITH cteNumbers AS
    (
      SELECT 1 AS COLUMN_VALUE FROM DUAL UNION
      SELECT 2 AS COLUMN_VALUE FROM DUAL UNION
      SELECT 3 AS COLUMN_VALUE FROM DUAL UNION
      SELECT 4 AS COLUMN_VALUE FROM DUAL UNION
      SELECT 5 AS COLUMN_VALUE FROM DUAL
    )
    SELECT *
    FROM TABLE(
            ACCUMULATE(
              (SELECT CAST(COLLECT(COLUMN_VALUE) AS T_NUMBERS)
               FROM cteNumbers)
            )
          );

Answer 3

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

Answer 4

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.