Oracle Safety Blankets: Insulating Your Applications Against Database Changes

July 29, 2003

Synopsis. Changes to Oracle database objects can wreak unexpected havoc on the applications that depend on them, especially when the database objects are invalidated even for a brief period. This article discusses some techniques that any DBA can put into practice to limit the impact of database object changes on dependent applications.

As a wise old philosopher once said, "The only certainty is change." As Oracle DBAs, we are responsible for managing changes to database objects on a regular basis. Depending on the stability of your IT organization and the stability of the applications that your developers deploy and support, you may be called upon to make changes to database objects on a daily or even hourly basis.

Since I am responsible for 24x7 support for my client's databases, one responsibility I take very seriously is to make sure that our applications continue to deliver stable performance in spite of necessary database maintenance. However, I have found that if I plan ahead, I can create "safety blankets" or cushions that insulate the applications from the database(s) that they access.

Safety Blankets

Use base views to insulate applications.
In its simplest form, a base view is nothing more than a view referencing all columns in a database table. I typically create a base view whenever I add a new table to the database. Once the base view is created, I also create a public synonym for the base view and then issue the necessary object permissions to the appropriate roles.

Since Oracle permits DML operations against the base table, I can also redirect all DML operations to use the base view instead of the base table. Here is an example using the EMPLOYEES table in the HR demo schema, which gives the OLTPROLE role full DML access to the view:

CREATE OR REPLACE VIEW hr.bv_employees AS
SELECT * FROM hr.employees
/
CREATE PUBLIC SYNONYM employees FOR hr.bv_employees;
GRANT SELECT, INSERT, UPDATE, DELETE ON hr.bv_employees TO OLTPROLE;

A big advantage of a base view is that I can now point the operations important to applications - reading and writing data - at the base view while simultaneously isolating the underlying table against really destructive operations like DROP TABLE or TRUNCATE TABLE. I have also provided a margin of safety that prevents a junior DBA or overzealous developer from dropping a critical database object by accident or in haste.

A few caveats: Note that if new columns are added to the base table, they are not automatically added to the base view unless I specifically recompile the base view. This is a double-edged sword, of course, because while it can be used to insulate against change to the dependent objects, it also means new columns aren't immediately available to those objects. In addition, remember that if a column with a constraint is left out of the base view - for example, it has a NOT NULL constraint without a DEFAULT value provided, or it has a CHECK constraint - and it is not included in the base view column list, an INSERT issued against a base view will likely fail.

Use base views to isolate application access to specific data.
Since column aliases can be specified for a view, we can use this feature to limit a user's view of the data returned. Using the HR.EMPLOYEES table again as our example, let's say I want to limit access for the OLTPUSER role to just the columns required to initially add a new employee to the company - in other words, just the columns that have NOT NULL constraints:

DROP VIEW hr.bv_employees;
CREATE OR REPLACE VIEW hr.bv_employees (
empid, 
fname, 
lname,
email,
hire_date,
job_id) 
 AS
SELECT
    employee_id, 
    first_name,
    last_name,
    email,
    hire_date,
    job_id
  FROM hr.employees
/

DROP PUBLIC SYNONYM employees;
CREATE PUBLIC SYNONYM employees FOR bv_employees;
GRANT SELECT, INSERT, UPDATE, DELETE on hr.bv_employees TO oltprole;

Now if I perform DML against the EMPLOYEES base view from the OLTPUSER (which has been granted the OLTPROLE role), I can add an employee with just the required information:

INSERT INTO employees
VALUES (501, 'Damien', 'McGillicudy', 'damienm@oracle.com', TO_DATE('12/31/1999'), 'FI_ACCOUNT');
COMMIT;


SQL> SELECT *
  2    FROM employees
  3   WHERE empid >= 500
  4   ORDER BY empid;

     EMPID FNAME                LNAME                                           
---------- -------------------- -------------------------                       
EMAIL                     HIRE_DATE           JOB_ID                            
------------------------- ------------------- ----------                        
       501 Damien               McGillicudy                                     
damienm@oracle.com        12/31/1999 00:00:00 FI_ACCOUNT                        

Use join views for more complex requirements.
Using a join view carries the previous examples to the next level: allowing an application to see information from more than one table. Expanding on our prior example:

DROP VIEW hr.bv_employees;
CREATE OR REPLACE VIEW hr.bv_employees (
empid, 
fname, 
lname,
email,
hire_date,
job_id,
jobtitle,
deptname) 
 AS
SELECT
    e.employee_id, 
    e.first_name,
    e.last_name,
    e.email,
    e.hire_date,
    e.job_id,
    j.job_title,
    d.department_name
  FROM 
   hr.employees e,
   hr.jobs j,
   hr.departments d
 WHERE e.job_id = j.job_id
   AND e.department_id = d.department_id
/
DROP PUBLIC SYNONYM employees;
CREATE PUBLIC SYNONYM employees FOR bv_employees;
GRANT SELECT, INSERT, UPDATE, DELETE on hr.bv_employees TO oltprole;

Remember that when you create a complex view like this one, Oracle will permit you to apply DML against one and only one base table used in the view per SQL statement issued. In addition, INSERTs can only be made to a key-preserved table accessed by the view. A key-preserved table is one who's primary and unique keys will be unique in the result set returned by the view. (In the prior example, this would be the EMPLOYEE table.) The ALL_UPDATABLE_COLUMNS data dictionary view bears this out:

SQL> SELECT
  2     column_name,
  3     updatable,
  4     insertable,
  5     deletable
  6    FROM all_updatable_columns
  7   WHERE owner = 'HR' AND table_name = 'BV_EMPLOYEES';

COLUMN_NAME                    UPD INS DEL
------------------------------ --- --- ---
EMPID                          YES YES YES
FNAME                          YES YES YES
LNAME                          YES YES YES
EMAIL                          YES YES YES
HIRE_DATE                      YES YES YES
JOB_ID                         YES YES YES
JOBTITLE                       NO  NO  NO
DEPTNAME                       NO  NO  NO

Use packages to encapsulate functionality and standardize database access.
One of the marvelous things about Oracle packages is their ability to encapsulate all required functionality for a data domain into a handful of database objects. Our development teams now use packages to describe all the public attributes (columns) and methods (procedures and functions) for groups of base views, including all the traditional "get" and "set" object-oriented methods an application needs to interface with the database.

Moreover, since a package specification describes the public interface of the package body's functions and procedures, it has one big advantage over traditional stored functions and procedures: The package body can be compiled separately from the package specification. This means that unless the signature (i.e. the arguments or returned values) of the packaged function or procedure has changed, there is no need to recompile the specification. This can help to limit unnecessary recompilations of dependent objects.