ch08 - P L / S Q L Oracle10g Developer: PL/SQL Programming...

Info iconThis preview shows pages 1–11. Sign up to view the full content.

View Full Document Right Arrow Icon
P L / S Q L Oracle10 g Developer: PL/SQL Programming Chapter 8 Program Unit Dependencies
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
P L / S Q L Oracle10g Developer: PL/SQL Programming 2 Chapter Objectives After completing this lesson, you should be able to understand: Local program unit dependencies Direct and indirect dependencies Data dictionary information concerning dependencies Running the dependency tree utility
Background image of page 2
P L / S Q L Oracle10g Developer: PL/SQL Programming 3 Chapter Objectives (continued) After completing this lesson, you should be able to understand (continued): Identifying the unique nature of package dependencies Remote object dependency actions Using remote dependency invalidation methods Avoiding recompilation errors Granting program unit privileges
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
P L / S Q L Program Unit Dependencie s Program unit dependencies are the inter-relationships of objects as they relate to procedure, functions, and packages. We need to avoid complications that can be raised from object references. Oracle10g Developer: PL/SQL Programming 4
Background image of page 4
P L / S Q L Oracle10g Developer: PL/SQL Programming 5 Program Unit Dependencie s Relationships or dependencies determine the validity of any program unit after modifications to database objects that the program unit references A modification may cause the status of the program unit to be INVALID 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
Background image of page 5

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
P L / S Q L Rebuild database Load and run “c8Dbcreate.sql” Oracle10g Developer: PL/SQL Programming 6
Background image of page 6
P L / S Q L Local Dependency Activity CREATE OR REPLACE PROCEDURE order_total_sp (p_bsktid IN NUMBER, p_cnt OUT NUMBER, p_sub OUT NUMBER, p_ship OUT NUMBER, p_total OUT NUMBER) IS BEGIN SELECT sum(quantity), sum(quantity*price) INTO p_cnt, p_sub FROM bb_basketitem where idbasket = p_bsktid; ship_cost_sp(p_cnt, p_ship); p_total := NVL(p_sub, 0) + NVL(p_ship, 0); END; Oracle10g Developer: PL/SQL Programming 7 Any referenced objects? Any dependent object?
Background image of page 7

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
P L / S Q L Local Dependency Activity If we modify one of the referenced objects, the status of the dependent object is flagged as INVALID. It means that the program unit needs to be recompiled. The status of each database object can be checked via data dictionary view USER_OBJECTS. Oracle10g Developer: PL/SQL Programming 8
Background image of page 8
P L / S Q L Oracle10g Developer: PL/SQL Programming 9 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
Background image of page 9

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
L / S Q L Local Dependency Activity 1. Turn on SQL-PLUS 2. Input “column object_name format a20” 3. Input SELECT object_name, status FROM user_objects WHERE object_type = 'PROCEDURE'; / That is the way to check whether there is any invalid procedure. Oracle10g Developer: PL/SQL Programming
Background image of page 10
Image of page 11
This is the end of the preview. Sign up to access the rest of the document.

Page1 / 38

ch08 - P L / S Q L Oracle10g Developer: PL/SQL Programming...

This preview shows document pages 1 - 11. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online