PL/SQL Program to display the salaries of top 'a' employees

Question

SET SERVEROUTPUT ON;

DECLARE
    CURSOR cemp
    IS
          SELECT esal
            FROM emp
        ORDER BY esal DESC;

    a     NUMBER (10) := &a;
    sal   emp.esal%TYPE;
BEGIN
    OPEN cemp;

    LOOP
        FETCH cemp INTO sal;

        DBMS_OUTPUT.put_line (sal);
        EXIT WHEN cemp%ROWCOUNT = a OR cemp%NOTFOUND;
    END LOOP;

    CLOSE cemp;
END;

I have written the above pl/sql program to display the salaries of top 'a' employees.I have executed the code in sql developer.But Iam not getting any output.Is there something that is wrong in the code?

First check that you can use `dbms_output.put_line` at all. Just do a simple 'Hello, world' test without any loops or tables. Maybe test it in SQL\*Plus with `set serveroutput on`. Then if that works, test the query on its own. It sounds like the issue is not really about Top-N salaries but about using the tools. — William Robertson, May 22 '18 at 09:51
I have executed your code in TOAD, it does display the salaries correctly. — Jacob, May 22 '18 at 11:09
Also verify that you do not have nulls in esal column - if you do, order by esal desc nulls last; — Goran Stefanović, May 22 '18 at 12:50
Maybe: https://stackoverflow.com/q/49803808/330315 or https://stackoverflow.com/q/46612851/330315 ? — , May 22 '18 at 13:51

score 2 · Accepted Answer · answered Jun 15 '18 at 07:15

I figured out the solution myself.

Apparently there was some problem with the usage of the cursor.

The query can be simplified as :

SELECT * 
FROM 
 (
     SELECT EMPLOYEE, LAST_NAME, SALARY,
     RANK() OVER (ORDER BY SALARY DESC) emprank
     FROM emp
 )
WHERE emprank <= 3;

score 0 · Answer 2 · answered Jun 01 '18 at 10:18

0

Go to Dbms Output tab see attached image then click enable dbms output

answered Jun 01 '18 at 10:18

Ramiz Tariq

387
10
27

PL/SQL Program to display the salaries of top 'a' employees

2 Answers2