 |
|
Oracle Tips by Burleson |
Tracing PL/SQL Execution
Description
This assignment will require you to generate a
trace file for a PL/SQL code snippet, locate the trace file, and
e-mail the trace file to your instructor. If you have trouble, refer
to Chapter 26 where Feuerstein gives complete tracing instructions.
This exercise is deliberately ambiguous. This is to get you used to
using Google to research Oracle techniques. Here are the steps:
Procedure
Step 1 – Turn on PL/SQL debugging
Step 2 – Copy the code snippet below,
and save it as show_pubs.sql in your c:\Burleson directory. Run the
show_pubs.sql script and ensure that there are no errors. Note that
this code was the answer to your first assignment in tutorial 7. Copy
this code and ensure that it is created on your server.
set
serveroutput on size 1000000
create or
replace procedure
show_pubs
is
v_avg_sales sales.quantity%type;
diff sales.quantity%type;
cursor c1 is
select
book_title,
store_name,
order_date,
sum(quantity) sum_qty
from
book
natural join
sales
natural join
store
group by
book_title,
store_name,
order_date
having
sum(quantity) > 1000
order by
sum(quantity) desc
;
sales_rec
c1%rowtype;
BEGIN
open c1;
loop
fetch
c1 into sales_rec;
exit when c1%NOTFOUND;
if
sales_rec.sum_qty > 500
then
dbms_output.put_line(
'"'||initcap(sales_rec.book_title)||'" had an order on '||
sales_rec.order_date||' by '||
initcap(sales_rec.store_name)||' for '||sales_rec.sum_qty||'
copies.'
);
end if;
end loop;
close c1;
END;
/
show errors
Here is what your
output should look like:
@cr_show_pubs
No Errors.
Step 3 – Turn on level 1 tracing. You
may use any of the techniques described in Chapter 26.
Step 4 – Execute the script to generate
the trace file.
exec
show_pubs;
Step 5 – Locate the trace file and
e-mail it to your instructor.
ANSWER:
The student should submit a standard Oracle
trace file showing all execution steps for the show_pubs stored
procedure.
For more details, see the "Easy
Oracle Series" a set of books especially designed by Oracle
experts to get you started fast with Oracle database technology.
|
