Equivalent to Oracle PostgreSQL INSERT ... RETURNING *;

Question

I converted several DML queries (INSERT / UPDATE / DELETE) from Oracle to PostgreSQL, and now I need to check if they produce the same set of rows, i.e. deletion deletes the same rows, assuming the oracle and the first postgresql databases contain the same data, the update updates the same rows, etc. On the PostgreSQL side, I can use the return clause with DML operators, i.e.

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

What is the use of the above statement is that I can add a “return” to any DML statement without knowing the structure or even the name of the table in which it was executed, and just get all the rows like it with a select statement.

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

Therefore, the question is whether there is an oracle statement (or sequence of statements) to emulate PostgreSQL "return *" without hard tables or column names. In other words, is there a way to write such an Oracle function:

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

It should return a set of rows inserted (or altered generally) by the SQL statement.

Update: I really found a very similar question (for conversion from SQL server, not PostgreSQL, to Oracle). However, I would like to hear a simpler answer to this, if possible.

Answer 1

This is currently not possible, especially on an older version of Oracle such as 8i. See this answer to a similar question.

Answer 2

I could imagine a solution with EXECUTE IMMEDIATE , RETURNING and REF CURSOR , but it is clear that it will be far from simple. Earlier, I found solutions, such as this one, including XML , for problems in which arbitrary type records should be used. At least they are freaky. I think you’ll have to resort to two separate queries ... In particular, with Oracle 8i, I’m afraid you won’t even be able to benefit from most of these features.

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