Example: CREATE OR REPLACE PACKAGE comm_package IS g_comm_rate NUMBER := 0.1; /* initialized to 10% */ PROCEDURE reset_comm_rate(v_comm_rate IN NUMBER); END comm_package; SQL> run CREATE OR REPLACE PACKAGE BODY comm_package IS FUNCTION validate_comm_rate (v_comm_rate IN NUMBER) RETURN BOOLEAN IS v_max_comm_rate NUMBER; BEGIN SELECT MAX(comm / sal) INTO v_max_comm_rate FROM pub_emp; IF v_comm_rate > v_max_comm_rate THEN RETURN (FALSE); ELSE RETURN (TRUE); END IF; END validate_comm_rate; PROCEDURE reset_comm_rate(v_comm_rate IN NUMBER) IS v_valid BOOLEAN; BEGIN v_valid :=validate_comm_rate(v_comm_rate); IF v_valid = TRUE THEN g_comm_rate := v_comm_rate; ELSE RAISE_APPLICATION_ERROR (-20210,'Invalid comm rate.'); END IF; END reset_comm_rate; END comm_package; SQL> run SQL> execute comm_package.reset_comm_rate(.15); SQL> execute comm_package.reset_comm_rate(10.00); BEGIN comm_package.reset_comm_rate(10.00); END; * ERROR at line 1: ORA-20210: Invalid comm rate. ORA-06512: at "BENJAMIN.COMM_PACKAGE", line 24 ORA-06512: at line 1 Q1. CREATE OR REPLACE PACKAGE explanation_package IS g_explanation s_inventory.out_of_stock_explanation%TYPE; PROCEDURE reset_explanation (v_explanation IN s_inventory.out_of_stock_explanation%TYPE); END explanation_package; SQL> run CREATE OR REPLACE PACKAGE BODY explanation_package IS FUNCTION validate_explanation (v_explanation IN s_inventory.out_of_stock_explanation%TYPE) RETURN BOOLEAN IS BEGIN IF ( UPPER(v_explanation) LIKE '%INVENTORY ITEM%') OR ((UPPER(v_explanation) LIKE '%PRODUCT%') AND ( UPPER(v_explanation) LIKE '%WAREHOUSE%')) THEN RETURN (TRUE); ELSE RETURN (FALSE); END IF; END validate_explanation; PROCEDURE reset_explanation (v_explanation IN s_inventory.out_of_stock_explanation%TYPE) IS v_valid BOOLEAN; BEGIN v_valid := validate_explanation (v_explanation); IF v_valid = TRUE THEN g_explanation := v_explanation; ELSE RAISE_APPLICATION_ERROR (-20301, 'The explanation is not worded correctly.'); END IF; END reset_explanation; BEGIN g_explanation := 'New inventory item entered by ' || USER; END explanation_package; SQL> run SQL> execute explanation_package.reset_explanation('testing'); BEGIN explanation_package.reset_explanation('testing'); END; * ERROR at line 1: ORA-20301: The explanation is not worded correctly. ORA-06512: at "BENJAMIN.EXPLANATION_PACKAGE", line 27 ORA-06512: at line 1 SQL> execute explanation_package.reset_explanation('testing inventory item'); PL/SQL procedure successfully completed. 2 CREATE OR REPLACE PROCEDURE add_inventory (v_product_id IN s_inventory.product_id%TYPE, v_warehouse_id IN s_inventory.warehouse_id%TYPE, v_reorder_point IN s_inventory.reorder_point%TYPE, v_max_in_stock IN s_inventory.max_in_stock%TYPE) IS c_amount_in_stock CONSTANT s_inventory.amount_in_stock%TYPE := NULL; v_out_of_stock_explanation s_inventory.out_of_stock_explanation%TYPE; c_restock_date CONSTANT s_inventory.restock_date%TYPE := NULL; BEGIN v_out_of_stock_explanation := explanation_package.g_explanation; INSERT INTO s_inventory (product_id, warehouse_id, amount_in_stock, reorder_point, max_in_stock, out_of_stock_explanation, restock_date) VALUES (v_product_id, v_warehouse_id, c_amount_in_stock, v_reorder_point, v_max_in_stock, v_out_of_stock_explanation, c_restock_date); COMMIT; END add_inventory; SQL> run SQL> execute add_inventory(10025,101,500,550); SQL> select product_id,out_of_stock_explanation from s_inventory;