Menu
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?
973
The OUTPUT clause returns the values of each row that was affected by an INSERT, UPDATE or DELETE statements. It even supports with a MERGE statement, which was introduced in SQL Server 2008 version. The result from the OUTPUT clause can be inserted into a separate table during the execution of the query.
To do this we use a procedure called dbms_output. put_line to place the results in a buffer that SQL*Plus will retrieve and display. SQL*Plus must be told to retrieve data from this buffer in order to display the results. The SQL*Plus command 'set serveroutput on' causes SQL*Plus to retrieve and display the buffer.
Oracle supports PL/SQL and SQL language to write queries to access data from its database.
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)
114
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
