Wsrtjtyk

I am trying to do build a sentence using a UDT, as follows:

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 TYPE fv_grouping AS OBJECT (

 fv_and_group fv_group_array,

 MEMBER PROCEDURE insert_groupby(FV NUMBER),

 MEMBER FUNCTION which_group(FV NUMBER) RETURN INTEGER

);

/



CREATE OR REPLACE TYPE BODY fv_grouping as

 MEMBER PROCEDURE insert_groupby(FV NUMBER) 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;

MEMBER FUNCTION which_group(FV NUMBER) RETURN INTEGER IS

feature NUMBER;

BEGIN

    FOR i IN 1..fv_and_group.count LOOP

        feature := fv_and_group(i).fv;

        IF  fv_and_group(i).fv = fv THEN

            RETURN fv_and_group(i).group_number;

        END IF;

    END LOOP;

    RETURN 0;

END;

END;

/

The representation I need is:

DECLARE

     obj fv_grouping;

BEGIN



 SELECT :obj.which_group(gb.fv), count(*)

 FROM (

   SELECT :obj.insert_groupby(c.fv, 6, 3)

   from  cophir

   ) gb

   GROUP BY :obj.which_group(gb.fv);



END; 

/

The procedure insert_groupby inserts each value of the cophir table into a varray, wich holds its values and the corresponding group.
After the varray is loaded with all values and their corresponding group, I want to group them. Is it possible, to do it in a query?
Thanks in advance!

grouping is a type. What is wrong?

GROUPING is an Oracle keyword. If you re-name your type to say FV_GROUPING you will solve the PLS-00488. Which will leave you free to address all the other syntax errors in your code:

1. The object type is not instantiated correctly in the query.


2. SELECT statements embedded in PL/SQL must select into a variable which matches the projection of the query.

Not sure what your code is trying to achieve, but this version of your code runs:

DECLARE

     obj fv_grouping;

BEGIN



   SELECT fv_grouping(cast(collect(fv_group(fv, 3)) as fv_group_array))

   into obj

   from  cophir;



   dbms_output.put_line(obj.which_group(2)) ;



END; 

/

This uses COLLECT to gather the fv_group objects into an fv_group_array instance which can be used to instantiate fv_grouping and populate the obj variable.