In SQL Server, we can write
DECLARE @tab TABLE (i INT, j INT);
INSERT INTO @tab VALUES (1, 1), (2, 2);
SELECT * FROM @tab;
The equivalent of the above in Oracle PL/SQL is this:
CREATE TYPE o AS OBJECT (i INT, j INT);
/
CREATE TYPE t AS TABLE OF o;
/
DECLARE
tab t := t();
c sys_refcursor;
BEGIN
tab.extend(1);
tab(1) := o(1, 1);
tab.extend(1);
tab(2) := o(2, 2);
OPEN c FOR SELECT * FROM TABLE (tab);
dbms_sql.return_result(c);
END;
/
First off, it would be great if we could use local PL/SQL types in a similar fashion, of course. But what I'd *really* like to do is something like this:
DECLARE
tab t := t();
c sys_refcursor;
BEGIN
INSERT INTO tab VALUES (1, 1);
INSERT INTO tab VALUES (2, 2);
OPEN c FOR SELECT * FROM TABLE (tab);
dbms_sql.return_result(c);
END;
/
I.e. I'd like to be able to use ordinary SQL statements to INSERT / UPDATE / DELETE / MERGE data into my in-memory table type, as if it were an ordinary table.
While there exists some workaround using SELECT .. BULK COLLECT INTO, that approach would be much less powerful than the SQL Server approach, where in-memory tables behave just like any other table.