ORACLE: SQL Server table variable equivalent in oracle.

In SQL Server we can define a table variable and use it like a regular table. We can insert, update, delete records from that table variable. There table variable becomes very useful if we do not want to use collections or temp tables. Also scope of table variable remains only within a T-SQL block on which it was declared. Take a look at the below T-SQL code written for SQL Server:

--Declare a table variable. Syntax is like any other database table.
DECLARE @v_tab TABLE (a INT, b VARCHAR(100));
DECLARE @v_cnt INT;
begin
--Insert records into a table variable.
INSERT  INTO @v_tab(a,b)
SELECT object_id, name FROM sys.tables;

SELECT @v_cnt = COUNT(*) 
FROM @v_tab;

PRINT @v_cnt;

--delete few records.
delete FROM @v_tab 
WHERE b like '%mail%';

SELECT @v_cnt = COUNT(*) 
FROM @v_tab;

PRINT @v_cnt;

end;
GO

Unfortunately Oracle does not provide any such capabilities in PL/SQL. If we want to achieve similar functionality we have to use collections, records and table() function. But it becomes little complicated then SQL Server. Take a look at below script.

create or replace type a_record as object   ( object_name varchar2(30), created_date date, object_type number );
/
create or replace type a_tab_variable as table of a_record; 
/

DECLARE 
       a_tab a_tab_variable;
       v_cnt INT;
BEGIN
       select  a_record ( object_name, created, object_id )
       bulk collect into a_tab
       from user_objects;  
  
   SELECT count(*)
INTO v_cnt  
FROM TABLE(a_tab);   

DBMS_OUTPUT.PUT_LINE( v_cnt );
         
         FOR a_rec IN ( 
                   SELECT b.object_name
                        FROM TABLE(a_tab) b    
                         WHERE b.created_date < (SYSDATE - 2)
                               AND ROWNUM < 101) 
         LOOP
                   DBMS_OUTPUT.PUT_LINE( a_rec.object_name );
         END LOOP;                  
END;
/