How to get rid of compilation errors in SQL?

Question

I keep getting the Warning 'Procedure created with compilation errors'.

The code is the following.

BEGIN
CREATE OR REPLACE PACKAGE ABC IS
  TYPE names_varray IS VARRAY(10) OF VARCHAR2(30);
  TYPE salaries_varray IS VARRAY(10) OF NUMBER;
  names   names_varray;
  salaries salaries_varray;

  PROCEDURE BuildVarrays;
  PROCEDURE DisplayEmpTop10SalariesInReverse;
END ABC;
CREATE OR REPLACE PACKAGE BODY ABC IS
  CURSOR c_emp_top_salaries
  IS
    SELECT ROWNUM, ENAME, SAL
    FROM (SELECT ENAME, SAL FROM EMP ORDER BY SAL DESC)
    WHERE ROWNUM <= 10;
  PROCEDURE BuildVarrays
  IS
     v_count  NUMBER := 0;
  BEGIN
     FOR emp_rec IN c_emp_top_salaries LOOP
        v_count := v_count + 1;
        names(v_count)   := emp_rec.ENAME;e
        salaries(v_count) := emp_rec.SAL;
     END LOOP;
  END BuildVarrays;
  PROCEDURE DisplayEmpTop10SalariesInReverse
  IS
  BEGIN
     FOR i IN REVERSE 1..10 LOOP
        DBMS_OUTPUT.PUT_LINE('Employee Name: ' || names(i) || '  Salary: ' || salaries(i));
     END LOOP;
  END DisplayEmpTop10SalariesInReverse;

END ABC;
BEGIN
   ABC.BuildVarrays;
   ABC.DisplayEmpTop10SalariesInReverse;
END;

END;

I have tried to rebuild the code, but I seem to be stuck. Being new to PL/SQL, I am unable to understand how to manage user-defined exceptions in procedures

Answer 1

• Don't wrap it in BEGIN and END;
• Use / on a new line as a PL/SQL statement terminator; and
• Use BULK COLLECT rather than a cursor.

Like this:

CREATE OR REPLACE PACKAGE ABC IS
  TYPE names_varray IS VARRAY(10) OF EMP.ENAME%TYPE;
  TYPE salaries_varray IS VARRAY(10) OF EMP.SAL%TYPE;
  names   names_varray;
  salaries salaries_varray;

  PROCEDURE BuildVarrays;
  PROCEDURE DisplayEmpTop10SalariesInReverse;
END ABC;
/

CREATE OR REPLACE PACKAGE BODY ABC IS
  PROCEDURE BuildVarrays
  IS
  BEGIN
    SELECT ENAME, SAL
    BULK COLLECT INTO names, salaries
    FROM (SELECT ENAME, SAL FROM EMP ORDER BY SAL DESC)
    WHERE ROWNUM <= 10;
  END BuildVarrays;

  PROCEDURE DisplayEmpTop10SalariesInReverse
  IS
  BEGIN
     FOR i IN REVERSE 1..10 LOOP
        DBMS_OUTPUT.PUT_LINE('Employee Name: ' || names(i) || '  Salary: ' || salaries(i));
     END LOOP;
  END DisplayEmpTop10SalariesInReverse;

END ABC;
/

and then call it using:

BEGIN
  DBMS_OUTPUT.ENABLE();
  ABC.BuildVarrays;
  ABC.DisplayEmpTop10SalariesInReverse;
END;
/

Which, for the table:

CREATE TABLE emp (ename, sal) AS
SELECT 'Alice', 100 FROM DUAL UNION ALL
SELECT 'Beryl', 500 FROM DUAL UNION ALL
SELECT 'Carol', 400 FROM DUAL UNION ALL
SELECT 'Debra', 900 FROM DUAL UNION ALL
SELECT 'Emily', 700 FROM DUAL UNION ALL
SELECT 'Fiona', 800 FROM DUAL UNION ALL
SELECT 'Gemma', 200 FROM DUAL UNION ALL
SELECT 'Hanna', 800 FROM DUAL UNION ALL
SELECT 'Ivory', 400 FROM DUAL UNION ALL
SELECT 'Jesse', 600 FROM DUAL UNION ALL
SELECT 'Kiera', 500 FROM DUAL;

Outputs:

Employee Name: Gemma  Salary: 200
Employee Name: Ivory  Salary: 400
Employee Name: Carol  Salary: 400
Employee Name: Kiera  Salary: 500
Employee Name: Beryl  Salary: 500
Employee Name: Jesse  Salary: 600
Employee Name: Emily  Salary: 700
Employee Name: Hanna  Salary: 800
Employee Name: Fiona  Salary: 800
Employee Name: Debra  Salary: 900

fiddle