CODEWITHSUNDEEP
sqloracle-databaseplsqlcursorrecords
David Adders
David Adders

Reputation: 138

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

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.

Upvotes: 0

Views: 2568

Answers (3)

William Robertson
William Robertson

Reputation: 16001

As an alternative, if you just want the record type to be a flat structure and match the cursor, you could place your cursor in the package spec and define a type as cursor%rowtype:

create or replace package emp_pkg
as
    cursor emp_cur
        ( cp_emp_id employees.employee_id%type )
    is
        select e.*, d.department_name
        from   employees e
               join departments d
                    on  e.department_id = d.department_id
        where  e.employee_id = cp_emp_id;

    subtype t_emp_rec is emp_cur%rowtype;

    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
    is
        v_emp_rec t_emp_rec;
    begin
        open emp_cur(p_emp_id);
        fetch emp_cur into v_emp_rec;
        close emp_cur;

        return v_emp_rec;
    end get_emp;
end emp_pkg;

Upvotes: 2

Rishabh Jain
Rishabh Jain

Reputation: 11

As Guillaume suggested, explicitly mention the column names. And when you call the function, you receive T_EMP_REC record. Now each Variable can be accesses as T_EMP_REC.employees_col1, T_EMP_REC.employees_col2, T_EMP_REC.department_col1 etc.

Could'n reply on your comment on his answer, so answered this way.

Upvotes: 1

Guillaume
Guillaume

Reputation: 81

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.

Upvotes: 1

Related Questions