Database Programming with PL/SQL 5-1: Introduction to Explicit Cursors Practice Activities Try It / Solve It 1. Create a package called overload. The package should contain three procedures all called what_am_i. The first procedure should accept a VARCHAR2 as an IN parameter, the second a NUMBER, and the third a DATE. Each procedure should display a simple message to show which datatype was passed to it. For example, the first procedure could display "Here I am a Varchar2." Save your work for later. When you are done, describe the package. 2. Test the overload package by calling it and passing in a character string, a number, and then a date. You should see the different messages returned. 3. Now modify the overload package to have a fourth procedure in it, again called what_am_i, accepting two parameters: pin NUMBER and pin2 VARCHAR2. Test the new procedure to verify that it works. 4. Modify the overload package specification again to add a fifth procedure called what_am_i with the same two IN parameters as the fourth procedure. Try to recreate the specification. What happens and why? Copynight @2020, Orade andior its affilates. All rights reserved. Oracke and Java are registered traderrarks of Orade andior its affliates. Other names may be tradeniarks of their respective owners. 5. Suppose you write a packaged function that returns a BOOLEAN value of TRUE or FALSE. Does the BOOLEAN limit the possible places from which you can call the packaged function? 6. Create a package init_pkg as follows: CREATE OR REPLACE PACKAGE init_pkg IS g_max_sal employees.salary\%TYPE; PROCEDURE get_emp_sal (p_emp_id IN employees.employee_id\%TYPE); END init_pkg; CREATE OR REPLACE PACKAGE BODY init_pkg IS PROCEDURE get_emp_sal (p_emp_id IN employees.employee_id\%TYPE) IS v_salary employees.salary\%TYPE; BEGIN SELECT salary INTO v_salary FROM employees WHERE employee_id = p_emp_id; IF v_salary > (g_max_sal / 2 ) THEN DBMS_OUTPUT.PUT_LINE('This employee earns MORE than half of ' II g_max_sal); ELSE DBMS_OUTPUT.PUT_LINE('This employee earns LESS than half of ' II g_max_sal); END IF; END get_emp_sal; BEGIN SELECT MAX(salary) INTO g_max_sal FROM employees; END init_pkg; Now execute the following anonymous block. Explain the output. BEGIN init_pkg.get_emp_sal(101); - - line 1 init_pkg.g_max_sal := 80000; - line 2 init_pkg.get_emp_sal(101); - line 3 DBMS_OUTPUT.PUT_LINE('g_max_sal is ' init_pkg.g_max_sal); line 4 END; Without doing anything else, execute the following anonymous block. Explain the results. BEGIN DBMS_OUTPUT.PUT_LINE('g_max_sal is ' init_pkg.g_max_sal); END; Copyright $2020, Orache and/orits affiliates. All rights reserved. Oncie and lava are registered trademarks of Oracle and/ar its affleates. Other names may be trademarks of their respective owners. Create a bodiless package called time_conversion_cons that contains constants that can be used to convert: - days into hours, minutes, and seconds, - hours into days, minutes, and seconds, - minutes into days, hours, and seconds, and - seconds into days, hours, and minutes. Create an anonymous block that uses the package constants to display the following conversions. Your output should be four lines formatted as, " x days is x hours or x minutes or x seconds," using the appropriate units on each line. Use good coding practices
