Lab 21.1 Exercise Answers


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; 



Oracle PL[s]SQL by Example
Oracle PL[s]SQL by Example
ISBN: 3642256902
EAN: N/A
Year: 2003
Pages: 289

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net