0

Create a PL/SQL block to display all the department names from the Department table using cursors. The department names should be displayed in ascending order.

Column Name 1 - DEPARTMENT_ID NUMBER(5) PRIMARY KEY
Column Name 2 - DEPARTMENT_NAME VARCHAR(25) NOT NULL
Column Name 3 - LOCATION_ID VARCHAR(15)

Code
SET SERVEROUTPUT On;
declare
counter number;
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;

Sample Output:
Department Names are :
ADMIN
DEVELOPMENT

Note: Use '/' to terminate your query before compilation and evaluation

Error:

Failed Test
 Test Case 2

 Summary of tests
+------------------------------+
| 2 tests run / 1 test passed  |
+------------------------------+
Ankit Bajpai
  • 13,128
  • 4
  • 25
  • 40
Ram Kumar Shukla_016
  • 11
  • 1
  • 4
  • PL/SQL code is commented (so it didn't execute at all). Output you posted is the SELECT statement, executed twice. – Littlefoot Feb 25 '22 at 06:53
  • It is automatically repeating therefore to know what is happening I have commented procedure block and run the simple SQL query – Ram Kumar Shukla_016 Feb 25 '22 at 07:28
  • Is there, by any chance, a forward slash (`/`) there? In SQL*Plus, it re-executes the last statement. – Littlefoot Feb 25 '22 at 07:29
  • Still same problem – Ram Kumar Shukla_016 Feb 25 '22 at 07:41
  • No idea, then. PL/SQL piece of code you posted looks OK to me. – Littlefoot Feb 25 '22 at 07:44
  • Instead of pasting a screenshot it's better to write your code with a reproducible example in your question. Screenshots aren't searchable and usable to other users to help them to look for their own answers, while written code and error messages might help others with the same problem to not repeat the same question. – Ana GH Feb 25 '22 at 09:56
  • Check now please – Ram Kumar Shukla_016 Feb 25 '22 at 10:51
  • The code works fine, although it could be formatted and simplified a lot, and your assignment says to order the results. How are you running this in PL/SQL Developer? Can you run any PL/SQL block and get output, for example a one-line 'Hello World' test? – William Robertson Feb 26 '22 at 16:01

1 Answers1

0
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;
hjwasim
  • 1
  • Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Mar 27 '22 at 15:38