I am getting an error when I work this program and I don't know why.

Here are the DDI.LEDGER_VIEW codes:

CREATE TABLE DDI.ROOMS ( RoomNum NUMBER NOT NULL, RoomSize VARCHAR2(6) NOT NULL, RoomBedCnt NUMBER NOT NULL, RoomRate NUMBER(18,2) NOT NULL, PRIMARY KEY (RoomNum))

CREATE TABLE DDI.PATRONS ( PatronID NUMBER NOT NULL, FirstName VARCHAR2(16) NOT NULL, LastName VARCHAR2(16) NOT NULL, PhoneNum VARCHAR2(12) Null, eMail VARCHAR2(100) Null, PRIMARY KEY (PatronID)) TABLESPACE USERS;

CREATE TABLE DDI.REGISTRATIONS ( RegID NUMBER NOT NULL, RegDate DATE NOT NULL, PatronID NUMBER NOT NULL, AdultCnt NUMBER Null, ChildCnt NUMBER Null, RoomNum NUMBER NOT NULL, RegNote VARCHAR2(100) Null, PRIMARY KEY (RegID), FOREIGN KEY (PatronID) REFERENCES DDI.PATRONS, FOREIGN KEY (RoomNum) REFERENCES DDI.ROOMS) TABLESPACE USERS;

CREATE VIEW DDI.LEDGER_VIEW AS SELECT REG.RegID, REG.RegDate, P.LastName, P.FirstName, REG.AdultCnt, REG.ChildCnt, REG.RoomNum, RM.RoomSize, RM.RoomBedCnt, RM.RoomRate FROM DDI.ROOMS RM, DDI.PATRONS P, DDI.REGISTRATIONS REG WHERE REG.PatronID = P.PatronID AND REG.RoomNum = RM.RoomNum ORDER BY REG.RegDate, REG.RoomNum;

The example that I use to do the program

SET ECHO ON SET SERVEROUT ON DECLARE emp_rec hr.employees%rowtype; crnt_dept hr.employees.department_id%type := 0; found_rows BOOLEAN := FALSE; --variable to test for no_data_found exception in the FOR LOOP -- DECLARE AND DEFINE FUNCTION TO CALC AVG SALARY BY DEPT FUNCTION avg_salary( dept_id IN NUMBER) RETURN NUMBER AS average NUMBER; BEGIN SELECT AVG(SALARY) INTO average FROM HR.EMPLOYEES WHERE department_id = dept_id; RETURN average; EXCEPTION WHEN others THEN RETURN NULL; END; BEGIN DBMS_OUTPUT.PUT_LINE('---------------------------------------'); DBMS_OUTPUT.PUT_LINE(' Average Salary by Department '); DBMS_OUTPUT.PUT_LINE('---------------------------------------'); -- LOOP CALLS FUNCTION AVG_SALARY ON EACH CHANGE IN DEPT ID/DISPLAYS AVG SALARY FOR emp_rec IN (SELECT * FROM hr.employees ORDER BY department_id) LOOP found_rows := TRUE; --if data exist, sets variable to TRUE, so IF statement doesn't run IF crnt_dept != emp_rec.department_id THEN crnt_dept := emp_rec.department_id; DBMS_OUTPUT.PUT_LINE('Department '||crnt_dept||': '||TO_CHAR(avg_salary(crnt_dept),'$99999.99')); END IF; END LOOP; /* Used to throw exception for FOR LOOP when no rows are returned from SELECT query */ IF NOT found_rows THEN RAISE NO_DATA_FOUND; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('No data found.'); END; /

The question to the assignment to do the program

For the assignment, create an average function similar to the example, and use that function to print to screen the average # of guests per room, ordered by room number. Your output will have three columns 1) room number, 2) avg guests per night, 3) # of nights booked. Make sure you round the average column to two places right of the decimal place, and base it on total guests, not just adults. Your output should be similar to the SQL statement below. Run this SQL statement from the PL/SQL prompt:

SELECT ROOMNUM, AVG(ADULTCNT + CHILDCNT) AS "AVG GUEST PER NIGHT", COUNT(*) AS "BOOKED NIGHTS" FROM DDI.LEDGER_VIEW GROUP BY ROOMNUM ORDER BY ROOMNUM;

You output should

1) Use a function 2) Be carefully formatted, something similar to the example, but also with column headers. 3) Code should use clear variable names. If you reuse code from other projects you shouldn't keep a variable named Salary and use it for room number without changing the name. 4) Include the exception handler call for the loop. Remember previously, if the for loop doesn't find any records it will not automatically call the no_data_found exception handler. You can test it by asking for the department_id at run time and choosing one that doesn't exist. 5) Include comments, header and code

My code after the program:

SET SERVEROUT ON

DECLARE

ROOM_REC DDI.LEDGER_VIEW%ROWTYPE;

CRNT_ROOM DDI.LEDGER_VIEW.ROOMNUM%TYPE : 0 ;

NIGHT_BOOK NUMBER;

FOUND_ROWS BOOLEAN: = FALSE; --VARIABLE TO TEST FOR NO_DATA_FOUND EXCEPTION IN THE FOR LOOP

--DECLARE AND DEFINE FUNCTION TO CALC AVG GUESTS PER NIGHT BY ROOM

FUNCTION AVG_GUESTS(

ROOMNUM IN NUMBER)

AS AVERAGE NUMBER; BEGIN

SELECT AVG(ADULTCNT+CHILDCNT)

INTO AVERAGE

FROM DDI.LEDGER_VIEW

WHERE ROOMNUM = ROOMNUM;

RETURN AVERAGE;

EXCEPTION

WHEN OTHERS THEN

RETURN NULL;

END;

BEGIN

DBMS_OUTPUT.PUT_LINE ( '--------------------------------------- ');

DBMS_OUTPUT.PUT_LINE (' ROOM NUMBER AVERAGE GUEST PER NIGHT BOOKED NIGHTS ');

DBMS_OUTPUT.PUT_LINE ( '---------------------------------------' );

--LOOP CALLS FUNCTION AVG_GUESTS ON EACH CHANGE IN ROOMNUM/DISPLAYS ROOM NUMBER, AVERAGE GUEST PER NIGHT AND BOOKED NIGHTS

FOR ROOM_REC IN

(SELECT *

FROM DDI.LEDGER_VIEW

ORDER BY ROOMNUM)

LOOP

FOUND_ROWS : = TRUE; --IF DATA EXITS, SETS VARIABLE TO TRUE, SO IF STATEMENT DOESN'T RUN FOUND_ROWS BOOLEAN := FALSE; --VARIABLE TO TEST FOR NO_DATA_FOUND EXCEPTION IN THE FOR LOOP

IF CRNT_ROOM != ROOM_REC.ROOMNUM THEN

CRNT_ROOM : = ROOM_REC.ROOMNUM;

SELECT COUNT(*) INTO NIGHTS_BOOK FROM DDI.LEDGER_VIEW WHERE ROOMNUM = CRNT_ROOM;

DBMS_OUTPUT.PUT_LINE (DDI.LEDGER_VIEW || CRNT_ROOM|| ': '||TO_CHAR(AVG_GUESTS(CRNT_ROOM), 9999999')); END IF;

END LOOP;

/* USED TO THROW EXCEPTION for FOR LOOP WHEN NO ROWS ARE RETURNED FROM SELECT QUERY */

IF NOT FOUND_ROWS THEN

RAISE NO_DATA_FOUND;

END IF;

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('NO DATA FOUND.');

END;