these are the querys

CREATE OR REPLACE FUNCTION status_desc_sf (p_stage IN NUMBER) RETURN VARCHAR2 IS lv_stage_txt VARCHAR2(30); BEGIN IF p_stage = 1 THEN lv_stage_txt := 'Order Submitted'; ELSIF p_stage = 2 THEN lv_stage_txt := 'Accepted, sent to shipping'; ELSIF p_stage = 3 THEN lv_stage_txt := 'Backordered'; ELSIF p_stage = 4 THEN lv_stage_txt := 'Cancelled'; ELSIF p_stage = 5 THEN lv_stage_txt := 'Shipped'; END IF; RETURN lv_stage_txt; END;

CREATE OR REPLACE PROCEDURE status_check_sp (p_bask IN NUMBER, p_stage OUT NUMBER, p_desc OUT VARCHAR2) IS BEGIN SELECT idstage INTO p_stage FROM bb_basketstatus WHERE idBasket = p_bask; p_desc := status_desc_sf(p_stage); END; / CREATE OR REPLACE PACKAGE lookup_pkg IS FUNCTION status_desc_pf (p_stage IN NUMBER) RETURN VARCHAR2; END;

CREATE OR REPLACE PACKAGE BODY lookup_pkg IS FUNCTION status_desc_pf (p_stage IN NUMBER) RETURN VARCHAR2 IS lv_stage_txt VARCHAR2(30); BEGIN IF p_stage = 1 THEN lv_stage_txt := 'Order Submitted'; ELSIF p_stage = 2 THEN lv_stage_txt := 'Accepted, sent to shipping'; ELSIF p_stage = 3 THEN lv_stage_txt := 'Backordered'; ELSIF p_stage = 4 THEN lv_stage_txt := 'Cancelled'; ELSIF p_stage = 5 THEN lv_stage_txt := 'Shipped'; END IF; RETURN lv_stage_txt; END; END;

CREATE OR REPLACE PROCEDURE status_check_sp (p_bask IN NUMBER, p_stage OUT NUMBER, p_desc OUT VARCHAR2) IS BEGIN SELECT idstage INTO p_stage FROM bb_basketstatus WHERE idBasket = p_bask; p_desc := lookup_pkg.status_desc_pf(p_stage); END; CREATE OR REPLACE FUNCTION status_desc_sf (p_stage NUMBER) RETURN VARCHAR2 IS lv_stage_txt VARCHAR2(30); BEGIN IF p_stage = 1 THEN lv_stage_txt := 'Order Submitted'; ELSIF p_stage = 2 THEN lv_stage_txt := 'Accepted, sent to shipping'; ELSIF p_stage = 3 THEN lv_stage_txt := 'Backordered'; ELSIF p_stage = 4 THEN lv_stage_txt := 'Cancelled'; ELSIF p_stage = 5 THEN lv_stage_txt := 'Shipped'; END IF; RETURN lv_stage_txt; END;

CREATE OR REPLACE PROCEDURE status_check_sp (p_bask IN NUMBER, p_stage OUT NUMBER, p_desc OUT VARCHAR2) IS BEGIN SELECT idstage INTO p_stage FROM bb_basketstatus WHERE idBasket = p_bask; p_desc := status_desc_sf@dblink2(p_stage); END;

Assignment 8-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 youre 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 3. Check the procedures 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 Does the modification in Step 4 affect the STATUS_CHECK_SP procedures status? Verify that its still VALID. The procedure is dependent on the function; however, because its a remote dependency, the status isnt 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 8-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 8-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 8-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-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

Hands-On Assignments Part II Assignment 8-9: Using Compilation Parameters Open and compile the DDCKBAL_SP procedure. Run a command to display all compiler parameter settings for the procedure; the results should reflect the settings for your current Oracle session . Next, run a command to make sure only informational PL/SQL compiler warnings are enabled in the session. Recompile the procedure again, and display all compiler parameter settings after your change to the warnings parameter.

Assignment 8-10: Using Conditional Compilation Create a procedure named DD_COMP1_SP that compiles to contain a single DBMS_OUTPUT statement based on a compilation flag value. If the compilation flag value is TRUE, this value should be displayed. If the compilation flag value is FALSE, this value should be displayed. Name the compilation flag ASSIGN. Create and run the procedure for both compiler flag values. In addition, run a command that displays the compile source code for the procedure when the compiler flag value is TRUE.

Assignment 8-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 youre 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 3. Check the procedures 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 Does the modification in Step 4 affect the STATUS_CHECK_SP procedures status? Verify that its still VALID. The procedure is dependent on the function; however, because its a remote dependency, the status isnt 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 8-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 8-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 8-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-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

Hands-On Assignments Part II Assignment 8-9: Using Compilation Parameters Open and compile the DDCKBAL_SP procedure. Run a command to display all compiler parameter settings for the procedure; the results should reflect the settings for your current Oracle session . Next, run a command to make sure only informational PL/SQL compiler warnings are enabled in the session. Recompile the procedure again, and display all compiler parameter settings after your change to the warnings parameter.

Assignment 8-10: Using Conditional Compilation Create a procedure named DD_COMP1_SP that compiles to contain a single DBMS_OUTPUT statement based on a compilation flag value. If the compilation flag value is TRUE, this value should be displayed. If the compilation flag value is FALSE, this value should be displayed. Name the compilation flag ASSIGN. Create and run the procedure for both compiler flag values. In addition, run a command that displays the compile source code for the procedure when the compiler flag value is TRUE.