Reference no: EM131402065

Assignment -1: Reviewing Dependency Information in the Data Dictionary

Two data dictionary views store information on dependencies: USER_OBJECTS and USER_DEPENDENCIES. Take a closer look at these views to examine the information in them:

1. In SQL Developer, issue a DESCRIBE command on the USER_OBJECTS view and review the available columns. Which columns are particularly relevant to dependencies? The STATUS column indicates whether the object is VALID or INVALID. The TIMESTAMP column is used in remote connections to determine invalidation.

2. Query the USER_OBJECTS view, selecting the OBJECT_NAME, STATUS, and TIMESTAMP columns for all procedures. Recall that you can use a WHERE clause to look for object types of PROCEDURE to list only procedure information.

3. Now issue a DESCRIBE command on the USER_DEPENDENCIES view to review the available columns. If you query this table for the name of a specific object, a list of all the objects it references is displayed. However, if you query for a specific referenced name, you see a list of objects that are dependent on this particular object.

4. Say you intend to make a modification to the BB_BASKET table and need to identify all dependent program units to finish recompiling. Run the following query to list all objects that are dependent on the BB_BASKET table: SELECT name, type
FROM user_dependencies

WHERE referenced_name = 'BB_BASKET';

Assignment 2: Testing Dependencies on Stand-Alone Program Units
In this assignment, you verify the effect of object modifications on the status of dependent objects. You work with a procedure and a function.

1. In a text editor, open the assignment08-02.txt file in the Chapter08 folder. This file contains statements to create the STATUS_CHECK_SP procedure and the STATUS_DESC_SF function. Review the code, and notice that the procedure includes a call to the function. Use the code in this file to create the two program units in SQL Developer.

2. Enter and run the following query to verify that the status of both objects is VALID:
SELECT object_name, status
FROM user_objects
WHERE object_name IN ('STATUS_CHECK_SP','STATUS_DESC_SF');

3. The STATUS_DESC_SF function adds a description of the numeric value for the IDSTAGE column. The company needs to add another order status stage for situations in which credit card approval fails. In SQL Developer, modify the function by adding the following ELSIF clause, and compile it. (Don't compile or run the function again.)
ELSIF p_stage = 6 THEN
lv_stage_txt := 'Credit Card Not Approved';

4. Does the modification in Step 3 affect the STATUS_CHECK_SP procedure's status? Verify by repeating the query in Step 2. The procedure is dependent on the function, so it's now INVALID and must be recompiled.
5. Call the procedure for basket 13, as shown in the following code:
DECLARE
lv_stage_numNUMBER(2);
lv_desc_txt VARCHAR2(30);
BEGIN
status_check_sp(13,lv_stage_num,lv_desc_txt); END;

6. Repeat the query in Step 2 to verify the STATUS_CHECK_SP procedure's status. The procedure now shows the status VALID as a result of automatic recompiling when the procedure was called.

Assignment 3: Testing Dependencies on Packaged Program Units
In this assignment, you verify the effect of object modifications on the status of dependent objects. You work with a procedure and a packaged function.

1. In a text editor, open the assignment08-03.txt file in the Chapter08 folder. This file contains statements to create the STATUS_CHECK_SP procedure and the LOOKUP_PKG package. Review the code, and note that the procedure includes a call to the packaged function STATUS_DESC_PF. Use the code in this file to create the procedure and package in SQL Developer.

2. Use the following query to verify the procedure's status:
SELECT status FROM user_objects
WHERE object_name = 'STATUS_CHECK_SP';

3. The function adds a description of the numeric value for the IDSTAGE column. The company needs to add another order status stage for situations in which credit card approval fails. In SQL Developer, edit the function by adding the following ELSIF clause to the packaged function:
ELSIF p_stage = 6
THEN lv_stage_txt := 'Credit Card Not Approved';

4. Does the modification in Step 3 affect the STATUS_CHECK_SP procedure's status? Verify that it's still VALID by repeating the query in Step 2. The procedure is dependent on the function; however, if the referenced program unit is in a package, only changes to the package specification result in the dependent object's status changing to INVALID.

Assignment 4: Testing Remote Object Dependencies
As you learned, program unit calls that use a database link to another database are called remote dependencies and act differently with program unit invalidation, as you see in the following steps:

1. Create a database link named dblink2. If you have a second Oracle database running, use a valid connection string for that database. Otherwise, use a connection string for the database you're connected to.

2. In a text editor, open the assignment08-04.txt file in the Chapter08 folder. This file contains statements to create the STATUS_CHECK_SP procedure and the STATUS_DESC_SF function. The procedure uses a database link when calling the function, which is treated as a remote database connection. (Note: If your database link connects toanother database, be sure to create the function on that database.) Use the code in this file to create the two program units in SQL Developer.

3. Check the procedure's status with a query of a data dictionary view.

4. The function adds a description of the numeric value for the IDSTAGE column. The company needs to add another order status stage for situations in which credit card approval fails. Return to the text file and add the following ELSIF clause to the packaged function:
ELSIF p_stage = 6
THEN lv_stage_txt := 'Credit Card Not Approved';

5. Copy the package code and paste it in SQL Developer to rebuild with the modifications. Does the modification in Step 4 affect the STATUS_CHECK_SP procedure's status? Verify that it's still VALID. The procedure is dependent on the function; however, because it's a remote dependency, the status isn't checked at the time the referenced object is modified.

6. Try calling the procedure, and verify its status again.

7. Call the procedure a second time. What happens?

Assignment 5: Identifying Dependencies
At this point, you have created a variety of database objects in your schema. Use an Oracle tool to identify all the direct and indirect dependencies on the BB_BASKET table, and produce dependency lists in two different formats. Identify each object as a direct or an indirect dependency, and describe the path of dependency for each indirectly dependent object.

Assignment 6: Reviewing the utldtree.sql Script
In Windows, search for the utldtree.sql file. It should be in the database directory under the rdbms\admin subdirectory. Open the file in a text editor and review the script. List all the objects that are created (name and type), and write a brief description of how each object is used for tracking dependencies.

Assignment 7: Avoiding Recompilation Errors
All applications undergo modifications, and as a developer, you should strive to produce code that helps minimize maintenance. Describe two coding techniques that help prevent recompilation errors after referenced objects have been modified, and explain briefly how these techniques help prevent recompilation errors.

Assignment 8: Defining Types of Dependencies
In this chapter, you learned about direct, indirect, and remote dependencies. Define these dependency types, and explain how they differ in program unit invalidation and recompilation.