Can someone please help me because I am getting this error SP2-0552: Bind variable "0" not declared.

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

here is the example that I use for the assignment:

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; /

here is the question that I follow:

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