ORA-00904 when trying to use Oracle TABLE() function in a UDT column passed as a function parameter

Question

Given the following type hierarchy:

CREATE OR REPLACE TYPE VC_MNumber AS Object (   
  idno NUMBER,
  MEMBER FUNCTION   typeDimension   RETURN NUMBER ,
  MEMBER FUNCTION typeName RETURN VARCHAR2) NOT INSTANTIABLE NOT FINAL;
/

CREATE OR REPLACE TYPE VC_MReal UNDER VC_MNumber(   
  YDCCoeff NUMBER,
  CbDCCoeff NUMBER,
  CrDCCoeff NUMBER,
  OVERRIDING MEMBER FUNCTION  typeDimension   RETURN NUMBER, 
  OVERRIDING MEMBER FUNCTION typeName RETURN VARCHAR2) FINAL;
/

CREATE OR REPLACE TYPE BODY VC_MReal AS
  OVERRIDING MEMBER FUNCTION typeDimension RETURN NUMBER IS
    var_dimension       number :=  10;
    BEGIN
        RETURN var_dimension;
    END;
   OVERRIDING MEMBER FUNCTION typeName RETURN VARCHAR2 IS
    var_typeName        VARCHAR2(400) :=  'VC_MREAL';
    BEGIN
        RETURN var_typeName;
    END;
END;
/

And the table:

CREATE TABLE cophirfv_int ( id NUMBER, fv VC_MReal );

I am trying to execute the function:

create or replace function sum_MNumber
(
    inn_FV VC_MNumber,
    out_FV VC_MNumber
) return NUMBER is
   sql_stmt1 varchar2(400);
   total_sum number := 0;
   inn_val number;
   cur_type varchar2(400);
   var_cur_type_name varchar2(400);
BEGIN
    FOR cur_type IN (select attr_name from user_type_attrs where type_name='VC_MREAL')
        LOOP    


        var_cur_type_name := cur_type.attr_name;

        dbms_output.put_line(var_cur_type_name);

        sql_stmt1 := 'SELECT ifv.' || var_cur_type_name ||' FROM TABLE(inn_FV.'|| var_cur_type_name ||') ifv';

        dbms_output.put_line(sql_stmt1);

        EXECUTE IMMEDIATE sql_stmt1 INTO inn_val;

        --total_sum := total_sum + inn_val;
    END LOOP;
    return 2;
END;
/

Through the query:

select sum_MNumber(fv,fv) from cophirfv_int;

But I am getting the error ORA-00904: "INN_FV"."CRDCCOEFF" Invalid identifier on the line:

EXECUTE IMMEDIATE sql_stmt1 INTO inn_val;

Someone know what is wrong? How can I fix it?

Answer 1

Using dynamic SQL:

(I do not believe using this is a good idea and am only posting it as a proof-of-concept. Instead you should be defining OVERRIDING MEMBER FUNCTIONs on each sub-type, as in my other answer, and calling those)

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE FUNCTION sum_MNumber
(
  i_FV VC_MNumber
) RETURN NUMBER
IS
  value NUMBER;
  total NUMBER := 0;
  type_names SYS.ODCIVARCHAR2LIST;
  attr_names SYS.ODCIVARCHAR2LIST;
BEGIN
  SELECT t.type_name,
         attr_name
  BULK COLLECT INTO
         type_names,
         attr_names
  FROM   USER_TYPE_ATTRS a
         INNER JOIN
         USER_TYPES t
         ON ( a.type_name = t.type_name )
  WHERE  t.TYPEID = SYS_TYPEID( i_FV );

  FOR i IN 1 .. type_names.COUNT LOOP
    EXECUTE IMMEDIATE
      'BEGIN :1 := TREAT(:2 AS "' || type_names(i) || '")."' || attr_names(i) || '"; END;'
      USING OUT value, i_FV;
    total := total + value;
  END LOOP;

  RETURN total;
END;
/

INSERT INTO cophirfv_int VALUES ( 1, VC_MReal( 1, 3, 4, 5 ) )
/

INSERT INTO cophirfv_int VALUES ( 2, VC_MReal( 2, 1, 3, 4 ) )
/

Query 1:

select sum_MNumber(fv) from cophirfv_int

Results:

| SUM_MNUMBER(FV) |
|-----------------|
|              13 |
|              10 |

Answer 2

You do not need dynamic SQL instead you can use the IS OF( type ) operator and the TREAT function:

SQL Fiddle

Oracle 11g R2 Schema Setup:

create function sum_MNumber
(
    inn_FV VC_MNumber,
    out_FV VC_MNumber
) return NUMBER
IS
  total NUMBER := 0;
BEGIN
  IF inn_FV IS OF( VC_MReal ) THEN
    total := total + TREAT( inn_FV AS VC_Mreal ).YDCCoeff
                   + TREAT( inn_FV AS VC_Mreal ).CbDCCoeff
                   + TREAT( inn_FV AS VC_Mreal ).CrDCCoeff;
  END IF;
  IF out_FV IS OF( VC_MReal ) THEN
    total := total + TREAT( out_FV AS VC_Mreal ).YDCCoeff
                   + TREAT( out_FV AS VC_Mreal ).CbDCCoeff
                   + TREAT( out_FV AS VC_Mreal ).CrDCCoeff;
  END IF;
  RETURN total;
END;
/

INSERT INTO cophirfv_int VALUES ( 1, VC_MReal( 1, 3, 4, 5 ) )
/

INSERT INTO cophirfv_int VALUES ( 2, VC_MReal( 2, 1, 3, 4 ) )
/

Query 1:

select sum_MNumber(fv,fv) from cophirfv_int

Results:

| SUM_MNUMBER(FV,FV) |
|--------------------|
|                 24 |
|                 16 |

Alternative

Or you could define an approriate member function in the types:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE OR REPLACE TYPE VC_MNumber AS Object (   
  idno NUMBER,
  MEMBER FUNCTION total RETURN NUMBER
) NOT INSTANTIABLE NOT FINAL;
/

CREATE OR REPLACE TYPE VC_MReal UNDER VC_MNumber(   
  YDCCoeff NUMBER,
  CbDCCoeff NUMBER,
  CrDCCoeff NUMBER,
  OVERRIDING MEMBER FUNCTION total RETURN NUMBER
) FINAL;
/

CREATE OR REPLACE TYPE BODY VC_MReal AS
  OVERRIDING MEMBER FUNCTION total RETURN NUMBER IS
  BEGIN
    RETURN self.YDCCoeff + self.CbDCCoeff + self.CrDCCoeff;
  END;
END;
/

CREATE TABLE cophirfv_int ( id NUMBER, fv VC_MReal )
/

INSERT INTO cophirfv_int VALUES ( 1, VC_MReal( 1, 3, 4, 5 ) )
/

INSERT INTO cophirfv_int VALUES ( 2, VC_MReal( 2, 1, 3, 4 ) )
/

create or replace function sum_MNumber
(
    inn_FV VC_MNumber,
    out_FV VC_MNumber
) return NUMBER
IS
  total NUMBER := 0;
BEGIN
  RETURN inn_Fv.total() + out_FV.total();
END;
/

Query 1:

select sum_MNumber(fv,fv) from cophirfv_int

Results:

| SUM_MNUMBER(FV,FV) |
|--------------------|
|                 24 |
|                 16 |