slide1 n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Oracle9 i Developer: PL/SQL Programming PowerPoint Presentation
Download Presentation
Oracle9 i Developer: PL/SQL Programming

Loading in 2 Seconds...

play fullscreen
1 / 16

Oracle9 i Developer: PL/SQL Programming - PowerPoint PPT Presentation


  • 82 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 'Oracle9 i Developer: PL/SQL Programming' - 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
slide1

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