'Display department names using Cursors.Create a PL/SQL block to display all the department names from the Department table using cursors

TABLE:

Column name Data type Constraints

DEPARTMENT_ID NUMBER(5) PK
DEPARTMENT_NAME VARCHAR2(25) NOT NULL
LOCATION_ID VARCHAR2(15)

I have tried this. But still i didn’t get my output. Can somebody help me to get the output.

set serveroutput on;
DECLARE
DEPARTMENT_NAME VARCHAR2(25);
CURSOR dep_cursor IS
SELECT
DEPARTMENT_NAME
FROM
department;
BEGIN
OPEN dep_cursor;
FETCH dep_cursor INTO DEPARTMENT_NAME;
DBMS_OUTPUT.PUT_LINE(‘Department Names are :’ || DEPARTMENT_NAME);
CLOSE dep_cursor;
END;
/

Error message: Bind variable “~” not declared.

Sample output:

Department Names are :
ADMIN
DEVELOPMENT
TESTING
sqloraclestored-proceduresplsql


Solution 1:[1]

It is about those "fancy" single quotes you used here:

DBMS_OUTPUT.PUT_LINE(‘Department Names are :’ || DEPARTMENT_NAME);

should be

DBMS_OUTPUT.PUT_LINE('Department Names are :' || DEPARTMENT_NAME);

As of other "errors" you've made: without a loop, cursor returns only one row and you then display it. I'd suggest you to switch to a cursor for loop as it is simpler to maintain:

begin
  for cur_r in (select department_name from department) loop
    dbms_output.put_line(cur_r.department_name);
  end loop;
end;
/

This is everything you need; no declaration section, no opening nor closing a cursor, no worrying about exiting the loop ... Oracle does it for you.

Solution 2:[2]

SET SERVEROUTPUT ON;
declare
v_dept department.department_name%type; 
cursor c_dept is SELECT department_name FROM department;
BEGIN
  dbms_output.put_line('Department Names are :');
    OPEN c_dept;
    LOOP
        FETCH c_dept INTO v_dept;
        EXIT WHEN c_dept%notfound;
        dbms_output.put_line(v_dept);
    END LOOP;
    CLOSE c_dept;
END;
/

Solution 3:[3]

SET SERVEROUTPUT ON;
declare v_dept department.department_name%type;
cursor c_dept is SELECT department_name FROM department order by department_name asc;
BEGIN dbms_output.put_line('Department Names are :');
OPEN c_dept;
LOOP
FETCH c_dept INTO v_dept;
EXIT WHEN c_dept%notfound;
dbms_output.put_line(v_dept);
END LOOP;
CLOSE c_dept; END;

This will 100% work as question is also asking to display in Ascending order. Please up-vote if you find this useful

Solution 4:[4]

SET SERVEROUTPUT ON; declare v_dept department.department_name%type; cursor c_dept is SELECT department_name FROM department ORDER BY DEPARTMENT_NAME; BEGIN dbms_output.put_line('Department Names are :'); OPEN c_dept; LOOP FETCH c_dept INTO v_dept; EXIT WHEN c_dept%notfound; dbms_output.put_line(v_dept); END LOOP; CLOSE c_dept; END; /

Solution 5:[5]

SET SERVEROUTPUT ON;
DECLARE
v_dept department.department_name%type;
cursor c_dept is SELECT department_name FROM department order by department_name asc;
BEGIN 
    dbms_output.put_line('Department Names are :');
        OPEN c_dept;
        LOOP
            FETCH c_dept INTO v_dept;
            EXIT WHEN c_dept%notfound;
            dbms_output.put_line(v_dept);
        END LOOP;
        CLOSE c_dept; 
END;

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Littlefoot
Solution 2 codex
Solution 3 Ram Kumar Shukla_016
Solution 4
Solution 5 4b0

Related Questions