Is it possible to fetch a cursor into a record using rowtype?

Question

I'm always getting

PLS-00597: expression 'V_EMP_REC' in the INTO list is of wrong type

Here's the package, i'm using default HR schema

CREATE OR REPLACE PACKAGE EMP_PKG AS
  TYPE T_EMP_REC IS RECORD (
    V_EMP_TAB EMPLOYEES%ROWTYPE,
    V_DEPT_NAME DEPARTMENTS.DEPARTMENT_NAME%TYPE
  );

  FUNCTION GET_EMP (P_EMP_ID EMPLOYEES.EMPLOYEE_ID%TYPE) RETURN T_EMP_REC;
END EMP_PKG;
/
CREATE OR REPLACE PACKAGE BODY EMP_PKG AS
  FUNCTION GET_EMP (P_EMP_ID EMPLOYEES.EMPLOYEE_ID%TYPE)
  RETURN T_EMP_REC
  AS
    CURSOR V_EMP_CUR IS
      SELECT E.*, D.DEPARTMENT_NAME
      FROM EMPLOYEES E, DEPARTMENTS D
      WHERE E.EMPLOYEE_ID = P_EMP_ID
      AND E.DEPARTMENT_ID = D.DEPARTMENT_ID;

  V_EMP_REC T_EMP_REC;
  BEGIN
    OPEN V_EMP_CUR;
    FETCH V_EMP_CUR INTO V_EMP_REC;
    CLOSE V_EMP_CUR;

  RETURN V_EMP_REC; 
  END GET_EMP;
END EMP_PKG;

The number of columns matches and I'm always using the same types as the table. I don't know if this approach is possible instead of declaring every column in the record type.

Answer 1

You have to explicitly declare all the columns of employees and departments in your collection:

TYPE t_emp_rec IS RECORD (employees_col1 employees.employees_col1%TYPE,
                          employees_col2 employees.employees_col2%TYPE,
                          ...
                          departments_col1 departments.departments_col1%TYPE,
                          departments_col2 departments.departments_col2%TYPE
                          ...
                          );

In your code you get an error because you'are trying to assign a column type (NUMBER, VARCHAR2, DATE, ...) to a RECORD collection.