Error due to issue of object declaration. (inconsistent datatypes)

Question

I'm using a function which returns a table in the procedure. I want the procedure to display the contents of the table.

The code of the function is

CREATE OR REPLACE TYPE employee_attr AS OBJECT (
      employee_id                        NUMBER(6,0),    
      first_name                         VARCHAR2(20 BYTE) ,
      last_name                          VARCHAR2(20 BYTE) ,
      email                              VARCHAR2(25 BYTE) ,
      phone_number                       VARCHAR(20 BYTE) ,
      hire_date                          DATE ,
      job_id                             VARCHAR2(10 BYTE) ,
      department_id                      NUMBER(4,0) ,
      salary                             NUMBER(8,2) ,
      manager_id                         NUMBER(6,0) ,
      commission_pct                     NUMBER );

CREATE OR REPLACE TYPE employee_table AS TABLE OF employee_attr;
CREATE OR REPLACE FUNCTION get_employee_data (
      col_name_in                       VARCHAR2,
      col_val_in                        NUMBER)


FROM 
    (SELECT employee_id, first_name,
            last_name, email,
            phone_number, hire_date,
            job_id, department_id,
            salary, manager_id,
            commission_pct FROM EMPLOYEES2 WHERE '|| col_name_in || '  = ' || col_val_in || ' ) e ';
--DBMS_OUTPUT.PUT_LINE(lv_query);
EXECUTE IMMEDIATE lv_query BULK COLLECT INTO required_employees;
RETURN required_employees;     
END;/

I want the following procedure to display the contents of the function.

PROCEDURE display_employee_data(
    col_name_in         IN  VARCHAR2,
    col_val_in          IN  NUMBER)

AS
    lv_query            VARCHAR2(1000);

    emp_data            employee_attr;

    TYPE employee_data_cur_tp IS REF CURSOR ;

    employee_data_cur   employee_data_cur_tp;


BEGIN

lv_query := 'SELECT employee_id, first_name, last_name, email,
                    phone_number, hire_date, job_id, department_id,
                    salary, manager_id, commission_pct 
             FROM TABLE(get_employee_data(' ||''''|| col_name_in || ''''||
            ' , ' || col_val_in ||' ))';

DBMS_OUTPUT.PUT_LINE(lv_query);

OPEN employee_data_cur FOR lv_query;

LOOP
  FETCH employee_data_cur INTO emp_data;
  EXIT WHEN employee_data_cur%NOTFOUND;
  DBMS_OUTPUT.PUT_LINE(emp_data.employee_id || '   ' || emp_data.first_name);

END LOOP;

CLOSE employee_data_cur;


END display_employee_data;

When I run the procedure, as follows

BEGIN
employee.display_employee_data('EMPLOYEE_ID', 30);
END;

It raises the following error:

ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got -
ORA-06512: at "CHAITU.EMPLOYEE", line 394
ORA-06512: at line 2

The line 394 is where the loop starts, before the fetch statement.

Please help me out to find the right way of object declaration, so that the error is resolved. Thank You

Answer 1

The object has to be initialized. The modified procedure is as follows:

PROCEDURE display_employee_data(
    col_name_in         IN  VARCHAR2,
    col_val_in          IN  NUMBER)

IS      
    emp_data           employee_attr  DEFAULT employee_attr(NULL, NULL, NULL, NULL,
                                        NULL, NULL, NULL, NULL,
                                        NULL, NULL, NULL);

    lv_query            VARCHAR2(1000);


    TYPE employee_data_cur_tp IS 
                              REF CURSOR ;

    employee_data_cur   employee_data_cur_tp;


BEGIN

lv_query := 'SELECT employee_id, first_name, last_name, email,
                    phone_number, hire_date, job_id, department_id,
                    salary, manager_id, commission_pct 
             FROM TABLE(get_employee_data(' ||''''|| col_name_in || ''''||
            ' , ' || col_val_in ||' ))';

DBMS_OUTPUT.PUT_LINE(lv_query);

OPEN employee_data_cur FOR lv_query;

LOOP
  FETCH employee_data_cur INTO emp_data.employee_id, emp_data.first_name, emp_data.last_name,
                               emp_data.email, emp_data.phone_number, emp_data.hire_date, 
                               emp_data.job_id, emp_data.department_id, emp_data.salary,
                               emp_data.manager_id, emp_data.commission_pct;
  EXIT WHEN employee_data_cur%NOTFOUND;
  DBMS_OUTPUT.PUT_LINE(emp_data.employee_id || '   ' || emp_data.first_name);

END LOOP;

CLOSE employee_data_cur;


END display_employee_data;

Answer 2

You need to omit %rowtype otherwise the code looks good. Thanks    

CREATE OR REPLACE PROCEDURE display_employee_data(
        col_name_in         IN  VARCHAR2,
        col_val_in          IN  NUMBER) 

    AS 

        lv_query            VARCHAR2(1000);

        emp_data            employee_table;

        TYPE employee_data_cur_tp IS REF CURSOR;

        employee_data_cur   employee_data_cur_tp;


    BEGIN

    lv_query := 'SELECT * FROM TABLE(get_employee_data(' ||''''|| col_name_in || ''''||
                ' , ' || col_val_in ||' ))';

    DBMS_OUTPUT.PUT_LINE(lv_query);

    OPEN employee_data_cur FOR lv_query;

    LOOP
      FETCH employee_data_cur INTO emp_data;
      EXIT WHEN employee_data_cur%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(emp_data.employee_id || '   ' || emp_data.first_name);

    END LOOP;

    CLOSE employee_data_cur;


    END display_employee_data;