Oracle9
Download
1 / 16

Oracle9i Developer: PL - PowerPoint PPT Presentation


  • 74 Views
  • Uploaded on

Oracle9 i Developer: PL/SQL Programming. Chapter 7. Program Unit Dependencies. Objectives. Identify local program unit dependencies Determine direct and indirect dependencies View data dictionary information concerning dependencies Run the dependency tree utility

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Oracle9i Developer: PL' - manasa


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

Oracle9i Developer:

PL/SQL Programming

Chapter 7

Program Unit

Dependencies


Objectives
Objectives

  • Identify local program unit dependencies

  • Determine direct and indirect dependencies

  • View data dictionary information concerning dependencies

  • Run the dependency tree utility

  • Identify the unique nature of package dependencies

  • Understand remote object dependency actions

  • Use remote dependency invalidation methods

  • Avoid recompilation errors

  • Grant program unit privileges


Program unit dependencie s
Program Unit Dependencies

  • Relationships or dependencies determine the validity of any program unit after modifications to database objects that the program unit references

  • This validity determines the need for recompilation

  • A procedure calls a function: the procedure is a dependent object and the function is the referenced object


Brewbean s challenge
Brewbean’s Challenge

  • Need to take any steps possible to make the execution more efficient

  • Users have been hitting some unexpected errors related to recent modifications to the database and program units

  • In this light, need to review database dependencies and their impact


Local dependency activity
Local Dependency Activity

  • Status of program unit can be checked using USER_OBJECTS

  • When a referenced object is modified the status of the dependent object changes to INVALID

  • INVALID status indicates need for recompilation

  • ALTER COMPILE command used to recompile a program unit


Automatic recompilation
Automatic Recompilation

  • Upon execution of a program unit with an INVALID status, the system will automatically recompile

  • Drawbacks:

    • Recompilation of dependent objects tests the changes to the referenced objects which could raise errors at runtime

    • Recompilation processing occurs during runtime


Direct indirect dependencies
Direct & Indirect Dependencies

  • Direct – a procedure calls a function

  • Indirect – a procedure calls a procedure which calls a function: the dependency between the first procedure and the function is indirect

  • Indirect dependencies have same affect as direct dependencies


Data dictionary
Data Dictionary

  • USER_DEPENDENCIES identify direct dependencies

  • Use WHERE clause on name column to analyze a particular object

  • DBA_DEPENDENCIES will identify direct dependencies of objects in all schemas


Dependency tree utility
Dependency Tree Utility

  • Mechanism to map direct and indirect dependencies

  • Execute utldtree.sql script once to set up the feature

  • Deptree_fill procedure used to analyze an object

  • Two views:

    • Deptree: numeric scheme

    • Ideptree: indented scheme


Package dependencies
Package Dependencies

  • Modifications to package specification will change status of dependent objects

  • Modifications to package body only does NOT change status of dependent objects

  • Separation of code in packages:

    • Minimizes recompilation needs

    • Dependent objects to be developed prior to the package body being created


Remote object dependencies
Remote Object Dependencies

  • Database links are used to connect to other Oracle databases

  • Links allow calls to objects in other databases - these objects are called remote objects

  • When remote objects are modified, local dependent objects are not initially flagged as INVALID

  • Remote dependencies are not checked until runtime


Remote invalidation methods
Remote Invalidation Methods

  • Timestamp: compares the last date of modification of dependent and referenced objects

  • Signature: compares the parameter modes, data types, and order

  • Timestamp is the default method

  • Databases in different time zones generate unnecessary recompilation using the timestamp method


Avoiding recompilation errors
Avoiding Recompilation Errors

  • Use %TYPE and %ROWTYPE attributes

  • Use the ‘*’ notation in queries to select all columns

  • Use a column list in INSERT statements


Program unit privileges

System Privilege

Explanation

CREATE PROCEDURE

Allows a user to create, modify, and drop program units within their own schema.

CREATE ANY PROCEDURE

Allows a user to create program units in any schema. Does not allow the modification or dropping of the program units.

ALTER ANY PROCEDURE

Allows a user to modify program units in any schema.

DROP ANY PROCEDURE

Allows a user to drop program units in any schema.

EXECUTE ON program_unit_name

Allows a user to execute a specific program unit.

EXECUTE ANY PROCEDURE

Allows a user to execute program units in any schema.

Program Unit Privileges


Privileges data dictionary

View Name

Description

SESSION_PRIVS

Shows all privileges of the current schema, direct and indirect

SESSION_ROLES

Shows all roles granted to the current schema

USER_SYS_PRIVS

Shows only direct privileges of the current schema

USER_ROLE_PRIVS

Shows only direct roles granted to the current schema

Privileges - Data Dictionary


Summary
Summary

  • Program unit status changes when referenced object is modified

  • INVALID status indicates a need for recompilation

  • Direct and indirect dependencies both affect status

  • Dependency tree utility allows mapping of both direct and indirect dependencies

  • Packages minimize recompilation needs

  • Remote dependencies do not update status until runtime

  • Appropriate privileges needed to create and use program units


ad