Using an associative array to trap list of errors?

Question

I am writing a procedure returning an IN OUT cursor. I want to check whether the IN parameters are appreciate. In case they are not, the cursor receive a grid of errors in the same place of results which only happen with appreciate parameters. The cursor is an IN parameter for a crystal report.

CREATE OR REPLACE PROCEDURE test_err (
                p_hire_date DATE,
                P_last_name IN VARCHAR2,
                refcur IN OUT SYS_REFCURSOR)
IS
   err_num1   PLS_INTEGER DEFAULT 0;
   err_msg1   VARCHAR2 (150);
   err_num2   PLS_INTEGER DEFAULT 0;
   err_msg2   VARCHAR2 (150);
   v_hire_date DATE;
   v_last_name VARCHAR2 (150);

BEGIN
   v_hire_date :=to_date(p_hire_date , 'dd/mm/yyyy');
   v_last_name :=UPPER(TRIM (p_last_name));
    --
   IF v_hire_date < '23/01/1990'
   THEN
      BEGIN
         err_num1 := 1;
         err_msg1 := 'Try another later hire date';
      END;
   ELSE  NULL;
   END IF;
   --
   IF v_last_name IS NULL
   THEN
      BEGIN
         err_num2 := 2;
         err_msg2 := 'Please input employee''s last name';
      END;
   ELSE v_last_name := '%'||v_last_name||'%';
   END IF;
   --
   IF (err_num1 = 0 AND err_num2 = 0)
   THEN
      GOTO main_task;
   ELSE
      GOTO err_hdle;
   END IF;

  <<main_task>>
   OPEN refcur FOR
      SELECT employee_id, last_name, hire_date
        FROM hr.employees
       WHERE hire_date >= v_hire_date
         AND UPPER(last_name) LIKE v_last_name;

   GOTO end_task;

  <<err_hdle>>
   OPEN refcur FOR
      SELECT 'Error '||err_num1 AS employee_id, err_msg1 AS last_name,
              v_hire_date AS hire_date
        FROM DUAL
       WHERE err_num1 <> 0

      UNION ALL
      SELECT 'Error '||err_num2 AS employee_id, err_msg2 AS last_name,
              v_hire_date AS hire_date
        FROM DUAL
       WHERE err_num2 <> 0;

  <<end_task>>
   NULL;
END;

However, I have to admit this code looks quite silly. I wonder there is any way using an associative array to trap errors and then fetch them into the cursor rather than extending (err_num3, err_msg3),... (err_num_n, err_msg_n) and consecutively using UNION ALL in the err_hdle block. Please help me figure out this case. Thank you!

Answer 1

Thank to @Wernfried, I have found the answer that works for my case. Instead of using an associative array, I should deploy a nested table as @Wernfried suggested. However, I must create new user-defined objects firstly. In @Wernfried's script, he defined a new type (err_msg_table_type) based on %ROWTYPE, which works in PL/SQL engine or for the body of a package only. To create a new TYPE in SQL engine, we need to clarify exactly what datatype of each inside component like the way creating a new table.

--Create table
CREATE TABLE hr.employees
(
   employee_id      NUMBER (6, 0),
   first_name       VARCHAR2 (20 BYTE),
   last_name        VARCHAR2 (25 BYTE),
   hire_date        DATE
);

--Insert records
INSERT INTO employees 
VALUES(100,'King','Steven',TO_DATE('2003/06/17', 'YYYY/MM/DD'));
INSERT INTO employees 
VALUES(101,'Kochhar','Neena',TO_DATE('2005/09/21', 'YYYY/MM/DD'));
INSERT INTO employees 
VALUES(102,'De Haan','Lex',TO_DATE('2001/01/13', 'YYYY/MM/DD'));
INSERT INTO employees 
VALUES(103,'Hunold','Alexander',TO_DATE('2006/01/03', 'YYYY/MM/DD'));
INSERT INTO employees 
VALUES(104,'Ernst','Bruce',TO_DATE('2007/05/21', 'YYYY/MM/DD'));
INSERT INTO employees 
VALUES(105,'Austin','David',TO_DATE('2005/06/25', 'YYYY/MM/DD'));
INSERT INTO employees 
VALUES(106,'Pataballa','Valli',TO_DATE('2006/02/05', 'YYYY/MM/DD'));
INSERT INTO employees 
VALUES(107,'Lorentz','Diana',TO_DATE('2007/02/07', 'YYYY/MM/DD'));
COMMIT;

--Create Objects
CREATE OR REPLACE TYPE error_set AS OBJECT (
   error_id      NUMBER (6, 0),
   error_note    VARCHAR2 (150 BYTE),
   hint_note      VARCHAR2 (150 BYTE)
   );

CREATE OR REPLACE TYPE err_msg_tab_type IS TABLE OF error_set;

--Create procedure
CREATE OR REPLACE PROCEDURE test_err (
                p_hire_date DATE,
                P_last_name IN VARCHAR2,
                refcur IN OUT SYS_REFCURSOR)

IS
   v_hire_date  DATE;
   v_last_name  VARCHAR2 (150);
   v_err_msg    err_msg_tab_type := err_msg_tab_type();

BEGIN
   v_hire_date := TO_DATE(p_hire_date, 'dd/mm/yyyy');
   v_last_name := UPPER(TRIM(p_last_name));

   IF TRIM(p_hire_date) IS NULL THEN
   v_err_msg.EXTEND;
   v_err_msg(v_err_msg.LAST):= error_set (1,
                                         'Please input the hire date',
                                         NULL); 
   END IF;

   IF v_hire_date < to_date ('13/01/2001','dd/mm/yyyy') THEN
      v_err_msg.EXTEND;
      v_err_msg(v_err_msg.LAST):= error_set (2,
                                             'Try another later hire date',
                                             'The oldest hire date was Jan 13, 2001'); 
   END IF;

   IF v_last_name IS NULL THEN
      v_err_msg.EXTEND;  
      v_err_msg(v_err_msg.LAST):= error_set (3,
                                             'Please input employee''s last name',
                                             NULL); 
   END IF;

   IF v_err_msg.COUNT = 0 THEN
      OPEN refcur FOR
      SELECT employee_id, last_name, first_name, hire_date
        FROM hr.employees
       WHERE hire_date >= v_hire_date
         AND UPPER(last_name) LIKE '%'||v_last_name||'%';
   ELSE
      OPEN refcur FOR
      SELECT error_id AS employee_id, error_note AS last_name,
             hint_note AS first_name, v_hire_date AS hire_date
        FROM TABLE(v_err_msg);
    END IF;      
END;

/*
--CLEAN UP after testing
DROP PROCEDURE test_err;
DROP TYPE err_msg_tab_type;
DROP TYPE error_set;
*/