ORA-00932 on Oracle function with UDT

Question

我正在尝试创建一个函数，但是我得到了以下错误。

LINE/COL ERROR
-------- -----------------------------------------------------------------
12/3     PL/SQL: SQL Statement ignored
12/18    PL/SQL: ORA-00932: inconsistent datatypes: expected UDT got NUMBER

代码是：

CREATE TYPE join_t IS OBJECT (
   inn     NUMBER(38),
   out     NUMBER(38)
);
/

CREATE TYPE join_jt IS TABLE OF join_t;
/

CREATE OR REPLACE FUNCTION knn_join RETURN number
IS
    CURSOR cur_fv_table IS SELECT id,fv FROM londonfv WHERE id <= 3000;

    retval join_jt := join_jt ();
    var_fv londonfv.fv%type;
    var_id londonfv.id%type;
    join_table join_jt := join_jt();
BEGIN
    OPEN cur_fv_table;
    LOOP
        FETCH cur_fv_table INTO var_id,var_fv;
        SELECT join_jt(r.id, var_id) BULK COLLECT INTO join_table
        FROM   londonfv r
        WHERE  manhattan_dist(r.fv,var_fv) <= 5;
    END LOOP;           
END;
/

Londonfv表>>

Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
ID                                        NOT NULL NUMBER(38)
PHOTOID                                            VARCHAR2(10)
FV                                                 BLOB

我该如何解决这个问题呢？

它在Oracle 11g上运行。

提前感谢！

Answer 1

CREATE TABLE londonfv (
 ID      NUMBER(38) NOT NULL,
 PHOTOID VARCHAR2(10),
 FV      BLOB
);

CREATE OR REPLACE TYPE join_t IS OBJECT (
   inn  NUMBER(38),
   out  NUMBER(38)
);
/

CREATE TYPE join_jt IS TABLE OF join_t;
/

CREATE OR REPLACE FUNCTION manhattan_dist(
  fv1 LONDONFV.FV%TYPE,
  fv2 LONDONFV.FV%TYPE
) RETURN NUMBER
IS
BEGIN
  RETURN 0;                              -- Implement this.
END;
/

CREATE OR REPLACE FUNCTION knn_join RETURN number
IS
  join_table join_jt;                    -- You don't need to initialise this BULK COLLECT will.
BEGIN
  SELECT join_t( b.id, a.id )            -- JOIN_T not JOIN_JT
  BULK COLLECT INTO join_table
  FROM   londonfv a
         INNER JOIN
         londonfv b
         ON ( manhattan_dist(a.fv,b.fv) <= 5 )
  WHERE  a.id <= 3000;

  RETURN 0;
END;
/