PL/SQL function that returns a table is not compiling

Question

I am trying to compile a PL/SQL function which returns a table. However, I am getting the error PL/SQL: ORA-00932: inconsistent datatypes: expected UDT got CHAR. I need to return a table using function. This function is just a sample. Actual function is of 200 lines.

Below is the code :

  create or replace type type_TRAUDIT040 as object
   (X VARCHAR2(1000), 
    DOCUMENT_ID VARCHAR2(1000 ), 
    PR3_ID VARCHAR2(1000 ), 
    MICROFILM_ID VARCHAR2(1000 ), 
    CNT_PR3_ID VARCHAR2(1000 ), 
    CNT_MICRO_ID VARCHAR2(1000 ), 
    CREATE_USERID VARCHAR2(1000 ), 
    PAYEE_PRVR_EXT_ID VARCHAR2(1000 ), 
    RECCNT VARCHAR2(1000 ),
    cp_claim_types_desc VARCHAR2(1000 ),
    cp_claim_status_desc VARCHAR2(1000 ),
    count_document_id  VARCHAR2(1000 )
   )
   
   create or replace type table_TRAUDIT040 is table of type_TRAUDIT040 

Below is the function :

create or replace function fn_traudit040
(
    p_Ambulance  varchar2
    ) 
    return table_TRAUDIT040
    as test_type table_TRAUDIT040;
   
BEGIN

     SELECT table_TRAUDIT040( 
             x,document_id,PR3_ID,MICROFILM_ID,CNT_PR3_ID,CNT_MICRO_ID,create_userid,PAYEE_PRVR_EXT_ID,RecCnt,'1' ,'2','3'
           )
    BULK COLLECT INTO test_type
    FROM   TRAUDIT040;
     
    RETURN test_type;

END;

DDL for Table TRAUDIT040

  CREATE TABLE TRAUDIT040 
   (    X VARCHAR2(1000 ), 
    DOCUMENT_ID VARCHAR2(1000 ), 
    PR3_ID VARCHAR2(1000 ), 
    MICROFILM_ID VARCHAR2(1000 ), 
    CNT_PR3_ID VARCHAR2(1000 ), 
    CNT_MICRO_ID VARCHAR2(1000 ), 
    CREATE_USERID VARCHAR2(1000 ), 
    PAYEE_PRVR_EXT_ID VARCHAR2(1000 ), 
    RECCNT VARCHAR2(1000 )
   )

Error Screenshot :

Thanks in advance

Answer 1

Change SELECT table_TRAUDIT040( to SELECT type_TRAUDIT040(. The SELECT clause projects a single record, and then the BULK COLLECT aggregates those records into a nested table.