ORA-00932 while executing into BULK COLLECT-CodePudding

I'm getting error: ORA-00932: inconsistent datatypes: expected - got -. There is no information what type is inconsistent. I see only -. Is there a way to check it? The code below is simplified :

create type IMPORT_R as object (
  ID int,
  IMPORT1_ID int,
  IMPORT2_ID int,
  PROD_ID int
);
create type IMPORT_T as table of IMPORT_R;

CREATE FUNCTION GetImport(
  id IN BINARY_INTEGER, 
  id2 IN BINARY_INTEGER, 
  id3 IN BINARY_INTEGER) 
return IMPORT_T
IS
  sqlCommand VARCHAR2(32000);
  v_Result IMPORT_T;
begin
    sqlCommand :=
        'SELECT ID, IMPORT1_ID, IMPORT2_ID, PROD_ID
        FROM 
        (
            SELECT ID,
                   IMPORT1_ID,
                   -1 AS IMPORT2_ID,
                   PROD_ID                
              FROM TABLE1
              WHERE IMPORT1> '|| id1;

       
    IF id := 123 THEN    
        sqlCommand := sqlCommand||
        'UNION
        SELECT ID,
               -1 AS IMPORT1_ID,
               IMPORT2_ID,
               PROD_ID               
          FROM TABLE2
          WHERE IMPORT2> '|| id2;
    END IF;
    
    sqlCommand := sqlCommand||')' ;
    
    EXECUTE IMMEDIATE sqlCommand BULK COLLECT INTO v_Result;
    
    return v_Result;
end;

The error occures in line where EXECUTE IMMEDIATE is executed. Any clue? Thank you!

CodePudding user response：

This is happening because Oracle tries to fit 4 numbers (id, import1_id, import2_id, prod_id) into one result column and fails.

In order to fix that you need explicitly say to the database how to cast those 4 numbers into an object. Fortunately it only the first line needs to be changed, the first line of the query part:

sqlCommand :=
    'SELECT IMPORT_R(ID, IMPORT1_ID, IMPORT2_ID, PROD_ID)

and everything will be working fine

(I don't know why the ORA-message is so unclear in this case)