oracle implicit sql - fuctions

Posted on 2009-04-21
Last Modified: 2013-12-18
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..
Question by:mahjag
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    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?

    Author Comment

    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?
    LVL 142

    Accepted Solution

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
    FreeBSD on EC2 FreeBSD ( is a robust Unix-like operating system that has been around for many years. FreeBSD is available on Amazon EC2 through Amazon Machine Images (AMIs) provided by FreeBSD developer and security office…
    This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
    This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now