在编译oracle函数时面对ORA-00932

Question

我做了下面的代码。

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;

在编译函数时出现以下错误。

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 

在函数中使用以下查询时

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

面对下面的错误

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

尝试一小段面向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;

我做错什么了？

Answer 1

我希望下面的片段能有所帮助。有几件事要考虑。

不要在任何术语中使用"“

2必须在select查询中从对象类型中进行选择，如下所述。

3.使用批量收集而不是只进入..。

    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-------------------------------------------