Support DML statement syntax on SQL and PL/SQL TABLE types

Lukas Eder

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.

Sven W.

I think that is possible in 18c (maybe 12.1). At least in a very similar way. We can declare a local plsql collection and then do a select .. .from table(collection);
Without the need to declare an object type in SQL.

See this example in LiveSQL (statement 10):

https://livesql.oracle.com/apex/livesql/file/content_CHREI3LLPBLRAHQR9TDJIO677.html

So while I think this is a good idea, it should also be marked as "already implemented".

blessed DBA

Will it work for complex query's . if so it will be more useful.