PLS-00306 on Oracle function using a UDT

Question

I am getting this error:

LINE/COL ERROR
-------- -----------------------------------------------------------------
13/3     PL/SQL: Statement ignored
13/13    PLS-00306: wrong number or types of arguments in call to 'JOIN_JT'

Types used:

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

CREATE TYPE join_jt IS TABLE OF join_t;
/

Here is the PL/SQL code from the function that is returning the error. When I try to pass the results I have got in join_table to retval the type error above is triggered):

CREATE OR REPLACE FUNCTION join RETURN join_jt
AS
    CURSOR cur_fv_table IS SELECT id,fv FROM london WHERE id <= 3000;

    retval join_jt := join_jt ();
    var_fv london.fv%type;
    var_id london.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_t(r.id, var_id) BULK COLLECT INTO join_table
        FROM   london r
        WHERE  manh_dist(r.fv,var_fv) <= 5;

        retval.EXTEND;
        retval := join_t(join_table);
    END LOOP;

    RETURN join_table;            
END;
/

You can use this function for testing the function above:

CREATE OR REPLACE FUNCTION manh_dist(
    fv1 LONDON.FV%TYPE,
    fv2 LONDON.FV%TYPE
) RETURN NUMBER
AS
BEGIN
    RETURN 0;                              -- Implement this.
END;
/

Does anyone know how to solve this error?

I am using the Oracle 11g.

Answer 1

So this is your problem:

       retval := join_t (join_table);

You're trying to cast a table to an object type. Which is wrong. To populate the output table you need to union the query collection with the return collection. MULTISET UNION is what you need:

CREATE OR REPLACE FUNCTION knn_join RETURN join_jt
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_t(r.id, var_id) BULK COLLECT 
        INTO join_table FROM londonfv r WHERE manhattan_dist(r.fv,var_fv) <=5;
       retval := retval multiset union all join_table;
    END LOOP;   
    RETURN retval;          
END;
/

Note: I assume you really meant to return the aggregated collection retval rather than the last intermediate set.

---

Not having time to test this right now, I admit @Wernfried has given me some doubt as to whether this will run. If you run into problems, this blunter approach will work:

for idx in join_table.first()..join_table.last()
       loop
            Retval.extend();
           retval(retval.count()) := join_table(idx);
       end loop;