ORA-00932 facing while compiling the oracle function

Question

I have made below code.

CREATE OR REPLACE  TYPE  CAL IS OBJECT(
       EMPLOYEE_NAME VARCHAR2(30), 
       R_DATE DATE,
       COMMENTS VARCHAR2(50)     
 );

CREATE OR REPLACE  TYPE  T_REC is table of cal;

create or replace function CALENDAR(v_team_name varchar2)
return t_rec
IS
v_rec t_rec;
v_COMM VARCHAR2(50);
v_name VARCHAR2(30);
BEGIN
FOR i in (Select ID,EMPLOYEE_NAME from EMPLOYEE where TEAM_NAME=v_team_name)
LOOP
v_name:=i.EMPLOYEE_NAME;
FOR k in (select EMP_ID, START_DT,END_DT-START_DT+1 DAYS,NAME,COMMENTS from EMP_ROTA where EMP_ID=i.ID)
LOOP
v_COMM:=k.NAME||', '||k.COMMENTS;
select t_rec(v_name,k.START_DT+level-1,v_COMM)
into v_rec
from dual connect by level < k.DAYS;
END LOOP;
END LOOP;
Return v_rec;
END;

Facing below error in compiling the function.

Compilation failed,line 14 (04:33:54)
PL/SQL: ORA-00932: inconsistent datatypes: expected UDT got CHARCompilation failed,line 14 (04:33:54)
PL/SQL: SQL Statement ignored 

While using below query in function

select v_name,k.START_DT + level -1,v_COMM
into v_REC
from dual connect by level < k.DAYS;

Facing below error

PL/SQL: ORA-00947: not enough valuesCompilation failed,line 14 (04:50:49)

Trying small snippet of code stil facing ORA-00932.

create or replace function ROTA_CALENDAR
return t_CAL
IS
v_CAL t_CAL;
BEGIN 
select t_cal('v_name',SYSDATE,'NAME')
into v_CAL
from dual;
Return v_CAL;
END;

what am I doing wrong?

Answer 1

I hope below snippet will help. Couple of things to take in consideration.

1 Don't use "" while any nomenclature

2 You have to select from Object type in the SELECT query as mentioned below.

3 Use BULK COLLECT instead of INTO only...

    CREATE OR REPLACE
  FUNCTION CALENDAR(
      V_TEAM_NAME VARCHAR2)
    RETURN t_rec
  IS
    v_rec t_rec;
    v_COMM VARCHAR2(50);
    v_name VARCHAR2(30);
  BEGIN
    FOR i IN
    (
      SELECT
        EMPNO,
        'AVRAJIT' ENAME
      FROM
        EMP
      WHERE
        JOB=v_team_name
    )
    LOOP
      v_name:=i.ENAME;
      FOR k IN
      (
        SELECT
          EMPNO,
          SYSDATE,
          SYSDATE+1 DAYS,
          ENAME,
          JOB
        FROM
          EMP_V1
        WHERE
          EMPNO=i.EMPNO
      )
      LOOP
        v_COMM:=k.ENAME||', '||k.JOB;
        SELECT
          CAL(V_NAME,SYSDATE+1,V_COMM) BULK COLLECT
        INTO
          v_rec
        FROM
          dual
          CONNECT BY level < 10;
      END LOOP;
    END LOOP;
    RETURN v_rec;
  END;


-------------------------------OUTPUT-------------------------------------------

SELECT OBJECT_NAME,STATUS FROM ALL_OBJECTS
WHERE OBJECT_NAME = 'CALENDAR';

OBJECT_NAME STATUS
CALENDAR    VALID

-------------------------------OUTPUT-------------------------------------------