how to find the record Id's that were touched when a SQL (select) was run in Oracle?

Question

I'm running select SQL queries on an oracle database. These SQL could select multiple columns from multiple tables in a single sql. I need to be able to update a hit count of all the records that were touched. Is there a way to do this?

Ex: a user may ask the program to run a particular select query and while returning this data, I should be able to update a column of these on records. If there is a way to find a object ID or a hidden column that I can always insert into the list of columns being selected, I could later go through all the records selected and extract this ID and update the records. Is there something like this available in oracle? Please let me know if you need any more info. Thanks

Answer 1

The usual way to do this would be to add columns to the tables that meet the requirements you need.

A report date field and a view count field perhaps.

But the control for these fields requires a seperate update statement to be processed at the same time as the select. This probably means that you cannot use a standard reporting tool and will have to write some code to produce the report you want.



Also there may be a requirement to re-produce a report. A paper jam in the printer, or a simple lost or damaged original. What then?



Have fun.

Answer 2

Why don't you just add an auto-increment ID column to your table(s)? Then you can select it along with the data you're looking for, and use those IDs to update the hit count afterward.



Edit:

You would add the column to your select query the same way you add any column to your select query. You know which table it comes from because you control which columns are selected in your query.

Answer 3

I'm not a 100% sure I understand what you are trying to achieve BUT, oracle has a "hidden" field called a ROWID that uniquely identifies each record in a table. You can add each tables rowid to your query and then you know which records to go back to.



There are a few exceptions though, when a table is created with movable rowids OR a table that has been partitioned.