Lab 21.1 Exercises


21.1.1 Access Files with UTL_FILE

Complete the following exercises:

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.

b)

Run the procedure LOG_USER_COUNT and then run the procedure READ_LOG for the same file.


21.1.2 Schedule Jobs with DBMS_JOB

Complete the following exercises:

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.

b)

Submit the procedure DELETE_ENROLL to execute one a month.


21.1.3 Generate an Explain Plan with DBMS_XPLAN

Complete the following exercises:

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.

b)

Compute statistics on all tables in your schema using a single SQL statement to generate the command.

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; 
d)

Use the DBMS_XPLAN package as a means to see the execution plan of the SQL statement.

e)

Generate an alternative SQL that will produce the same results and then examine the explain plan.


21.1.4 Create an HTML Page with the Oracle Web Toolkit

Complete the following exercises:

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> 
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.




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