CODEWITHSUNDEEP
sqldatabaseoracle-databaseplsqluser-defined-types
Siqueira
Siqueira

Reputation: 443

PLS-00306 on Oracle function using a UDT

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.

Upvotes: 0

Views: 424

Answers (2)

APC
APC

Reputation: 146239

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;

Upvotes: 1

XING
XING

Reputation: 9886

The mistake you are making is while storing the result. See my comments inline

retval := join_t (join_table);

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
      --Fetching records of cursor to variable var_id & var_fv  
      FETCH cur_fv_table INTO var_id, var_fv;


      SELECT join_t (r.id, r.fv) -- You made mistake here. You need to select your table columns here not any variable.
        BULK COLLECT INTO join_table --- Populating the collection
        FROM londonfv r
       WHERE manhattan_dist (var_id, var_fv) <= 5; -- Checking from the function

      --- Assuming there is only 1 record in collection join_table. 
      retval.EXTEND;
      --- Storing the value of into the collection
      retval := join_table;

      /*  If there are more then 
         for rec in 1..join_table.count
         loop
          retval.EXTEND;
          retval(rec):= join_table(rec);              
        end loop; 
       */
    END LOOP;

   RETURN retval;
END;
/

Upvotes: 1

Related Questions