[Webinar] Streamline your web hosting managementRegister Today

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 196
  • Last Modified:

oracle implicit sql - fuctions

I have a anonymous block sql that updates a set of rows from a table I get a message like 2 rows updated or 9 rows updated, is there any way that I can get more information from that statement like for example I want to print all the eligible rows that got updated and list them after the update statement is executed.. is there any sql% functions that will hold the rows before it updates or just the count of rows that got updated..
  • 2
1 Solution
Guy Hengel [angelIII / a3]Billing EngineerCommented:
no, that is not possible with the update itself.

the only way to do something like this is to:
1) create a table with (1) column for the primary key values of the rows to be updated
2) insert the pk value for the eligible rows to be updated
3) select the data using that list as input criteria
4) update the original table using again that list as input criteria

however, this has eventually a drawback that between the time of 2) and 4), some rows might have been updated from elsewhere and should no longer be updated on step 4).
to solve that, you would need to create a high transaction level, which would have a drawback that any other session requesting to update the affected rows (or even the entire table) will be locked for too long.

that said: why do you want this information? just for curiousity?
mahjagAuthor Commented:
it is the for the same reason that I am asking that some other application has changed data and the sql that it updates is not what is expected.. how to set the transaction level to be high?
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I think you should simply review the update statement.
resp, transform it into a SELECT statement to see what it is doing, so you can find out what is going wrong.

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now