21.1.1 Answers a) | Create a companion procedure to the example procedure LOG_USER_COUNT, name your proceudre READ_LOG. This procedure will read a text file and display each line via DBMS_OUTPUT.PUT_LINE. | A1: | Answer: The following PL/SQL will create a procedure to read a file and display the contents. Note that the exception WHEN NO_DATA_FOUND will be raised when the last line of the file has been read and there are no more lines to read. CREATE OR REPLACE PROCEDURE READ_FILE (PI_DIRECTORY IN VARCHAR2, PI_FILE_NAME IN VARCHAR2) AS V_File_handle UTL_FILE.FILE_TYPE; V_FILE_Line VARCHAR2(1024); BEGIN V_File_handle := UTL_FILE.FOPEN(PI_DIRECTORY, PI_FILE_NAME, 'R'); LOOP UTL_FILE.GET_LINE( V_File_handle , v_file_line); DBMS_OUTPUT.PUT_LINE(v_file_line); END LOOP; EXCEPTION WHEN NO_DATA_FOUND THEN UTL_FILE.FCLOSE( V_File_handle ); END; | b) | Run the procedure LOG_USER_COUNT and then run the procedure READ_LOG for the same file. | A2: | Answer: Before the procedures are executed it is important to submit the SQL*Plus command SET SERVEROUTPUT ON. SQL> EXEC LOG_USER_COUNT('C:\working', ' User .Log'); SQL> EXEC READ_LOG('C:\working', 'User.Log'); | 21.1.2 Answers a) | Create a procedure DELETE_ENROLL that will delete all student enrollments if there are no grades in the GRADE table for that student's enrollment and the start date of the section is already one month past. | A1: | Answer: CREATE or REPLACE procedure DELETE_ENROLL AS CURSOR C_NO_GRADES is SELECT st.student_id, se.section_id FROM student st, enrollment e, section se WHERE st.student_id = e.student_id AND e.section_id = se.section_id AND se.start_date_time < ADD_MONTHS(SYSDATE, -1) AND NOT EXISTS (SELECT g.student_id, g.section_id FROM grade g WHERE g.student_id = st.student_id AND g.section_id = se.section_id); BEGIN FOR R in C_NO_GRADES LOOP DELETE enrollment WHERE section_id = r.section_id AND student_id = r.student_id; END LOOP; COMMIT; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLERRM); END; | b) | Submit the procedure DELETE_ENROLL to execute one a month. | A2: | Answer: SQL> VARIABLE V_JOB NUMBER SQL> SQL> EXEC DBMS_JOB.SUBMIT(:v_job, 'DELETE_ENROLL;',SYSDATE, 'ADD_MONTHS(SYSDATE, 1)'); PL/SQL procedure successfully completed. SQL> commit; Commit complete. SQL> print v_job V_JOB ---------- 2 | 21.1.3 Answers a) | Find out if your schema has a table named PLAN_TABLE that matches the DDL in the Plan table script ch21_1a.sql. If it does not, then use the above script to create the PLAN_TABLE. | A1: | Answer: Describe PLAN_TABLE. If this does not match the values in CH21_1a.sql, run the script. | b) | Compute statistics on all tables in your schema using a single SQL statement to generate the command. | A2: | Answer: SQL> Spool compute.sql SQL> set pagesize 500 SQL> select 'Analyze table 'table_name' compute statistics;' from user_tables; SQL> Spool off SQL> @compute.sql | c) | The following SQL statement generates a list of the open sections in courses that the student with the ID of 214 is not enrolled in. There are many different SQL statements that would produce the same result. Since various in-line views are required, it is important to examine the execution plan to determine which plan will produce the result with the least COST to the database. Run the SQL as follows to generate an SQL plan. -- ch21_1b.sql EXPLAIN PLAN FOR SELECT c.course_no course_no, c.description description, b.section_no section_no, s.section_id section_id, i.first_name first_name, i.last_name last_name FROM course c, instructor i, section s, (SELECT a.course_no course_no, MIN(a.section_no) section_no FROM (SELECT count(*) enrolled, se.CAPACITY capacity, se.course_no course_no, se.section_no section_no, e.section_id section_id FROM section se, enrollment e WHERE se.section_id = e.section_id AND e.student_id <> 214 GROUP BY se.CAPACITY, se.course_no, e.section_id, se.section_no HAVING count(*) < se.CAPACITY) a GROUP BY a.course_no) b WHERE c.course_no = b.course_no AND b.course_no = s.course_no AND s.section_no = b.section_no AND s.instructor_id = i.instructor_id; | A3: | Answer: When executed properly, the SQL*Plus session will just display the word EXPLAINED. If you have another error, the PLAN_TABLE most likely is incorrect. | d) | Use the DBMS_XPLAN package as a means to see the execution plan of the SQL statement. | A4: | Answer: SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------- Id Operation Name Rows Bytes Cost (%CPU) Time ---------------------------------------------------------------------------------------- 0 SELECT STATEMENT 12 888 15 (40) 00:00:01 * 1 HASH JOIN 12 888 15 (40) 00:00:01 * 2 HASH JOIN 12 612 12 (42) 00:00:01 * 3 HASH JOIN 78 1950 6 (34) 00:00:01 4 TABLE ACCESS FULL INSTRUCTOR 10 140 3 (34) 00:00:01 5 TABLE ACCESS FULL SECTION 78 858 3 (34) 00:00:01 6 VIEW 12 312 6 (100) 00:00:01 7 SORT GROUP BY 12 192 6 (50) 00:00:01 8 VIEW 12 192 6 (100) 00:00:01 * 9 FILTER 10 SORT GROUP BY 12 192 6 (50) 00:00:01 * 11 HASH JOIN 225 3600 5 (40) 00:00:01 12 TABLE ACCESS FULL SECTION 78 780 3 (34) 00:00:01 * 13 INDEX FULL SCAN ENR_PK 225 1350 2 (50) 00:00:01 14 TABLE ACCESS FULL COURSE 30 690 3 (34) 00:00:01 ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("C"."COURSE_NO"="B"."COURSE_NO") 2 - access("B"."COURSE_NO"="S"."COURSE_NO" AND "S"."SECTION_NO"="B"."SECTION_NO") 3 - access("S"."INSTRUCTOR_ID"="I"."INSTRUCTOR_ID") 9 - filter("SE"."CAPACITY">COUNT(*)) 11 - access("SE"."SECTION_ID"="E"."SECTION_ID") 13 - filter("E"."STUDENT_ID"<>214) 31 rows selected. | e) | Generate an alternative SQL that will produce the same results and then examine the explain plan. | A5: | Answer: Note that in some cases the explain plan is not what you expect to see; this may be because the SQL was adjusted by having a QUERY RE-WRITE setting turned on. The resulting explain plan is for the SQL that the database re-wrote, which is why table alias names may be unfamiliar. Also note that if you have unnamed views inside the SQL, they will be given system names , and that is what will be referred to in the explain plan. 1 EXPLAIN PLAN FOR 2 SELECT s.course_no, description, s.section_no, s.section_id, i.first_name, i.last_name 3 FROM section s, course c, instructor i 4 WHERE c.course_no = s.course_no 5 AND s.instructor_id = i.instructor_id 6 AND section_id IN 7 (SELECT MIN(section_id) 8 FROM section s 9 WHERE section_id IN 10 (SELECT section_id 11 from enrollment e 12 GROUP BY section_id 13 HAVING COUNT(*) < 14 (SELECT capacity 15 FROM section 16 WHERE e.section_id = section_id)) 17 GROUP BY course_no) 18 AND s.course_no NOT IN 19 (SELECT s.course_no 20 FROM section s, enrollment e 21 WHERE s.section_id = e.section_id 22 AND student_id = 214) 23* ORDER BY s.course_no Explained. SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------- Id Operation Name Rows Bytes Cost (%CPU) Time ------------------------------------------------------------------------------------------------------- 0 SELECT STATEMENT 1 61 15 (40) 00:00:01 1 SORT ORDER BY 1 61 12 (42) 00:00:01 * 2 FILTER 3 NESTED LOOPS 1 61 11 (37) 00:00:01 4 NESTED LOOPS 1 38 10 (40) 00:00:01 * 5 HASH JOIN SEMI 1 24 9 (45) 00:00:01 6 TABLE ACCESS FULL SECTION 4 44 3 (34) 00:00:01 7 VIEW VW_NSO_2 4 52 6 (100) 00:00:01 8 SORT GROUP BY 4 36 6 (50) 00:00:01 * 9 HASH JOIN 4 36 5 (40) 00:00:01 10 VIEW VW_NSO_1 4 12 2 (100) 00:00:01 * 11 FILTER 12 SORT GROUP BY 4 12 2 (50) 00:00:01 13 INDEX FULL SCAN ENR_SECT_FK_I 226 678 2 (50) 00:00:01 14 TABLE ACCESS BY INDEX ROWID SECTION 1 5 2 (50) 00:00:01 * 15 INDEX UNIQUE SCAN SECT_PK 1 1 (100) 00:00:01 16 TABLE ACCESS FULL SECTION 78 468 3 (34) 00:00:01 17 TABLE ACCESS BY INDEX ROWID INSTRUCTOR 10 140 2 (50) 00:00:01 * 18 INDEX UNIQUE SCAN INST_PK 1 1 (100) 00:00:01 19 TABLE ACCESS BY INDEX ROWID COURSE 30 690 2 (50) 00:00:01 * 20 INDEX UNIQUE SCAN CRSE_PK 1 1 (100) 00:00:01 21 NESTED LOOPS 1 12 3 (34) 00:00:01 * 22 INDEX RANGE SCAN ENR_PK 1 6 2 (50) 00:00:01 * 23 TABLE ACCESS BY INDEX ROWID SECTION 1 6 2 (50) 00:00:01 * 24 INDEX UNIQUE SCAN SECT_PK 1 1 (100) 00:00:01 ------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "ENROLLMENT" "E","SECTION" "S" WHERE "S"."SECTION_ID"="E"."SECTION_ID" AND LNNVL("S"."COURSE_NO"<>:B1) AND "STUDENT_ID"=214)) 5 - access("SECTION_ID"="$nso_col_1") 9 - access("SECTION_ID"="$nso_col_1") 11 - filter(COUNT(*)< (SELECT "CAPACITY" FROM "SECTION" "SECTION" WHERE "SECTION_ID"=:B1)) 15 - access("SECTION_ID"=:B1) 18 - access("S"."INSTRUCTOR_ID"="I"."INSTRUCTOR_ID") 20 - access("C"."COURSE_NO"="S"."COURSE_NO") 22 - access("STUDENT_ID"=214) 23 - filter(LNNVL("S"."COURSE_NO"<>:B1)) 24 - access("S"."SECTION_ID"="E"."SECTION_ID") 45 rows selected. - another alternative SQL would be 1 EXPLAIN PLAN FOR 2 SELECT * FROM 3 ( 4 SELECT s.course_no course, 5 description, 6 e.section_id sec_id, 7 section_no, 8 i.first_name ' ' i.last_name i_full_name, 9 Rank() over (PARTITION BY s.course_no 10 order by count(e.student_id) ASC, 11 min(section_no) ASC) as RANK_WINTHIN_SEC 12 FROM section s, enrollment e, course c, instructor i 13 WHERE s.section_id = e.section_id and 14 s.instructor_id = i.instructor_id and 15 c.course_no = s.course_no and 16 s.course_no not in (SELECT ss.course_no 17 FROM section ss, enrollment ee 18 WHERE ss.section_id = ee.section_id and 19 ee.student_id = 214) 20 GROUP BY s.course_no, 21 description, 22 e.section_id, 23 section_no, 24 i.first_name ' ' i.last_name 25 ) 26* WHERE RANK_WITHIN_SEC = 1 Explained. SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------- Id Operation Name Rows Bytes Cost (%CPU) Time -------------------------------------------------------------------------------------------------- 0 SELECT STATEMENT 14 1484 32 (38) 00:00:01 * 1 VIEW 14 1484 32 (100) 00:00:01 * 2 WINDOW SORT PUSHED RANK 14 714 11 (46) 00:00:01 3 SORT GROUP BY 14 714 11 (46) 00:00:01 * 4 FILTER * 5 HASH JOIN 14 714 9 (34) 00:00:01 6 NESTED LOOPS 14 392 6 (34) 00:00:01 * 7 HASH JOIN 4 100 6 (34) 00:00:01 8 TABLE ACCESS FULL SECTION 4 44 3 (34) 00:00:01 9 TABLE ACCESS FULL INSTRUCTOR 10 140 3 (34) 00:00:01 * 10 INDEX RANGE SCAN ENR_SECT_FK_I 226 678 1 (100) 00:00:01 11 TABLE ACCESS FULL COURSE 30 690 3 (34) 00:00:01 12 NESTED LOOPS 1 12 3 (34) 00:00:01 * 13 INDEX RANGE SCAN ENR_PK 1 6 2 (50) 00:00:01 * 14 TABLE ACCESS BY INDEX ROWID SECTION 1 6 2 (50) 00:00:01 * 15 INDEX UNIQUE SCAN SECT_PK 1 1 (100) 00:00:01 -------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("RANK_WITHIN_SEC"=1) 2 - filter(RANK() OVER ( PARTITION BY "S"."COURSE_NO" ORDER BY COUNT(*),MIN("SECTION_NO"))<=1) 4 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "ENROLLMENT" "EE","SECTION" "SS" WHERE "SS"."SECTION_ID"="EE"."SECTION_ID" AND LNNVL("SS"."COURSE_NO"<>:B1) AND "EE"."STUDENT_ID"=214)) 5 - access("C"."COURSE_NO"="S"."COURSE_NO") 7 - access("S"."INSTRUCTOR_ID"="I"."INSTRUCTOR_ID") 10 - access("S"."SECTION_ID"="E"."SECTION_ID") 13 - access("EE"."STUDENT_ID"=214) 14 - filter(LNNVL("SS"."COURSE_NO"<>:B1)) 15 - access("SS"."SECTION_ID"="EE"."SECTION_ID") 37 rows selected. | 21.1.4 Answers a) | Create a PL/SQL procedure that will generate the following HTML page. <HTML> <HEAD> <TITLE>Section Location Update Form</TITLE> </HEAD> <BODY> <H1>Change Section Location</H1> <FORM ACTION="update_section" METHOD="GET"> Section ID: <INPUT TYPE="text" NAME="p_section" SIZE="8" MAXLENGTH="8" VALUE="150"> Course No: <INPUT TYPE="text" NAME="" SIZE="8" VALUE="120"> <SELECT NAME="p_location" SIZE="10"> <OPTION VALUE=H310>H310 <OPTION VALUE=L206>L206 <OPTION SELECTED VALUE=L210>L210 <OPTION VALUE=L211>L211 <OPTION VALUE=L214>L214 <OPTION VALUE=L500>L500 <OPTION VALUE=L507>L507 <OPTION VALUE=L509>L509 <OPTION VALUE=L511>L511 <OPTION VALUE=M200>M200 <OPTION VALUE=M311>M311 <OPTION VALUE=M500>M500 </SELECT> <INPUT TYPE="submit" VALUE="Change the location"> </FORM> </BODY> </HTML> | A1: | Answer: CREATE OR REPLACE PROCEDURE instructor_list IS v_string VARCHAR2(100); CREATE OR REPLACE PROCEDURE section_form IS v_string VARCHAR2(100); v_section_id SECTION.section_id%TYPE; v_location SECTION.location%TYPE; -- Use %TYPE whenever possible. v_course_no SECTION.course_no%TYPE; cursor c_location is select distinct location from section order by location; BEGIN SELECT section_id, location, course_no INTO v_section_id, v_location, v_course_no FROM section WHERE section_id=150; htp.htmlOpen; htp.headOpen; htp.title('Section Location Update Form'); htp.headClose; htp.bodyOpen; htp.header(1,'Change Section Location'); htp.FormOpen('update_section', 'GET'); htp.p('Section ID:'); htp.formText('p_section', 8, 8,v_section_id); htp.p('Course No: '); htp.formText( cname=>null, csize=>8,cvalue=> v_course_no); htp.FormSelectOpen(cname=>'p_location', nsize=>10); FOR rec in c_location LOOP IF rec.location = v_location THEN htp.FormSelectOption(rec.location,'SELECTED', cattributes=>'VALUE='rec.location); ELSE htp.FormSelectOption(rec.location, cattributes=>'VALUE='rec.location); END IF; END LOOP; htp.FormSelectClose; htp.FormSubmit(cvalue=>'Change the location'); htp.FormClose; htp.bodyClose; htp.htmlClose; EXCEPTION WHEN OTHERS THEN HTP.P('An error occurred: 'SQLERRM'. Please try again later.'); END; | b) | Generate an update page for the form action in the last HTML page. This update will be the form handler for the SUBMIT button, it will commit the changes to the database and then refresh the page. | A2: | Answer: CREATE OR REPLACE PROCEDURE update_section (p_section IN SECTION.section_id%TYPE, p_location IN SECTION.location%TYPE) IS BEGIN UPDATE section SET location = p_location WHERE section_id = p_section; COMMIT; section_form; htp.p('The section 'p_section' is moved to 'p_location'.'); EXCEPTION WHEN OTHERS THEN HTP.P('An error occurred: 'SQLERRM'. Please try again later.'); END; | |