I got these error when trying to run my program SP2-0552: Bind variable "0" not declared. AS * ERROR at line 11: ORA-06550: line 11, column 1: PLS-00103: Encountered the symbol "AS" when expecting one of the following: return ORA-06550: line 24, column 2: PLS-00103: Encountered the symbol "BEGIN" when expecting one of the following: end not pragma final instantiable order overriding static member constructor map ORA-06550: line 34, column 12: PLS-00103: Encountered the symbol "BOOLEAN" when expecting one of the following: := . ( @ % ; The symbol "." was substituted for "BOOLEAN" to continue. ORA-06550: line 37, column 11: PLS-00103: Encountered the symbol ":" when expecting one of the following: := . ( @ % ; 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;

/