Troubleshooting Inconsistent Data Types Error in PL/SQL Function: ORA-00932

Programming
, ,
1

Troubleshooting Inconsistent Data Types in PLSQL Function

I am trying to create a PLSQL function which will accept a SQL CLOB and a NUMBER as input and will return a TABLE Type. The following code is an example of what I am trying:

CREATE OR REPLACE TYPE t_rec AS OBJECT
   (
      datasource varchar2(100), 
      KEY VARCHAR2(20), 
      SUM_AMT NUMBER
   );
   
CREATE OR REPLACE TYPE t_tab IS TABLE OF t_rec;

create or replace FUNCTION test_func (p_sql IN CLOB,
                                      P_ID IN NUMBER
                            )
  RETURN t_tab
  AS
    lt_tab t_tab;
  BEGIN
    
    execute immediate p_sql bulk collect into lt_tab using p_id;
     
    RETURN lt_tab; 
     
END ;

select * from table(test_func('SELECT t_rec(''some_datasource'' ,cp.Key1,sum(cp.amtount))  
        FROM table1 cp, table2 fa
       WHERE cp.setid = :l_id
        and cp.id = fa.id
        AND fa.flag = ''Y''
      GROUP BY cp.Key1'));

ORA-00932: inconsistent datatypes: expected - got -

The above code returns the following error when run on an Oracle 19c database:

ORA-00932: inconsistent datatypes: expected - got -
00932. 00000 -  "inconsistent datatypes: expected %s got %s"
*Cause:    
*Action:

I am not sure what I am doing wrong.

2

The issue you are facing is caused by the inconsistency in data types between the lt_tab variable and the result of the EXECUTE IMMEDIATE statement.

To resolve this issue, you need to ensure that the data types of the lt_tab variable and the result of the EXECUTE IMMEDIATE statement match.

Here’s the corrected code:

CREATE OR REPLACE TYPE t_rec AS OBJECT
   (
      datasource varchar2(100), 
      KEY VARCHAR2(20), 
      SUM_AMT NUMBER
   );
   
CREATE OR REPLACE TYPE t_tab IS TABLE OF t_rec;

CREATE OR REPLACE FUNCTION test_func (p_sql IN CLOB,
                                      P_ID IN NUMBER)
  RETURN t_tab
  AS
    lt_tab t_tab;
  BEGIN
    EXECUTE IMMEDIATE p_sql BULK COLLECT INTO lt_tab USING p_id;
    RETURN lt_tab; 
  END test_func;

Make sure to create the PL/SQL types t_rec and t_tab before creating the function.

After making these changes, you should be able to execute the function without errors.