CODEWITHSUNDEEP
sqloracle-databasestored-proceduresplsql
Kavitha P
Kavitha P

Reputation: 13

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

Upvotes: 1

Views: 10226

Answers (5)

Ruchika
Ruchika

Reputation: 1

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;

Upvotes: 0

Ram Kumar Shukla_016
Ram Kumar Shukla_016

Reputation: 11

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

Upvotes: 1

allo
allo

Reputation: 1

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

Upvotes: 0

codex
codex

Reputation: 21

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

Upvotes: 2

Littlefoot
Littlefoot

Reputation: 143103

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.

Upvotes: 2

Related Questions