This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 31st, when you will be able to use this site as normal.

    Forum Stats

  • 3,890,588 Users
  • 2,269,776 Discussions
  • 7,916,825 Comments

Discussions

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

Lukas Eder
Lukas Eder St. Gallen, SwitzerlandMember Posts: 128 Bronze Badge
edited May 1, 2018 10:22AM in Database Ideas - Ideas

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.

Lukas EderThomas Teske-OracleThomas AreggertbenbrahimApexBineSven W.blessed DBAHolgerHsdstuber
9 votes

Active · Last Updated

Comments