Solved

Can creating and compiling Packages corrupt an Oracle Database?

Posted on 2004-08-05
6
599 Views
Last Modified: 2013-12-11
Just want to know how careful need to be when creating and compiling pakages...

Can creating and compliling Packages corrupt an Oracle Database?

Many Thanks



0
Comment
Question by:oogooglies
6 Comments
 
LVL 7

Accepted Solution

by:
BobMc earned 125 total points
Comment Utility
No not really, you might invalidate some other stored code, or require other objects to be recompiled, but this is normally taken care of when the objects are first used.

If you are creating new packages, then the worst that could really happen is that your new packages wont work.

HTH
Bob
0
 
LVL 4

Assisted Solution

by:andertst
andertst earned 125 total points
Comment Utility
Well, I guess the truth is it depends on what your new package does.  It could alter session and set an event that simulates a corrupt database which would crash the database.  It could delete data that is needed for the business and then business will say it is corrupt.

Generally, BobMc is right and simply issuing a "create package" statement should not cause any problems in the database.

Stephen
0
 
LVL 47

Assisted Solution

by:schwertner
schwertner earned 125 total points
Comment Utility
Yes, it is true. CREATE OR REPLACE PACKAGE can not harm the instance.

BUT changes in
1. Tables, views used in the package
2. Changes in procedures, functions, packages used in the package can invalidate the package.

This can lead to colaps of the first run of the package subroutine, but after that Oracle will automatically recompile the package.
0
 
LVL 4

Assisted Solution

by:ramumorla
ramumorla earned 125 total points
Comment Utility
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.


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.



LBL: Looking Before Leaping
The preceding techniques work just fine when planning a set of new objects for deployment, or revising an existing set of objects before deployment. However, I have also found that disruption to working applications is indirectly proportional to the planning for their deployment. In other words, a well-planned deployment usually protects applications against disruption caused by invalidation and recompilation of modified database objects.

Know what the impact is before invalidating an object.
Before I make a change to a database object, I make it a point to take a sanity check of the objects that will be invalidated. Obvious? Absolutely! And, I've obviously absolutely accidentally violated this common-sense advice several times over the years because I was hurried by an anxious developer or harried manager, usually to the diminution of an application's performance.

Here's some sample code that can help you identify what objects would be invalidated by a recompilation of an object (in this case, the EMPLOYEES table in the HR schema):


SQL> SET WRAP OFF
SQL> TTITLE CENTER "Parent and Dependent Objects"
SQL> BREAK ON par_typ SKIP 1 ON par_sts SKIP 1 ON par_obj SKIP 1 NODUPLICATES
SQL> COLUMN par_typ FORMAT A12 HEADING "Type"
SQL> COLUMN par_sts FORMAT A08 HEADING "Status"
SQL> COLUMN par_obj FORMAT A16 HEADING "Parent"
SQL> COLUMN dep_obj FORMAT A16 HEADING "Child"
SQL> COLUMN dep_typ FORMAT A12 HEADING "Type"
SQL> COLUMN dep_sts FORMAT A08 HEADING "Status"
SQL> SELECT
  2     O1.object_type par_typ,
  3     O1.status      par_sts,
  4     O1.object_name par_obj,
  5     O2.object_name dep_obj,
  6     O2.object_type dep_typ,
  7     O2.status      dep_sts
  8    FROM
  9     public_dependency PD,
 10     all_objects O1,
 11     all_objects O2
 12   WHERE PD.referenced_object_id = O1.object_id
 13     AND PD.object_id = O2.object_id
 14     AND O1.object_name = 'EMPLOYEES'
 15   ORDER BY par_obj;

                          Parent and Dependent Objects
Type         Status   Parent           Child            Type         Status
------------ -------- ---------------- ---------------- ------------ --------
TABLE        VALID    EMPLOYEES        PKG_SECURITY     PACKAGE BODY INVALID
                                       BV_EMPLOYEES     VIEW         VALID
                                       EMP_DETAILS_VIEW VIEW         VALID
                                       PKG_SECURITY     PACKAGE BODY VALID
                                       SECURE_EMPLOYEES TRIGGER      VALID
                                       UPDATE_JOB_HISTO TRIGGER      VALID

6 rows selected.

After recompiling, always check for invalidated objects. Then check again.
Sometimes I have seen situations where the UTLRP.SQL recompiler or third-party software does not recompile all recently invalidated objects. At a minimum, this can lead to some frustration; in the worst case, an application could be prevented from accessing the database for some time unless a user notices immediately.

For example, I recently spent about 90 minutes one afternoon helping a developer debug a PowerBuilder application that was returning an ORA-00942 "table not found" Oracle database error when run against our development database. The same code ran fine against the production database. The error seemed to appear and reappear intermittently even while we were debugging the application code.

I knew I had seen this type of error in the past, and spent several minutes racking my brain, when it finally hit me that invalidated objects were the culprit. It turned out that another developer had been dropping and recreating a table that was used by several dozen other database objects, but had neglected to recompile all of the invalidated dependent objects.

Watch out for global temporary tables.
One last caveat: If you are using global temporary tables (GTTs) to store and accumulate stateful information, be aware of the impact of altering GTTs on the applications that may be utilizing them indirectly.

I recently had to expand a column in a GTT from VARCHAR2(15) to VARCHAR2(25), a simple operation for a "normal" table. However, this particular GTT was being used by a package to store stateful information per each user session via the ON COMMIT PRESERVE ROWS option. Oracle steadfastly refused to allow the ALTER TABLE operation to complete until I had asked all users running the application to log out, thus freeing the GTT for modification. It only took a few moments to complete this, and only a few users were logged in at the time, but the impact could have been much worse if an emergency change to a GTT had to be deployed during peak business operations.


0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

728 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

10 Experts available now in Live!

Get 1:1 Help Now