Menu
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.
404
In an INSERT , the data available to RETURNING is the row as it was inserted. This is not so useful in trivial inserts, since it would just repeat the data provided by the client. But it can be very handy when relying on computed default values.
NEW and OLD in row level triggers The special variables NEW and OLD in a row level trigger function contain the new and the old row version. They can be modified and used in the RETURN statement. Note that NEW is NULL in ON DELETE triggers and OLD is NULL in ON INSERT triggers. trigger invocation. NEW is set.
Description. INSERT inserts new rows into a table. One can insert one or more rows specified by value expressions, or zero or more rows resulting from a query. The target column names can be listed in any order.
While both solutions are quite capable in this category, PostgreSQL may have a slight advantage due to its open-source characteristics. Not only is it much lighter than Oracle, it doesn't require users to spend more money to expand infrastructure. PostgreSQL is capable of accommodating any volume of data.
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.
60
It's not currently possible, especially in an old version of Oracle such as 8i. See this answer to a similar question.
3
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
