Is there an Oracle equivalent to SQL Server's OUTPUT INSERTED.*?
Solution 1
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)
Solution 2
The RETURNING
clause supports the BULK COLLECT INTO synthax. Consider (10g):
SQL> CREATE TABLE t (ID NUMBER);
Table created
SQL> INSERT INTO t (SELECT ROWNUM FROM dual CONNECT BY LEVEL <= 5);
5 rows inserted
SQL> DECLARE
2 TYPE tab_rowid IS TABLE OF ROWID;
3 l_r tab_rowid;
4 BEGIN
5 UPDATE t SET ID = ID * 2
6 RETURNING ROWID BULK COLLECT INTO l_r;
7 FOR i IN 1 .. l_r.count LOOP
8 dbms_output.put_line(l_r(i));
9 END LOOP;
10 END;
11 /
AADcriAALAAAAdgAAA
AADcriAALAAAAdgAAB
AADcriAALAAAAdgAAC
AADcriAALAAAAdgAAD
AADcriAALAAAAdgAAE
It works with multi-row UPDATE
and DELETE
with my version (10.2.0.3.0) but NOT with INSERT
:
SQL> DECLARE
2 TYPE tab_rowid IS TABLE OF ROWID;
3 l_r tab_rowid;
4 BEGIN
5 INSERT INTO t (SELECT ROWNUM FROM dual CONNECT BY LEVEL <= 5)
6 RETURNING ROWID BULK COLLECT INTO l_r;
7 FOR i IN 1 .. l_r.count LOOP
8 dbms_output.put_line(l_r(i));
9 END LOOP;
10 END;
11 /
ORA-06550: line 7, column 5:
PL/SQL: ORA-00933: SQL command not properly ended
Maybe you have a more recent version (11g?) and the BULK COLLECT INTO
is supported for multi-row INSERT
s ?
Related videos on Youtube
Comments
-
Jason Baker almost 4 years
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?
-
Jeffrey Kemp almost 15 yearsIf it's the columns you're interested in (not the row data)... - How are you deriving the (...)? Surely at that point you know which columns are being referenced in the insert?
-
Jeffrey Kemp almost 15 yearsI just read up on OUTPUT INSERTED (msdn.microsoft.com/en-us/library/ms177564.aspx). Apparently it allows you to pick whether the row data returned is that before or after any table triggers have run. Oracle's RETURNING clause doesn't support this - it only gives you the data after triggers have had a chance to change it.
-
Jason Baker almost 15 years@ Jeffrey Kemp - I would know what those columns are. However, so should the database. :-)
-