Nrthugu

Is there a way to use an object as an input parameter of a UDF ? As I am trying bellow:

CREATE OR REPLACE TYPE fv_group as object(

fv NUMBER,

group_number INTEGER

);

/



CREATE OR REPLACE TYPE fv_group_array IS VARRAY(100) OF fv_group;





CREATE OR REPLACE PROCEDURE insert_groupby(FV NUMBER, fv_and_group IN OUT fv_group_array) IS

    g fv_group;

BEGIN

    IF fv < 15 THEN

        g := fv_group(fv,1);

    ELSE

        g := fv_group(fv,2);

    END IF;



    fv_and_group.extend(1);

    fv_and_group(fv_and_group.last) := g;

END;

/



declare

    obj fv_group_array := fv_group_array();

begin

    select insert_groupby(c.fv,obj)

    from   cophir c;

end; 

/

When I execute the code above I get the error
PL/SQL: ORA-00904.

PS .. Table cophir:

SQL> desc cophir

 Nome                          Tipo



 FV                         NUMBER(38)



 ID                         NUMBER(38)

To answer your question: yes, you can pass objects to a UDF. But that's not your problem.

In the anonymous block at the end of your code you've got the statement

select insert_groupby(c.fv,obj)

  from   cophir c;

However, insert_groupby is a procedure. Procedures cannot be called from SQL statements because they don't return a value. You might be able to change insert_groupby into a function, but you'll need to figure out what it should return.

Another option is to use a loop:

declare

  obj fv_group_array := fv_group_array();

begin

  FOR aRow IN (SELECT fv FROM cophir)

  LOOP

    insert_groupby(aRow.fv, obj);

  END LOOP;

end; 

Best of luck.