Oracle pl/sql - Use associative array with user created record

Question

I'm trying to use an associative array with the element type of a user defined record. This array is to print the first name, last name, and grade of a student.

SET SERVEROUTPUT ON

DECLARE
TYPE studentRec IS RECORD (
  STUDENT_ID studentdb.student.student_id%TYPE,
  FIRST_NAME STUDENTDB.STUDENT.FIRST_NAME%TYPE,
  LAST_NAME STUDENTDB.STUDENT.LAST_NAME%TYPE,
  GRADE STUDENTDB.GRADE.NUMERIC_GRADE%TYPE
);

CURSOR studentCursor IS
  SELECT STUDENT.STUDENT_ID, STUDENT.FIRST_NAME, STUDENT.LAST_NAME, AVG(GRADE.NUMERIC_GRADE) AS GRADE
  FROM STUDENTDB.STUDENT
  INNER JOIN STUDENTDB.GRADE
  ON STUDENTDB.STUDENT.STUDENT_ID = STUDENTDB.GRADE.STUDENT_ID
  GROUP BY STUDENT.STUDENT_ID, STUDENT.FIRST_NAME, STUDENT.LAST_NAME ;

sr studentRec;

TYPE studentArray IS TABLE OF studentRec INDEX BY PLS_INTEGER;

vars studentArray;

BEGIN


FOR rec IN studentCursor LOOP

  vars(rec.STUDENT_ID) := rec.FIRST_NAME || ' ' || rec.LAST_NAME || ' has grade ' || rec.GRADE;

END LOOP;

FOR ind IN vars.FIRST .. vars.LAST LOOP

DBMS_OUTPUT.PUT_LINE(vars(ind));

END LOOP;



END;

This throws:

Error report - ORA-06550: line 27, column 27: PLS-00382: expression is of wrong type ORA-06550: line 27, column 3: PL/SQL: Statement ignored ORA-06550: line 33, column 1: PLS-00306: wrong number or types of arguments in call to 'PUT_LINE' ORA-06550: line 33, column 1: PL/SQL: Statement ignored 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. *Action:

Kaushik Nayak Kaushik Nayak · Accepted Answer · 2017-12-13T16:54:14

You don't need a RECORD declaration here, although you could use it if you know it. It is better to use CURSOR%ROWTYPE. syntax as shown. There is also no need of associative array, as your indexes are going to be numbers anyway.

Also, instead of looping through the CURSOR, you could use BULK COLLECT INTO.

You cannot directly pass vars(ind) to .PUT_LINE() . It should refer to specific column names.

SET serveroutput ON
DECLARE
    CURSOR studentcursor IS
      SELECT student.student_id,
             student.first_name,
             student.last_name,
             AVG(grade.numeric_grade) AS GRADE
      FROM   studentdb.student
             inner join studentdb.grade
                     ON studentdb.student.student_id =
                        studentdb.grade.student_id
      GROUP  BY student.student_id,
                student.first_name,
                student.last_name;
    TYPE studentarray
      IS TABLE OF studentcursor%ROWTYPE;
    vars STUDENTARRAY;
BEGIN
    OPEN studentcursor;

    FETCH studentcursor BULK COLLECT INTO vars;

    FOR ind IN vars.first .. vars.last LOOP
        dbms_output.put_line(vars(ind).student_id
                             ||','
                             || vars(ind).first_name
                             ||','
                             ||vars(ind).last_name
                             ||','
                             || vars(ind).grade);
    END LOOP;
END;

Oracle pl/sql - Use associative array with user created record

1 Answers