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.

Eckenrode

Are you calling this SQL from Java, perhaps? – Hedvige

No, actually I'm calling them from a python script, hence, writing per-statement returning colname,1 colname2 requires parsing the original statement and extracting column and table names, which is not what I'd love to do for such a minor task. – Eckenrode

Why are you using a de-supported and discontinued Oracle version? – Sangraal

I'm migrating an app from that version into PostgreSQL. – Eckenrode

@Eckenrode If you are porting to PostgreSQL why do you need the returning in Oracle? – Sangraal

to make sure DML queries produce the same result for both databases. – Eckenrode

Answer 1

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

Superphysical

Answer 2

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.

Hedvige