Oracle9
This presentation is the property of its rightful owner.
Sponsored Links
1 / 16

Oracle9 i Developer: PL/SQL Programming PowerPoint PPT Presentation


  • 53 Views
  • Uploaded on
  • Presentation posted in: General

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

Download Presentation

Oracle9 i Developer: PL/SQL Programming

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

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


  • Login