Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
CREATE OR REPLACE PROCEDURE pro_department_report

AS
v_did   department.deptid%TYPE;
v_dname department.dname%TYPE;
v_sname student.sname%TYPE;
v_count NUMBER(2);
v_sdid  student.deptid%TYPE;

CURSOR dept_name IS
SELECT deptid,dname
FROM department
ORDER BY dname;

CURSOR stud_name IS
SELECT dname,sname
FROM student JOIN department
USING (deptid)
ORDER BY 1 ASC, 2 ASC;

CURSOR stud_count IS
SELECT deptid,count(sname) as total
FROM student
GROUP BY deptid;

BEGIN

OPEN dept_name;

LOOP
FETCH dept_name INTO v_did,v_dname;
EXIT WHEN dept_name%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Department: '||v_dname);
END LOOP;

CLOSE dept_name;

OPEN stud_count;

LOOP
FETCH stud_count INTO v_sdid,v_count;
EXIT WHEN stud_count%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Total number of students: '||v_count);
FOR item IN v_count
END LOOP;

CLOSE stud_count;

OPEN stud_name;

LOOP
FETCH stud_name INTO v_dname,v_sname;
EXIT WHEN stud_name%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_sname);
END LOOP;

CLOSE stud_name;


END;
/

My anonymous block is

BEGIN

pro_department_report;

END;
/

The procedure is run correctly but once I try to run it from block. its shows error.

The output should be like this.

Department: Education Studies
Total number of students: 0
-----------------------------
Department: Information Technology
Total number of students: 3
1. student name
2. student name
3. student name
------------------------------

this result will go on until all 5 departments is listed.

share|improve this question
1  
ORA-06550 means there is a compilation error. Type show error procedure pro_department_report to get the exact error. –  John Doyle Dec 14 '13 at 14:39
    
You have a syntax error in pro_department_report procedure. The PL/SQL compiler error message tells you the exact place. –  user272735 Dec 14 '13 at 14:42

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.