• Author / Uploaded
• scribdfatih

• Categories
• Pl/Sql
• Oracle Database
• Subrutina
• SQL
• Compilador

Citation preview

Oracle Academy Introduction to PL/SQL Instructor Resource Guide INSTRUCTOR NOTES FOR SLIDES SECTION 12 LESSON 1 Slide 1: Understanding Dependencies No instructor notes for this slide Slide 2: What Will I Learn? No instructor notes for this slide Slide 3: Why Learn It? No instructor notes for this slide Slide 4: Tell Me / Show Me – Understanding Dependencies No instructor notes for this slide Slide 5: Tell Me / Show Me – Dependencies A procedure or function can directly or indirectly (through an intermediate view, procedure, function, or packaged procedure or function) reference the following objects: • Tables • Views • Sequences • Procedures • Functions • Packaged procedures or functions Slide 6: Tell Me / Show Me – Dependencies Summarized Point out that some object types (sometimes the same object) can be both dependent and referenced. This will become clearer in the following slides. Slide 7: Tell Me / Show Me – Local Dependencies Managing Local Dependencies If the referenced object is in a different database, the dependency is called a remote dependency. Because the two database have separate data dictionaries, some of the features described in this lesson work differently for remote dependencies. Slide 8: Tell Me / Show Me – Local Dependencies (continued) Assume that the structure of the table on which a view is based is modified. When you describe the view by using the DESCRIBE command, you get an error message that states that the object is invalid to describe. This is because DESCRIBE is not a SQL command and, at this stage, the

Oracle Academy

1 Database Programming with PL/SQL Copyright © 2007, Oracle. All rights reserved.

view is invalid because the structure of its base table is changed. If you query the view now, the view is recompiled automatically and you can see the result (if it is successfully recompiled). Although this example discusses automatic recompilation of a view, the same mechanisms apply to other dependent objects such as subprograms. Slide 9: Tell Me / Show Me – A Scenario of Local Dependencies The QUERY_EMP procedure directly references the EMPLOYEES table. The ADD_EMP procedure updates the EMPLOYEES table indirectly by using the EMP_VW simple view. Ask students: In each of the following cases, will the ADD_EMP procedure be invalidated, and will it successfully recompile when next invoked? 1. The internal logic of the QUERY_EMP procedure is modified. 2. A new column is added to the EMPLOYEES table. 3. The EMP_VW view is dropped. Answers: 1. ADD_EMP will not be invalidated because it is not directly or indirectly dependent on QUERY_EMP. 2. ADD_EMP will be invalidated because it is indirectly dependent on EMPLOYEES. When ADD_EMP is next invoked, it will recompile successfully (and so will EMP_VW). 3. ADD_EMP will be invalidated because it is directly dependent on EMP_VW. When next invoked, it will not recompile successfully because its referenced object (EMP_VW) no longer exists. Slide 10: Tell Me / Show Me – Displaying Direct Dependencies by Using USER_DEPENDENCIES Determine which database objects to recompile manually by displaying direct dependencies from the USER_DEPENDENCIES data dictionary view. Examine the ALL_DEPENDENCIES and DBA_DEPENDENCIES views, each of which contains the additional column OWNER, which references the owner of the object. Note: the *_DEPENDENCIES views do not show indirect dependencies. For these we need to run the utldtreel.sql script as shown in the next slide. Slide 11: Tell Me / Show Me – Displaying Direct and Indirect Dependencies Display direct and indirect dependencies from additional user views called DEPTREE and IDEPTREE; these views are provided by Oracle. Example 1. Make sure that the utldtree.sql script has been executed. This script is located in the $ORACLE_HOME/rdbms/admin folder. (This script is supplied in the lab folder of your class files.) Each user needs to do this only once.

Oracle Academy

2 Database Programming with PL/SQL Copyright © 2007, Oracle. All rights reserved.

2. Populate the DEPTREE_TEMPTAB table with information for a particular referenced object by invoking the DEPTREE_FILL procedure. There are three parameters for this procedure: object_type: Type of the referenced object object_owner: Schema of the referenced object object_name: Name of the referenced object Slide 12: Tell Me / Show Me – Displaying Direct and Indirect Dependencies (continued) Display a tabular representation of all dependent objects by querying the DEPTREE view. Display an indented representation of the same information by querying the IDEPTREE view, which consists of a single column named DEPENDENCIES. For example, SELECT * FROM ideptree; provides a single column of indented output of the dependencies in a hierarchical structure. Slide 13: Tell Me / Show Me – another Scenario of Local Dependencies Predict the effect that a change in the definition of a procedure has on the recompilation of a dependent procedure. Suppose that the RAISE_SAL procedure updates the EMPLOYEES table directly, and that the REDUCE_SAL procedure updates the EMPLOYEES table indirectly by way of RAISE_SAL. In each of the following cases, will the REDUCE_SAL procedure successfully recompile? 1. The internal logic of the RAISE_SAL procedure is modified. 2. One of the formal parameters to the RAISE_SAL procedure is eliminated. Answers: 1. REDUCE_SAL will successfully recompile because its expected parameters have not changed, therefore its invocation from REDUCE_SAL is still valid. 2. REDUCE_SAL will not successfully recompile because one of its actual parameters in its call to RAISE_SAL no longer exists. Slide 14: Tell Me / Show Me – A Third Scenario of Local Naming Dependencies Be aware of the subtle case in which the creation of a table, view, or synonym may unexpectedly invalidate a dependent object because it interferes with the Oracle server hierarchy for resolving name references. Predict the effect that the name of a new object has upon a dependent procedure. Suppose that your QUERY_EMP procedure originally referenced a public synonym called EMPLOYEES. However, you have just created a new table called EMPLOYEES within your own schema.

Oracle Academy

3 Database Programming with PL/SQL Copyright © 2007, Oracle. All rights reserved.

Questions: 1. Does this change invalidate the procedure? Answer: Yes 2. Which of the two EMPLOYEES objects does QUERY_EMP reference when the procedure recompiles? Answer: your own EMPLOYEES table. 3. Now suppose that you drop your private EMPLOYEES table. Does this invalidate the procedure? Answer: Yes. 4. Now what happens when the procedure recompiles? Answer: It will recompile successfully and reference the public synonym. You can track security dependencies in the USER_TAB_PRIVS data dictionary view. Slide 15: Tell Me / Show Me – Recompiling a PL/SQL Program Unit If the recompilation is successful, the object becomes valid. If not, the Oracle server returns an error and the object remains invalid. When you recompile a PL/SQL object, the Oracle server first recompiles any invalid object on which it depends. Procedure: Any local objects that depend on a procedure (such as procedures that call the recompiled procedure or package bodies that define the procedures that call the recompiled procedure) are also invalidated, even if they were previously valid. This is because the recompiled procedure could have been edited before recompilation. Packages: The COMPILE PACKAGE option recompiles both the package specification and the body, regardless of whether it is invalid. The COMPILE SPECIFICATION option recompiles the package specification. Recompiling a package specification invalidates any local objects that depend on the specification, such as subprograms that use the package. Note that the body of a package also depends on its specification. The COMPILE BODY option recompiles only the package body, and does not invalidate objects dependent on the specification. Triggers: Explicit recompilation eliminates the need for implicit run-time recompilation and prevents associated run-time compilation errors and performance overhead. The DEBUG option instructs the PL/SQL compiler to generate and store the code for use by the PL/SQL debugger. Slide 16: Tell Me / Show Me – Unsuccessful Recompilation Sometimes a recompilation of dependent procedures is unsuccessful (for example, when a referenced table is dropped or renamed). The success of any recompilation is based on the exact dependency. If a referenced view is recreated, any object that is dependent on the view needs to be recompiled. The success of the recompilation depends on the columns that the view now contains, as well as the columns that the dependent objects require for their execution. If the required columns are not part of the new view, then the object remains invalid. Oracle Academy

4 Database Programming with PL/SQL Copyright © 2007, Oracle. All rights reserved.

Slide 17: Tell Me / Show Me – Successful Recompilation The recompilation of dependent objects is successful if: • New columns are added to a referenced table • All INSERT statements include a column list • No new column is defined as NOT NULL When a private table is referenced by a dependent procedure and the private table is dropped, the status of the dependent procedure becomes invalid. When the procedure is recompiled (either explicitly or implicitly) and a public table exists, the procedure can recompile successfully but is now dependent on the public table. The recompilation is successful only if the public table contains the columns that the procedure requires; otherwise, the status of the procedure remains invalid. Slide 18: Tell Me / Show Me – Recompilation of Procedures You can minimize recompilation failure by following the guidelines that are shown in the slide. Slide 19: Tell Me / Show Me – Packages and Dependencies You can simplify dependency management and avoid unnecessary invalidations with packages when referencing a package procedure or function from a stand-alone procedure or function. • If the package body changes and the package specification does not change, then the stand-alone procedure that references a package construct remains valid. • If the package specification changes, then the outside procedure referencing a package construct is invalidated, as is the package body. Slide 20: Tell Me / Show Me – Packages and Dependencies (continued) Managing Dependencies (continued) If a stand-alone procedure that is referenced within the package changes, then the entire package body is invalidated, but the package specification remains valid. Therefore, it is recommended that you bring the procedure into the package. Slide 21: Summary No instructor notes for this slide Slide 22: Try It / Solve It No instructor notes for this slide

Oracle Academy

5 Database Programming with PL/SQL Copyright © 2007, Oracle. All rights reserved.

PRACTICE SOLUTIONS SECTION 12 LESSON 1 - Understanding Dependencies Terminology No new vocabulary for this lesson. Try It / Solve It 1. The code for the new_emp procedure and the check_dept function is below. These subprograms are referenced in the following questions. You may recognize these subprograms from previous exercises. If you do not have these subprograms in your schema, then create them by copying and executing the code. CREATE OR REPLACE PROCEDURE new_emp_proc (p_empid IN employees.employee_id%TYPE, p_fname IN employees.first_name%TYPE, p_lname IN employees.last_name%TYPE, p_email IN employees.email%TYPE, p_hdate IN employees.hire_date%TYPE, p_job IN employees.job_id%TYPE, p_dept_id IN employees.department_id%TYPE) IS BEGIN IF check_dept(p_dept_id) THEN INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, job_id, department_id) VALUES (p_empid, p_fname, p_lname, p_email, p_hdate, p_job, p_dept_id); ELSE DBMS_OUTPUT.PUT_LINE('Invalid department number given, please try again.'); END IF; END; CREATE OR REPLACE FUNCTION check_dept (p_dept_in IN departments.department_id%TYPE) RETURN BOOLEAN IS v_dept departments.department_id%TYPE; BEGIN SELECT department_id INTO v_dept FROM departments WHERE department_id = p_dept_in; RETURN TRUE; EXCEPTION WHEN no_data_found THEN RETURN FALSE; END; Oracle Academy

6 Database Programming with PL/SQL Copyright © 2007, Oracle. All rights reserved.

A. In your own words, explain why the issue of dependencies is important. Think of the employees table, the new_emp procedure and the check_dept function and use them as examples in your explanation. If the employees table is altered, depending on the changes, the new_emp procedure may or may not work. For example, if a referenced column is dropped or a new required column is added, the procedure may be referencing something that no longer exists or may not be referencing something it needs. Similarly, if the check_dept function was dropped or had its argument list changed, the call to the function from new_emp would no longer work. B. What Data Dictionary view would you use to check to see if a dependent object had its status invalidated? Describe the view in Application Express, and retrieve the status of the following objects in your schema: employees table, the new_emp procedure, and the check_dept function. DESCRIBE user_objects; SELECT object_name, object_type, status FROM user_objects WHERE object_name IN ('EMPLOYEES','NEW_EMP','CHECK_DEPT'); 2. Think about your employees table, your new_emp procedure and your check_dept funtion. Which are dependent objects and which are referenced objects? What object is also involved that is not mentioned here and how is it related? Which dependencies are direct and which are indirect? Dependent

Referenced

Direct/Indirect

new_emp procedure

employees table

Direct

new_emp procedure

check_dept function

Direct

check_dept function

departments table

Direct

new_emp procedure

departments table

Indirect

The departments table was not mentioned. The check_dept function is directly dependent on this table, and the new_emp procedure is indirectly dependent on it (via check_dept).

Oracle Academy

7 Database Programming with PL/SQL Copyright © 2007, Oracle. All rights reserved.

3. Which data dictionary view shows information about dependencies? Describe it. Then query it to retrieve the name and type of all objects that are dependent on the employees table. Sort your results by object type. DESCRIBE user_dependencies SELECT name, type, referenced_name, referenced_type FROM user_dependencies WHERE referenced_name = 'EMPLOYEES' ORDER BY type; 4. Answer the following question utldtree: A. List the three steps involved in using the utldtree script. 1) Execute utldtree.sql to create the deptree_fill procedure, the deptree_temptab table and its two views: deptree and ideptree. 2) Execute the deptree_fill procedure for the object for which you wish to track dependencies. 3) Retrieve the dependency information using either the deptree view or the ideptree view. B. The utldtree script has already been run for you. Check this by describing the four objects which it has created. DESCRIBE deptree_temptab DESCRIBE deptree_fill DESCRIBE deptree DESCRIBE ideptree C. Populate the table which utldtree created with dependency information for your departments table. BEGIN deptree_fill('TABLE','your-schema-name','DEPARTMENTS'); END; D. Using the deptree view, display the information you populated in step c. Explain the values which are displayed in the nested_level column. SELECT nested_level, type, name FROM deptree ORDER BY seq#; The nested_level shows whether a dependency is direct or indirect. The new_emp procedure is nested level 2 because it is indirectly dependent via check_dept. Oracle Academy

8 Database Programming with PL/SQL Copyright © 2007, Oracle. All rights reserved.

E. Now use the ideptree view to display the same information. SELECT * FROM ideptree; 5. Answer the following dependency questions: A. List four ways in which dependency failures can be minimized. • Declaring records with the %ROWTYPE attribute • Declaring variables with the %TYPE attribute • Querying with the SELECT * notation • Including a column list with INSERT statements B. Look again at the code of your new_emp procedure and your check_dept function that help with minimizing dependency failure. List two ways in which this code helps to minimize dependency failures. new_emp and check_dept use %TYPE rather than declaring specific type and size for variables. new_emp lists the columns on the INSERT that are provided in the VALUES clause.

Oracle Academy

9 Database Programming with PL/SQL Copyright © 2007, Oracle. All rights reserved.

6. Answer the following questions on the new_emp procedure: A. Modify the check_dept function so that the IN parameter p_dept_in is declared as NUMBER instead of departments.department_id%TYPE (this is not a good thing to do, but we’ll do it in this question anyway!). Recreate the function. Then look at the status of the new_emp procedure in user_objects. What happened and why? CREATE OR REPLACE FUNCTION check_dept (p_dept_in IN NUMBER) RETURN BOOLEAN IS v_dept departments.department_id%TYPE; BEGIN SELECT department_id INTO v_dept FROM departments WHERE department_id = p_dept_in; RETURN TRUE; EXCEPTION WHEN no_data_found THEN RETURN FALSE; END; SELECT object_name, status FROM user_objects WHERE object_name = ‘NEW_EMP’; The procedure is dependent on check_dept and therefore has been invalidated when check_dept was recompiled. B. State two ways in which the new_emp procedure could be recompiled. Would the recompilation be successful in this case? Recompile the procedure to check your answer. The procedure would be recompiled automatically the next time it is executed. Or we can recompile it ourselves by an ALTER statement. The recompilation would be successful because departments.department_id%TYPE and NUMBER are compatible datatypes, so the parameter list has not changed. ALTER PROCEDURE new_emp COMPILE; SELECT object_name, status FROM user_objects WHERE object_name = ‘NEW_EMP’;

Oracle Academy

10 Database Programming with PL/SQL Copyright © 2007, Oracle. All rights reserved.