Project 5 is about printing a formatted report from DDILEDGE

Project 5 is about printing a formatted report from DDI.LEDGER_VIEW using Head of Forms, Form Breaks, and an End of Form. SPOOL your output to c:\\cs422a\\project5spool.txt DECLARE a record variable, a variable to keep track of a change in RoomNum, and two accumulators Add procedures to the DECLARE block to print a Head of Form, subtotals, and a grand total In the BEGIN block add a select statement to read all the ledger records in DDI.LEDGER_VIEW ordered by RoomNum and RegDate Add a LOOP Use DBMS__OUTPUT.PUT statements to print all the values for each record on a single line. End with a DBMS_OUTPUT.NEW_LINE After the LOOP be sure to print the final subtotal and the grand tota Compile and run the procedure I created my code; but something funny is happening in the output.

My code:

SQL> DECLARE
2 Room_rec DDI.LEDGER_VIEW%ROWTYPE;
3 Last_room DDI.LEDGER_VIEW.ROOMNUM%TYPE := 0;
4 Sub_Total number := 0;
5 Grand_Total number := 0;
6
7 PROCEDURE HEAD_OF_FORM
8 IS
9 BEGIN
10 DBMS_OUTPUT.NEW_LINE;
11 DBMS_OUTPUT.PUT_LINE(\' Room Rates from DDI.LEDGER_VIEW by Room Number \');
12 DBMS_OUTPUT.PUT_LINE(\'RmNum ID RegDate Last First Adults Children Size Beds Rate\');
13 DBMS_OUTPUT.PUT_LINE(\'---- --- --------- ------------ ------------ -- -- -------- -- ----\');
14 END;
15
16 PROCEDURE FORM_BREAK(v_subtot IN OUT NUMBER)
17 IS
18 BEGIN
19 DBMS_OUTPUT.PUT_LINE(\' --------\');
20 DBMS_OUTPUT.PUT_LINE(\'Subtotal \' ||
21 LPAD(TO_CHAR(Sub_Total, \'$99,999\'),8));
22 DBMS_OUTPUT.NEW_LINE;
23 v_subtot := 0;
24 END;
25
26 PROCEDURE END_OF_FORM (v_gtot IN NUMBER)
27 IS
28 BEGIN
29 DBMS_OUTPUT.PUT_LINE(\' --------\');
30 DBMS_OUTPUT.PUT_LINE(\'Grand Total \' ||
31 LPAD(TO_CHAR(v_gtot, \'$99,999\'),8));
32 END;
33
34 BEGIN
35 FOR Room_rec IN
36 (SELECT *
37 FROM DDI.LEDGER_VIEW
38 ORDER BY ROOMNUM, REGDATE)
39 LOOP
40 IF (Last_room = 0) THEN
41 HEAD_OF_FORM;
42 DBMS_OUTPUT.PUT(LPAD(TO_CHAR(Room_rec.ROOMNUM,\'999\'), 3));
43 Last_room := Room_rec.ROOMNUM;
44 Sub_Total := Sub_Total + Room_rec.ROOMRATE;
45 Grand_Total := Grand_Total + Room_rec.ROOMRATE;
46 ELSIF (Room_rec.Roomnum <> Last_room) THEN
47 FORM_BREAK(Sub_Total);
48 HEAD_OF_FORM;
49 DBMS_OUTPUT.PUT(LPAD(TO_CHAR(Room_rec.ROOMNUM,\'999\'), 3));
50 Last_room := Room_rec.ROOMNUM;
51 Sub_Total := Sub_Total + Room_rec.ROOMRATE;
52 Grand_Total := Grand_Total + Room_rec.ROOMRATE;
53 ELSE
54 DBMS_OUTPUT.PUT(\' \');
55 Sub_Total := Sub_Total + Room_rec.ROOMRATE;
56 Grand_Total := Grand_Total + Room_rec.ROOMRATE;
57 END IF;
58 DBMS_OUTPUT.PUT(\' \');
59 DBMS_OUTPUT.PUT(LPAD(TO_CHAR(Room_rec.ROOMNUM, \'999\'), 3));
60 DBMS_OUTPUT.PUT(\' \');
61 DBMS_OUTPUT.PUT(LPAD(TO_CHAR(Room_rec.REGID, \'999\'), 3));
62 DBMS_OUTPUT.PUT(\' \');
63 DBMS_OUTPUT.PUT(TO_CHAR(Room_rec.REGDATE, \'MM/DD/YY\'));
64 DBMS_OUTPUT.PUT(\' \');
65 DBMS_OUTPUT.PUT(RPAD(Room_rec.LASTNAME,12));
66 DBMS_OUTPUT.PUT(\' \');
67 DBMS_OUTPUT.PUT(RPAD(Room_rec.FIRSTNAME,12));
68 DBMS_OUTPUT.PUT(\' \');
69 DBMS_OUTPUT.PUT(LPAD(TO_CHAR(Room_rec.ADULTCNT, \'99\'), 3));
70 DBMS_OUTPUT.PUT(\' \');
71 DBMS_OUTPUT.PUT(LPAD(TO_CHAR(Room_rec.CHILDCNT, \'99\'), 3));
72 DBMS_OUTPUT.PUT(\' \');
73 DBMS_OUTPUT.PUT(RPAD(Room_rec.ROOMSIZE,8));
74 DBMS_OUTPUT.PUT(\' \');
75 DBMS_OUTPUT.PUT(LPAD(TO_CHAR(Room_rec.ROOMBEDCNT, \'99\'), 3));
76 DBMS_OUTPUT.PUT(\' \');
77 DBMS_OUTPUT.PUT(LPAD(TO_CHAR(Room_rec.ROOMRATE, \'$9,999\'),6));
78 DBMS_OUTPUT.NEW_LINE;
79 END LOOP;
80 FORM_BREAK(Sub_Total);
81 END_OF_FORM(Grand_Total);
82 EXCEPTION
83 WHEN NO_DATA_FOUND THEN
84 DBMS_OUTPUT.PUT_LINE(\'No data found.\');
85
86 END;
87 /

My columns are not lining up, every room number is displaying as 10, and not all my information is being displayed. Here is a snap shot of what I am getting:

Room Rates from DDI.LEDGER_VIEW by Room Number

RmNum ID RegDate Last First Adults Children Size Beds Ra
---- --- --------- ------------ ------------ -- -- -------- -- --
10 10 06/01/15 Marino Chip 2 0 King 1 $6
10 1 06/02/15 Parks Eric 2 1 King 1 $6   
10 3 06/05/15 Downs Marsha 2 0 King 1 $6   
10 3 06/06/15 Sailors Mike 1 0 King 1 $6   
10 4 06/07/15 Romez Richardo 1 0 King 1 $6   
------
Subtotal $30
  
-----
Grand Total $2,6

Solution

I dont see any issue with your PL/SQL code. However, please take care of the below points in order to resolve your issue:

Ponit 1, Please consider formatting your output using the below commands before executing the PL/SQL code:

set lines 200 pages 300

If formatting of a column is required, please do that , e.g., col Last for a10. By doing this, your output will be displayed in a better and more readable manner.

Point 2. For spooling, you need to do the fllowing:

SQL > spool project5spool.txt

SQL > DECLARE ...................

SQL > spool off;

By doing this, you will get a text file with name project5spool.txt in your O.S pwd(present working directory).