|
|
|
|
|
|
| Re: merge - how to idenitfy the number of rows inserted and updated [message #290799 is a reply to message #290788] |
Mon, 31 December 2007 19:56   |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
The power of MERGE is that it doesn't NEED to separate inserts from updates. It's an annoyance that it does not report the numbers separately, because it does actually track them internally (for determining when statistics are stale - see ALL_TAB_MODIFICATIONS). But that's all it is: an annoyance.
You need to carefully examine your reason for wanting to know. Usually it is for the collection of runtime metadata set up on a pre-9i system. Collecting metadata is important for reconciliation and debugging, but we shouldn't get too hung up about it.
So now you have a decision: is it better to comply with the archaic metadata collection standard, or to exploit a new and powerful tool.
Ross Leishman
|
|
|
|
| Re: merge - how to idenitfy the number of rows inserted and updated [message #292046 is a reply to message #290781] |
Mon, 07 January 2008 10:18 |
smithsl
Messages: 9
Registered: August 2007
|
Junior Member |
|
|
I like your thinking, please continue to develop my thinking in this manner.
My reason for wanting the counts originates as below.
My task is to upsert data located in a data file, into a db table.
I chose to accomplish the task using an external table definition and the sql merge statement.
Along w/ my completed code, I must submit an Acceptance document.
For that document, I must derive a test case (to serve as a form of proof, I guess) which demonstrates I accomplished the task above.
I was looking to meet the documentation requirement by simply showing pre/post counts.
For example: Compare the expected inserts/update counts - to the actual insert/update counts. If they match, a bit of faith in the merge is achieved.
To grab and compare a few records pre and post processing would serve as a test case as well, perhaps a better one even, but it requires more code than the counts and its value is about the same as the counts.
Thus my question on this website.
|
|
|
|
|
|
| Re: merge - how to idenitfy the number of rows inserted and updated [message #292052 is a reply to message #292048] |
Mon, 07 January 2008 10:28 |
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
|
Senior Member |
|
|
Michel,
I don't think he trusts in it more, I think it is to produce a portion of the risk analysis/testing section(s) of the design document. To prove that the new program has inserted/updated/deleted the expected number of rows.
As the OP stated, he can do the same with some sample test cases, but it would be easier if the update/insert/delete numbers
are available.
|
|
|
|
| Re: merge - how to idenitfy the number of rows inserted and updated [message #292058 is a reply to message #290781] |
Mon, 07 January 2008 10:43 |
smithsl
Messages: 9
Registered: August 2007
|
Junior Member |
|
|
If I created my own "insert/update process/statement", I would still have the same requirement to write a test case, just as I do for the merge statement.
It's the documentation required at my job, that's pointing me down this road. I believe a simple count would satisfy the document requirement.
A "count output" from the merge statement would be an easy test case (proof I accomplished what they asked me to do) to put in the acceptance document.
It is not that I trust one method over the other. I trust that if the merge condition I specified is met, oracle will update, otherwise it will insert.
The response time is amazing on this website. Thank you.
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
