Oracle equivalent of PostgreSQL INSERT...RETURNING *;

Question

I've converted a bunch of DML (INSERT/UPDATE/DELETE) queries from Oracle into PostgreSQL and now I need to check whether they produce the same set of rows, i.e. that delete removes the same rows, assuming the oracle and postgresql databases contain the same data initially, update updates the same rows etc. On PostgreSQL side, I can use the returning clause with DML statements, i.e.

INSERT INTO test(id, name) VALUES(42, 'foo') RETURNING *;

What's good about the statement above is that I can prepend 'returning *' to any DML statement without knowing the structure or even the name of the table it's executed against and just get all rows like it's a select statement.

However, it seems to be not that shiny on the Oracle side. According to the documentation, Oracle 8i (the one I'm working with) supports RETURNING clause, but it has to store the result into variables and there seem to be no obvious way to get all result columns instead of manually specifying the column name.

Hence, the question is if there is an oracle statement (or sequence of statements) to emulate PostgreSQL 'returning *' without hard-coding table or column names. In other words, is there a way to write an Oracle function like this:

fn('INSERT INTO test(id, name) VALUES(42, ''foo'')') 

It should return the set of rows inserted (or modified in the generic case) by the SQL statement.

Update: I actually found a very similar question (for the conversion from SQL server, not PostgreSQL, into Oracle). Still, I'd love to hear a more simple answer to that if possible.

Answer 1

I could imagine a solution involving EXECUTE IMMEDIATE, RETURNING, and REF CURSOR, but clearly it will be far from simple. I've previously found solutions such as this one, involving XML to problems where records of arbitrary type are to be used. They're quite freaky, to say the least. I guess you'll have to resort to running two separate queries... Specifically, with Oracle 8i, I'm afraid you won't even be able to profit from most of those features.

In short, I don't think there is any SQL construct as powerful as Postgres ... RETURNING clause in Oracle.

Answer 2

It's not currently possible, especially in an old version of Oracle such as 8i. See this answer to a similar question.