Lab 14.1 Exercise Answers

14.1.1 Answers

a)	Type the preceding code into a text file. Then run the script in a SQL*Plus session. Explain what happened .
A1:	Answer: The specification for the package manage_students has been compiled into the database. The specification for the package now indicates that there is one procedure and one function. The procedure find_sname requires one IN parameter, which is the student ID, and it returns two OUT parameters, one being the student's first name and the other being the student's last name . The function id_is_good takes in a single parameter of a student ID and returns a Boolean (true or false). Although the body has not yet been entered into the database, the package is still available for other applications. For example, if you included a call to one of these procedures in another stored procedure, that procedure would compile (but would not execute).
b)	If the following script was run from a SQL*PLUS session, what would the result be and why? -- ch14_2a.sql SET SERVEROUTPUT ON DECLARE v_first_name student.first_name%TYPE; v_last_name student.last_name%TYPE; BEGIN manage_students.find_sname (125, v_first_name, v_last_name); DBMS_OUTPUT.PUT_LINE(v_first_name' 'v_last_name); END;
A2:	Answer: The procedure cannot run because only the specification for the procedure exists in the database, not the body. The SQL*Plus session returns the following: ERROR at line 1: ORA-04068: existing state of packages has been discarded ORA-04067: not executed, package body "STUDENT.MANAGE_STUDENTS" does not exist ORA-06508: PL/SQL: could not find program unit being called ORA-06512: at line 5
c)	Create a package specification for a package named student_ta_api. The package contains the procedure discount from Chapter 12 and the function new_instructor_id from Chapter 13.
A3:	Answer: 1 CREATE OR REPLACE PACKAGE school_api as 2 PROCEDURE discount_cost; 3 FUNCTION new_instructor_id 4 RETURN instructor.instructor_id%TYPE; 5 END school_api;

14.1.2 Answers

a)	Type the preceding code into a text file. Then run the script in a SQL*Plus session. Explain what happens.
A1:	Answer: The package body manage_students is compiled into the database. The package contains the procedure manage_students.find_sname, which accepts the parameter student_id and returns the student's last_name and first_name from the Student table.
b)	Create a package body for the package named cta_api that you just created.
A2:	Answer: -- ch14_5a.sql 1 CREATE OR REPLACE PACKAGE BODY school_api AS 2 PROCEDURE discount_cost 3 IS 4 CURSOR c_group_discount 5 IS 6 SELECT distinct s.course_no, c.description 7 FROM section s, enrollment e, course c 8 WHERE s.section_id = e.section_id 9 GROUP BY s.course_no, c.description, 10 e.section_id, s.section_id 11 HAVING COUNT(*) >=8; 12 BEGIN 14 FOR r_group_discount IN c_group_discount 14 LOOP 15 UPDATE course 16 SET cost = cost * .95 17 WHERE course_no = r_group_discount.course_no; 18 DBMS_OUTPUT.PUT_LINE 19 ('A 5% discount has been given to' 20 r_group_discount.course_no' 21 'r_group_discount.description); 22 END LOOP; 23 END discount_cost; 24 FUNCTION new_instructor_id 25 RETURN instructor.instructor_id%TYPE 26 IS 27 v_new_instid instructor.instructor_id%TYPE; 28 BEGIN 29 SELECT INSTRUCTOR_ID_SEQ.NEXTVAL 30 INTO v_new_instid 31 FROM dual; 32 RETURN v_new_instid; 33 EXCEPTION 34 WHEN OTHERS 35 THEN 36 DECLARE 37 v_sqlerrm VARCHAR2(250) := SUBSTR(SQLERRM,1,250); 38 BEGIN 39 RAISE_APPLICATION_ERROR(-20003, 40 'Error in instructor_id: 'v_sqlerrm); 41 END; 42 END new_instructor_id; 43 END school_api;

14.1.3 Answers

a)	The previous example displays how a procedure within a package is executed. What results do you expect if you run this PL/SQL block?
A1:	Answer: This is a correct PL/SQL block for running the function and the procedure in the package manage_students . If an existing student_id is entered, then the name of the student is displayed. If the id is not valid, then the error message is displayed.
b)	Run the script and see the results. How does this compare with what you expected? Explain what the script is accomplishing line by line.
A2:	Answer: Initially the following appears: Enter value for v_id: If you enter "145," then you see: old 5: IF manage_students.id_is_good(&v_id) new 5: IF manage_students.id_is_good(145) old 7: manage_students.find_sname(&&v_id, v_first_name, new 7: manage_students.find_sname(145, v_first_name, old 9: DBMS_OUTPUT.PUT_LINE('Student No. '&&v_id ' is ' new 9: DBMS_OUTPUT.PUT_LINE('Student No. '145' is ' old 14: ('Student ID: '&&v_id' is not in the database.'); new 14: ('Student ID: '145' is not in the database.'); Student No. 145 is Lefkowitz, Paul PL/SQL procedure successfully completed.

The function id_is_good returns TRUE for an existing student_id such as 145. The control then flows to the first part of the IF statement and the procedure manage_students.find_sname finds the first and last name for student_id 145, which happens to be Lefkowitz, Paul.

c)	Create a script testing the cta_api package.
A3:	Answer: SET SERVEROUTPUT ON DECLARE V_instructor_id instructor.instructor_id%TYPE; BEGIN cta_api.Discount; v_instructor_id := cta_api.new_instructor_id; DBMS_OUTPUT.PUT_LINE ('The new id is: 'v_instructor_id); END;

14.1.4 Answers

a)	Replace the last lines of the manage_students package specification with the following and recompile the package specification: 11 PROCEDURE display_student_count; 12 END manage_students; Replace the end of the body with the following and recompile the package body: 37 FUNCTION student_count_priv 38 RETURN NUMBER 39 IS 40 v_count NUMBER; 41 BEGIN 42 select count(*) 43 into v_count 44 from student; 45 return v_count; 46 EXCEPTION 47 WHEN OTHERS 48 THEN 49 return(0); 50 END student_count_priv; 51 PROCEDURE display_student_count 52 is 53 v_count NUMBER; 54 BEGIN 55 v_count := student_count_priv; 56 DBMS_OUTPUT.PUT_LINE 57 ('There are 'v_count' students.'); 58 END display_student_count; 59 END manage_students; What have you added to the manage_student package?
A1:	Answer: A private function, student_count_privs, and a public procedure, display_student_count, calling the private function.
b)	If you run the following from your SQL*PLUS session, what are the results? DECLARE V_count NUMBER; BEGIN V_count := Manage_students.student_count_priv; DBMS_OUTPUT.PUT_LINE(v_count); END;
A2:	Answer: Since the private function, student_count_privs, cannot be called from outside the package, you receive an error message as follows : ERROR at line 1: ORA-06550: line 4, column 31: PLS-00302: component 'STUDENT_COUNT_PRIV' must be declared ORA-06550: line 4, column 3: PL/SQL: Statement ignored

It appears as if the private function does not exist. This is important to keep in mind. You can see this can be useful when you are writing PL/SQL packages used by other developers. In order to simplify the package for them, they only need to see the package specification. This way they know what is being passed into the procedures and functions and what is being returned. They do not need to see the inner workings. If a number of procedures make use of the same logic, it may make more sense to put them into a private function called by the procedures.

c)	If you were to run the following, what do you expect to see? SET SERVEROUTPUT ON Execute manage_students.display_student_count;
A3:	Answer: This is a valid method of running a procedure. A line is displayed indicating the number of students in the database. Note that the procedure in the package manage_ students is using the private function student_count_priv to retrieve the student count. Note that if you forget to include a procedure or function in a package specification, it becomes private. On the other hand, if you declare a procedure or function in the package specification, and then you do not define it when you create the body, you receive the following error message: PLS-00323: subprogram or cursor 'procedure_name' is declared in a package specification and must be defined in the package body
d)	Add a private function to the school_api called get_course_descript_private. It accepts a course.course_no%TYPE and returns a course.description%TYPE. It searches for and returns the course description for the course number passed to it. If the course does not exist or if an error occurs, it returns a NULL.
A4:	Answer: Add the following lines to the package body: There is nothing that needs to be added to the package specification, since you are only adding a private object. 43 FUNCTION get_course_descript_private 44 (i_course_no course.course_no%TYPE) 45 RETURN course.description%TYPE 46 IS 47 v_course_descript course.description%TYPE; 48 BEGIN 49 SELECT description 50 INTO v_course_descript 51 FROM course 52 WHERE course_no = i_course_no; 53 RETURN v_course_descript; 54 EXCEPTION 55 WHEN OTHERS 56 THEN 57 RETURN NULL; 58 END get_course_descript_private; 59 END school_api;

14.1.5 Answers

a)	Add a package wide variable called v_current_date to cta_api; additionally, add an initialization section that assigns the current sysdate to the variable v_current_date.
A1:	Answer: Add the following line to the beginning of the package specification: 1 CREATE OR REPLACE PACKAGE school_api as 2 v_current_date DATE; 3 PROCEDURE Discount; 4 FUNCTION new_instructor_id 5 RETURN instructor.instructor_id%TYPE; 6 END school_api; Add the following to the end of the package body: 59 BEGIN 60 SELECT trunc(sysdate, 'DD') 61 INTO v_current_date 62 FROM dual; 63 END school_api;