Oracle: Insert, Update and Delete on complex views

Posted on 2012-08-19
Last Modified: 2012-08-22
I am looking for an detailed oversight on when can I and when can I not  update, insert or delete from complex views in Oracle? I seem to find some discussions on the topic via Google, but those do not seem to be final and covering the whole topic.
Question by:itnifl
    LVL 73

    Accepted Solution

    It basically boils down to 3 rules

    1 - do you have the requisite privilege on the view?
                   e.g.  grant insert on your_view to _some_user

    2 - does your action validate against the view's conditions when the "with check" option is present?
          e.g.  The view has a condition like   "AND some_date < sysdate"
                   and you try to insert a value for tomorrow, your insert will fail

    3 -  does the view maintain unique row identification?  This is the tricky one.
          This means when you update a row in the view, can that row be mapped back to a single row in a table?  if no, then you can't modify the view.  Note "instead of" triggers can get around this by intercepting the sql statement and, as the name suggests, do some other action "instead of" the insert,update or delete that was issued.
    LVL 2

    Author Comment

    I guess the view also needs to cover all the not null columns in the table, and also the primary key.
    LVL 73

    Assisted Solution

    if there are triggers or a default values for the not null columns then the view doesn't need to reference them.

    the primary key isn't necessary if there is a trigger to populate it on insert
    LVL 2

    Author Comment

    Yes, but then the triggers are filling the requirement

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
    Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
    This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
    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…

    733 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

    23 Experts available now in Live!

    Get 1:1 Help Now