CODEWITHSUNDEEP
oracle-databaseplsql
Siqueira
Siqueira

Reputation: 443

ORA-00932 on Oracle function with a UDT

I am trying to create a function, but I am getting the following error.

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

The code is:

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

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

How can I solve this?

It is running on Oracle 11g.

Thanks in advance!

Upvotes: 0

Views: 2355

Answers (1)

MT0
MT0

Reputation: 167972

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

Upvotes: 1

Related Questions