ORA-00932: inconsistent data types expected UDT got NUMBER-CodePudding

Can anyone help. I've stripped this down and cannot figure it out. I am getting ORA-00932: inconsistent data types expected UDT got NUMBER. I have been trying for a week to figure this out. I don't have much experience with this.

create or replace TYPE            TEST_REC as object (
    NUM NUMBER
);

create or replace TYPE            TEST_TAB is table of ELIMINATOR.TEST_REC



create or replace FUNCTION            test_func_smm return TEST_TAB
as
        query_str_main             VARCHAR2 (20000);
        V_ILIMIT                   NUMBER ;  
        V_IINTCNT                  PLS_INTEGER := 0;
 
      TYPE RC IS REF CURSOR;
 
      VRC        RC;
      VTAB        TEST_TAB := TEST_TAB ();
      VTEMPTAB    TEST_TAB := TEST_TAB ();
     
      con_iReclimit CONSTANT   NUMBER := 5000;
 
begin
 
query_str_main := ' select 5 as a from dual';
 
      dbms_output.put_line(query_str_main);
 
      OPEN VRC FOR query_str_main;
 
      LOOP
         EXIT WHEN VRC%NOTFOUND;
 
         FETCH VRC
            BULK COLLECT INTO   VTEMPTAB
            LIMIT con_iReclimit;
            DBMS_OUTPUT.PUT_LINE('BULK COLLECT');
 
     VTAB :=
     VTAB MULTISET UNION ALL VTEMPTAB;

 
     V_IINTCNT :=  
     V_IINTCNT   VTEMPTAB.COUNT;

      END LOOP;
     
      IF VRC%ISOPEN
         THEN
            CLOSE VRC;
         END IF;
 
      RETURN VTAB;
end;

CodePudding user response：

This is the culprit:

query_str_main := ' select 5 as a from dual';

Should be

query_str_main := ' select test_rec(5) as a from dual';

When fixed, function works:

SQL> CREATE OR REPLACE FUNCTION test_func_smm
  2     RETURN test_tab
  3  AS
  4     query_str_main          VARCHAR2 (20000);
  5     v_ilimit                NUMBER;
  6     v_iintcnt               PLS_INTEGER := 0;
  7
  8     TYPE rc IS REF CURSOR;
  9
 10     vrc                     rc;
 11     vtab                    test_tab := test_tab ();
 12     vtemptab                test_tab := test_tab ();
 13
 14     con_ireclimit  CONSTANT NUMBER := 5000;
 15  BEGIN
 16     query_str_main := ' select test_rec(5) as a from dual';
 17
 18     DBMS_OUTPUT.put_line (query_str_main);
 19
 20     OPEN vrc FOR query_str_main;
 21
 22     LOOP
 23        EXIT WHEN vrc%NOTFOUND;
 24
 25        FETCH vrc BULK COLLECT INTO vtemptab LIMIT con_ireclimit;
 26
 27        DBMS_OUTPUT.put_line ('BULK COLLECT');
 28
 29        vtab := vtab MULTISET UNION ALL vtemptab;
 30
 31        v_iintcnt := v_iintcnt   vtemptab.COUNT;
 32     END LOOP;
 33
 34     IF vrc%ISOPEN
 35     THEN
 36        CLOSE vrc;
 37     END IF;
 38
 39     RETURN vtab;
 40  END;
 41  /

Function created.

Testing:

SQL> SELECT * FROM TABLE (test_func_smm);

       NUM
----------
         5

select test_rec(5) as a from dual
BULK COLLECT
SQL>