Is there an Oracle equivalent to SQL Server's OUTPUT INSERTED.*?

Jason Baker picture Jason Baker · Jul 2, 2009 · Viewed 13.9k times · Source

In SQL Server, you can do things like this:

INSERT INTO some_table (...) OUTPUT INSERTED.*
VALUES (...)

So that you can insert arbitrary sets of columns/values and get those results back. Is there any way to do this in Oracle?

The best I can come up with is this:

INSERT INTO some_table (...)
VALUES (...)
RETURNING ROWID INTO :out_rowid

...using :out_rowid as a bind variable. And then using a second query like this:

SELECT *
FROM some_table
WHERE ROWID = :rowid

...but this isn't quite the same as it returns everything within the column, not just the columns I inserted.

Is there any better way to do this without using a lot of PL/SQL and preferably with only one query?

Answer

Michal Pravda picture Michal Pravda · Jul 3, 2009

Maybe I don't understand the question, but wouldn't this do it? (you must know what you want back)

INSERT INTO some_table (...)
VALUES (...)
RETURNING some_column_a, some_column_b, some_column_c,  ...  INTO :out_a, :out_b, :out_c, ...

@Vincent returning bulk collect into for multi-row insert works only in conjunction with forall (in another words if you insert from collection you can retrieve "results" into another)