Skip to Main Content
Oracle PL/SQL for DBAs
book

Oracle PL/SQL for DBAs

by Arup Nanda, Steven Feuerstein
October 2005
Intermediate to advanced content levelIntermediate to advanced
454 pages
14h 44m
English
O'Reilly Media, Inc.
Content preview from Oracle PL/SQL for DBAs

Performing an Update Check

Let’s consider a slight twist on our earlier example. Instead of updating the COMM column, the user now updates the SAL column. Because SAL is the column used in the predicate, it will be interesting to see the result.

    SQL> UPDATE hr.emp SET sal = 1200;
    7 rows updated.

    SQL> UPDATE hr.emp SET sal = 1100;
    7 rows updated.

Only seven rows are updated, as expected. Now, let’s change the updated amount. After all, everyone deserves a better salary.

    SQL> UPDATE hr.emp SET sal = 1600;
    7 rows updated.

    SQL> UPDATE hr.emp SET sal = 1100;
    0 rows updated.

Note the last update. Why were no rows updated?

The answer lies in the first update. The first one updated the SAL column to 1,600, which is not satisfied by the filtering predicate “SAL <= 1500”. Thus, after the first update, all of the rows became invisible to the user.

This is a potentially confusing situation: the user can execute a SQL statement against rows, and the statement changes the access to those rows. During application development, this seeming data instability may create bugs or at least introduce a degree of unpredictability that makes debugging a challenge. To counter this behavior, we can take advantage of another ADD_POLICY parameter, update_check. Let’s take a look at the impact of setting this parameter to TRUE when we create a policy on the table.

 BEGIN DBMS_RLS.add_policy (object_name => 'EMP', policy_name => 'EMP_POLICY', function_schema => 'HR', policy_function => 'AUTHORIZED_EMPS', statement_types ...
Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Oracle PL/SQL Best Practices

Oracle PL/SQL Best Practices

Steven Feuerstein
Expert Oracle PL/SQL

Expert Oracle PL/SQL

Ron Hardman, Michael McLaughlin
Oracle PL/SQL For Dummies

Oracle PL/SQL For Dummies

Michael Rosenblum, Paul Dorsey

Publisher Resources

ISBN: 0596005873Supplemental ContentErrata Page